Как оставить возможность работать с группировкой/структурой на защищенном листе?
Наверняка многие уже сталкивались с ситуацией, когда необходимо защитить лист от внесения изменений в ячейки(Рецензирование (Review) —Защитить лист (Protect Sheet) — читать подробнее про защиту листа), на котором уже имеются сгруппированные в структуру данные. И при установке такой защиты теряется возможность работы с этой самой группировкой/структурой. Если не знаете, что такое структура(еще её называют группировка): это такие плюсики левее строк/выше столбцов, при нажатии на которые раскрываются скрытые строки/столбцы:
Но что делать, если нужна и защита и возможность структурой пользоваться? Т.е. чтобы пользователь мог просмотреть все в удобной форме, но не смог ничего изменить. Одновременно и просто и не очень.
Если вы не знакомы с макросами и VBA, то обязательно пройдите по ссылкам из инструкции ниже — эти знания потребуются, чтобы сделать все правильно и получить корректный результат. Итак, чтобы разрешить использовать структуру на защищенном листе необходимо:
- создать в книге стандартный модуль
- разместить в нем нижеприведенный код:
Sub ProtectShWithOutline() ActiveSheet.EnableOutlining = True ActiveSheet.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True End Sub
Код сам устанавливает защиту на лист( не надо перед его выполнением устанавливать защиту вручную! ), но при этом разрешает использовать группировку.
Основную роль здесь играет параметр UserInterfaceOnly , который говорит Excel-ю, что коды VBA могут выполнять определенные действия, не снимая защиты методом Unprotect. А второй важный пункт — EnableOutlining = True . Он как раз и включает возможность использования группировки. Как ни странно, но без UserInterfaceOnly он не работает. Поэтому важно применять их оба.
Код выше устанавливает такую защиту только на активный лист книги. Но можно указать лист явно(например установить защиту на лист с именем Лист1 в активной книге):
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).
Сделать это можно таким кодом:
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
Для применения этого кода в своих книгах необходимо будет лишь изменить(добавить, удалить, вписать другие имена) имена листов в этой строке: Array(«Январь», «Февраль», «Март») . Записывать обязательно в кавычках.
Примечание: Описанный метод защиты имеет одно существенное ограничение: его невозможно использовать в книге с общим доступом(Рецензирование -Доступ к книге), т.к. при общем доступе существуют ограничения, среди которых и такое, которое запрещает изменять параметры защиты для книги в общем доступе.
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Здравствуйте Дмитрий. К сожалению я не программист, а обычный юзер. И прошу Вашей помощи.
Я создал таблицу расчетов, в которой есть группировки, закрепленные области, шапки у столбцов и итоговые ячейки с объединенными ячейками. Книга в расширении .xlsx содержит только один лист. Можно ли защитить некоторые столбцы ( они с формулами) таблицы, чтобы оставить возможность работать с группировками на защищенном листе? И в какой последовательности нужно действовать — сначала ставить защиту, а потом вписать макрос или наоборот? Кстати, пробовал простым способом ставить защиту на столбец с объединенными ячейками, но не разрешает.
Буду признателен, если бы Вы смогли написать необходимый код.
Заранее благодарен!
А в чем моя помощь должна заключаться? Все коды уже приведены. Вы бы статью внимательно сначала прочитали — там все разжевано и добавить нечего. В какой последовательности что делать, что делает макрос и как он это делает. Плюс ссылка на статью по защите ячеек в Excel есть: Защита листов и ячеек в MS Excel — там можно посмотреть как ставить защиту на отдельные ячейки и какие нюансы при этом возникают.
Как разрешить группировку на защищенном листе
Регистрация на форуме тут, о проблемах пишите сюда — alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите восстановить пароль
| Поиск по форуму |
| Расширенный поиск |
| К странице. |
| Страница 1 из 5 | 1 | 2 | 3 | 4 | 5 | Следующая > |
У меня следующая ситуация: надо сделать так, чтоб информацию в ячейках нельзя было изменять, но в тоже время можно было пользоваться функцией группировка. Я поставила защиту на лист, теперь форматировать данные нельзя, но и группировка не работает.
Помогите, пожалуйста, разобраться!
Вложения
| Группы.rar (6.3 Кб, 501 просмотров) |
| если я закрываю книгу и открываю ее снова, то при попытке раскрыть группу, у меня вылазит сообщение, что данное действие нельзя произвести на защищенном листе |
Сделала так как написали, но потом при открытии этого файла защищенного появляется окно:
Compile error:
Sub of Function not defined
Затем закрываю все это и группировка так и не работает.
Что то не так сделала?
Как разрешить группировку на защищенном листе
Для этого вам понадобится VBA, и конечный пользователь должен будет разрешить макросы, чтобы это работало.
Нажмите Alt + F11, чтобы активировать редактор Visual Basic.
Дважды щелкните ThisWorkbook в разделе «Объекты Microsoft Excel» в проводнике проекта с левой стороны.
Скопируйте следующий код в появившийся модуль:
Private Sub Workbook_Open ()
С рабочими листами («Сводка Emp»)
.EnableOutlining = Истина
.Защитить UserInterfaceOnly:=True
Конец с
End Sub
Этот код будет выполняться автоматически при каждом открытии книги.
Я получил этот код для работы. Но когда я закрываю и снова открываю, я должен перейти на вкладку разработчика, выбрать кнопку макросов, выбрать «Выполнить» и ввести пароль.
Есть ли способ удалить пароль из кода ИЛИ код автозапуска, который автоматически запустит этот марко и введет пароль?
Кому-то это может понадобиться, я думаю, что понял, как заставить это работать.
Во-первых, ваш код должен быть написан в «ThisWorkbook» в разделе «Объекты Microsoft Excel», как предлагает @peachyclean.
Во-вторых, возьмите код, который написал @Sravanthi, и вставьте в указанное выше место.
Подпрограмма Workbook_Open()
‘Обновление 20140603
Dim xWs как рабочий лист
Установите xWs = Application.ActiveSheet
Dim xPws как строка
xPws = «rfc» »Application.InputBox(«Пароль:», xTitleId, «», Тип:=2)
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = Истина
End Sub
Дело в том, что вам нужно быть на листе, который вы хотите защитить, но с возможностью группировки, сохранить книгу и закрыть, не защищая. Теперь, если вы откроете его, макрос запустится автоматически, он сделает лист защищенным паролем «rfc». Теперь можно пользоваться группировкой, лист защищен.
Для моего решения я изменил примененный пароль, поэтому вы можете переписать любой пароль ЗДЕСЬ:
xPws = «WRITEANYPASSWORDHERE» »Application.InputBox(«Пароль:», xTitleId, «», Тип:=2)
Кроме того, я не хотел, чтобы защищаемый лист был активен при открытии файла, поэтому я изменил эту часть:
Установите xWs = Application.ActiveSheet ->
Установите xWs = Application.Worksheets(«WRITEANYSHEET’SNAMEHERE»)
Теперь это работает как шарм, лист с именем «WRITEANYSHEET’SNAMEHERE» защищен, но применима группировка. В долгосрочной перспективе, я думаю, проблема будет заключаться в том, что если я захочу изменить этот файл и сохранить решение, мне нужно снять защиту с этого листа, чтобы он работал при следующем открытии. Я думаю, вы можете написать другой макрос для автоматического снятия защиты при закрытии 🙂