Наверняка многие уже сталкивались с ситуацией, когда необходимо защитить лист от внесения изменений в ячейки(Рецензирование
Но что делать, если нужна и защита и возможность структурой пользоваться? Т.е. чтобы пользователь мог просмотреть все в удобной форме, но не смог ничего изменить. Одновременно и просто и не очень.
Если вы не знакомы с макросами и VBA, то обязательно пройдите по ссылкам из инструкции ниже - эти знания потребуются, чтобы сделать все правильно и получить корректный результат. Итак, чтобы разрешить использовать структуру на защищенном листе необходимо:
- создать в книге стандартный модуль(
Alt +F11 -Insert -Module) - разместить в нем нижеприведенный код:
Sub ProtectShWithOutline() ActiveSheet.EnableOutlining = True ActiveSheet.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True End Sub
- Выполнить данный код(
Alt +F8 -Protect_And_Structure )
Код сам устанавливает защиту на лист(
Основную роль здесь играет параметр
Код выше устанавливает такую защиту только на активный лист книги. Но можно указать лист явно(например установить защиту на лист с именем
Sub ProtectShWithOutline() Sheets("Лист1").EnableOutlining = True Sheets("Лист1").Protect Password:="1111", UserInterfaceOnly:=True End Sub |
Так же приведенный код можно еще чуть модернизировать и разрешить пользователю помимо изменения ячеек еще и использовать автофильтр:
Sub ProtectShWithOutline() 'на лист "Лист1" поставим защиту и разрешим пользоваться фильтром Sheets("Лист1").EnableOutlining = True 'разрешаем группировку Sheets("Лист1").Protect Password:="1111", AllowFiltering:=True, UserInterfaceOnly:=True End Sub |
Можно разрешить и иные действия(выделение незащищенных ячеек, выделение защищенных ячеек, форматирование ячеек, вставку строк, вставку столбцов и т.д. Чуть подробнее про доступные параметры можно узнать в статье Защита листов и ячеек в MS Excel). А как будет выглядеть строка кода с разрешенными параметрами можно узнать, записав макрорекордером установку защиты листа с нужными параметрами:
После этого получится строка вроде такой:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True |
здесь я разрешил использовать автофильтр(AllowFiltering:=True), вставлять строки(AllowInsertingRows:=True) и столбцы(AllowInsertingColumns:=True).Чтобы добавить возможность изменять данные ячеек только через код VBA, останется добавить параметр UserInterfaceOnly:=True и установить EnableOutlining = True:
ActiveSheet.EnableOutlining = True 'разрешаем группировку ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True |
и так же неплохо бы добавить и пароль для снятия защиты, т.к. запись макрорекордером не записывает пароль:
ActiveSheet.EnableOutlining = True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111" |
Самая большая ложка дегтя заключается в том, что параметр UserInterfaceOnly сбрасывается сразу после закрытия книги. Т.е. если установить таким образом защиту на лист и закрыть книгу, то при следующем открытии этой защиты уже не будет - останется лишь стандартная защита, а группировка работать не будет. Что ставит под сомнение полезность подобного подхода, потому как обычно такое применяется для других пользователей, которые как правило далеки от макросов и даже слушать не станут, что мы там будем им предлагать выполнить. Поэтому, если необходимо такую защиту видеть постоянно и не только у себя на компьютере, то данный макрос лучше всего прописывать на событие открытия книги(модуль ЭтаКнига(ThisWorkbook)). Т.е. приведенный ниже код в обязательном порядке должен быть именно в модуле ЭтаКнига(ThisWorkbook) на событие Workbook_Open. Это заставит код установки защиты на лист выполняться автоматически при открытии книги. Т.е. конечному пользователю не надо будет ничего нажимать для его запуска: открыл книгу - код сам запустился, все работает.
Собственно, сам код защиты, срабатывающий при открытии книги:
Private Sub Workbook_Open() Sheets("Лист1").EnableOutlining = True Sheets("Лист1").Protect Password:="1111", UserInterfaceOnly:=True End Sub |
Правда куда чаще необходимо устанавливать одинаковую защиту на все листы книги. Сделать это можно кодом ниже, который так же должен быть размещен в модуле ЭтаКнига(ThisWorkbook):
Private Sub Workbook_Open() Dim wsSh As Object For Each wsSh In Me.Sheets ProtectShWithOutline wsSh Next wsSh End Sub Sub ProtectShWithOutline(wsSh As Worksheet) 'Password:="1111" - это пароль на лист - 1111 wsSh.Protect Password:="1111", UserInterfaceOnly:=True End Sub |
Плюс во избежание ошибок лучше перед установкой защиты снимать ранее установленную(если она была):
Sub ProtectShWithOutline(wsSh As Worksheet) wsSh.Unprotect "1111" 'снимаем прежнюю защиту wsSh.EnableOutlining = True 'разрешаем группировку wsSh.Protect Password:="1111", UserInterfaceOnly:=True 'защищаем лист с паролем "1111" End Sub |
Если же защиту необходимо установить только на конкретные листы, имена которых заранее известны, то можно использовать чуть иной подход - использовать массивы:
Private Sub Workbook_Open() Dim arr, sSh arr = Array("Январь", "Февраль", "Март") For Each sSh in arr ProtectShWithOutline Me.Sheets(sSh) Next End Sub Sub ProtectShWithOutline(wsSh As Worksheet) wsSh.EnableOutlining = True wsSh.Protect Password:="1111", AllowFiltering:=True, UserInterfaceOnly:=True End Sub |
Для применения этого кода в своих книгах необходимо будет лишь изменить(добавить, удалить, вписать другие имена) имена листов в этой строке:
Примечание: Описанный метод защиты имеет одно существенное ограничение: его невозможно использовать в книге с общим доступом(Рецензирование -Доступ к книге), т.к. при общем доступе существуют ограничения, среди которых и такое, которое запрещает изменять параметры защиты для книги в общем доступе.
Также см.:
Как защитить лист от пользователя, но не от макроса?
Защита листов и ячеек в MS Excel
Защита листов/снятие защиты
Снять защиту с листа(без пароля)
Добрый день!
У меня все отлично работает, я вообще первый раз коснулась VBA по вашей статье, не знаю, как у других опытных не работает. Спасибо большое!
А вопрос, возможно простой, но я первый раз, задам: как добавить защиту на структуру книги? Чтобы пользователь не смог удалять листы, менять названия и т. д. Сейчас у меня в ЭтаКнига стоит вот это:
Private Sub Workbook_Open()
Dim wsSh As Object
For Each wsSh In Me.Sheets
Protect_for_User_Non_for_VBA wsSh
Next wsSh
End Sub
Sub Protect_for_User_Non_for_VBA(wsSh As Worksheet)
wsSh.Unprotect "2121"
wsSh.Protect Password:="2121", AllowFiltering:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
End Sub
Буду благодарна за ответ! :)
Екатерина, в общем-то не слишком сложно. Достаточно защитить книгу руками(Рецензирование -Защитить книгу). Защитить один раз достаточно - это будет работать и не помешает кодам защиты листов из статьи. Или можете защитить кодом: