Как разрешить группировку на защищенном листе
У меня следующая ситуация: надо сделать так, чтоб информацию в ячейках нельзя было изменять, но в тоже время можно было пользоваться функцией группировка. Я поставила защиту на лист, теперь форматировать данные нельзя, но и группировка не работает.
Помогите, пожалуйста, разобраться!
Вложения
| Группы.rar (6.3 Кб, 502 просмотров) |
| если я закрываю книгу и открываю ее снова, то при попытке раскрыть группу, у меня вылазит сообщение, что данное действие нельзя произвести на защищенном листе |
Сделала так как написали, но потом при открытии этого файла защищенного появляется окно:
Compile error:
Sub of Function not defined
Затем закрываю все это и группировка так и не работает.
Что то не так сделала?
Как разрешить группировку на защищенном листе
Как оставить возможность работать с группировкой/структурой на защищенном листе?
Для многих, наверное, не секрет, что если защитить лист от внесения изменений в ячейки и на листе имеется сгруппированные в структуру данные, то при установке обычной защиты(Рецензирование -Защитить лист) теряется возможность работы с этой структурой. Для тех, кто не совсем понимает, что такое структура(еще её называют группировка): это такие плюсики левее строк/выше столбцов, при нажатии на которые раскрываются скрытые строки/столбцы.
Однако часто бывает необходимо сделать так, чтобы наряду с защитой листа можно было еще и структурой пользоваться. Т.е. чтобы пользователь мог просмотреть все в удобной форме, но не смог ничего изменить. Так как же защитить лист и оставить возможность работы со структурой? Очень просто.
Если вы не знакомы с макросами и VBA, то обязательно пройдите по ссылкам из инструкции ниже. Итак, чтобы разрешить использовать структуру на защищенном листе необходимо:
- создать в книге стандартный модуль
- разместить в нем нижеприведенный код:
S ub ProtectShWithOutline() ActiveSheet.EnableOutlining = True ActiveSheet.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True End Sub
S ub ProtectShWithOutline()
ActiveSheet.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
Код сам устанавливает защиту на лист(не надо перед его выполнением устанавливать защиту вручную!), но при этом разрешает использовать группировку.
Основную роль здесь играет параметр UserInterfaceOnly , который говорит Excel-ю, что коды VBA могут выполнять определенные действия, не снимая защиты методом Unprotect. А второй важный пункт — EnableOutlining = True. Он как раз и включает возможность использования группировки. Как ни странно, но без UserInterfaceOnly он не работает. Поэтому важно применять их оба.
Код выше устанавливает такую защиту только на активный лист книги. Но можно указать лист явно(например установить защиту на лист с именем Лист1 в активной книге):
S ub ProtectShWithOutline() Sheets("Лист1").EnableOutlining = True Sheets("Лист1").Protect Password:="1111", UserInterfaceOnly:=True End Sub
S ub ProtectShWithOutline()
Sheets("Лист1").Protect Password:="1111", UserInterfaceOnly:=True
Так же приведенный код можно еще чуть модернизировать и разрешить пользователю помимо изменения ячеек еще и использовать автофильтр:
S ub ProtectShWithOutline() ‘на лист "Лист1" поставим защиту и разрешим пользоваться фильтром Sheets("Лист1").EnableOutlining = True ‘разрешаем группировку Sheets("Лист1").Protect Password:="1111", AllowFiltering:=True, UserInterfaceOnly:=True End Sub
S ub ProtectShWithOutline()
‘на лист "Лист1" поставим защиту и разрешим пользоваться фильтром
Sheets("Лист1").EnableOutlining = True ‘разрешаем группировку
Sheets("Лист1").Protect Password:="1111", AllowFiltering:=True, UserInterfaceOnly:=True
Можно разрешить и иные действия(выделение незащищенных ячеек, выделение защищенных ячеек, форматирование ячеек, вставку строк, вставку столбцов и т.д. Чуть подробнее про доступные параметры можно узнать в статье Защита листов и ячеек в MS Excel ). А как будет выглядеть строка кода с разрешенными параметрами можно узнать, записав макрорекордером установку защиты листа с нужными параметрами:
После этого получится строка вроде такой:
A ctiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True
A ctiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True
здесь я разрешил использовать автофильтр( A llowFiltering:=True ), вставлять строки( A llowInsertingRows:=True ) и столбцы( A llowInsertingColumns:=True ).Чтобы добавить возможность изменять данные ячеек только через код VBA, останется добавить параметр UserInterfaceOnly:=True и установить EnableOutlining = True:
A ctiveSheet.EnableOutlining = True ‘разрешаем группировку ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True
A ctiveSheet.EnableOutlining = True ‘разрешаем группировку
A ctiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True
и так же неплохо бы добавить и пароль для снятия защиты, т.к. запись макрорекордером не записывает пароль:
A ctiveSheet.EnableOutlining = True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"
A ctiveSheet.EnableOutlining = True
A ctiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"
Самая большая ложка дегтя заключается в том, что параметр UserInterfaceOnly сбрасывается сразу после закрытия книги. Т.е. если установить таким образом защиту на лист и закрыть книгу, то при следующем открытии защиты этой уже не будет — останется лишь стандартная защита. А значит группировка работать по прежнему не будет, что ставит под сомнение полезность подобного подхода, потому как обычно такое применяется для других пользователей, которые как правило далеки от макросов и даже слушать не станут, что мы там будем им предлагать выполнить. Поэтому, если необходимо такую защиту видеть постоянно и не только у себя на компьютере, то данный макрос лучше всего прописывать на событие открытия книги(модуль ЭтаКнига ( ThisWorkbook ) ).
Сделать это можно таким кодом:
P rivate Sub Workbook_Open() Sheets("Лист1").EnableOutlining = True Sheets("Лист1").Protect Password:="1111", UserInterfaceOnly:=True End Sub
P rivate Sub Workbook_Open()
Sheets("Лист1").Protect Password:="1111", UserInterfaceOnly:=True
Правда куда чаще необходимо устанавливать одинаковую защиту на все листы книги. Сделать это можно кодом ниже, который так же должен быть размещен в модуле ЭтаКнига ( ThisWorkbook ) :
P rivate Sub Workbook_Open() Dim wsSh As Object For Each wsSh In Me.Sheets ProtectShWithOutline wsSh Next wsSh End Sub Sub ProtectShWithOutline(wsSh As Worksheet) wsSh.Protect Password:="1111", UserInterfaceOnly:=True End Sub
P rivate Sub Workbook_Open()
Dim wsSh As Object
For Each wsSh In Me.Sheets
S ub ProtectShWithOutline(wsSh As Worksheet)
wsSh.Protect Password:="1111", UserInterfaceOnly:=True
Плюс во избежание ошибок лучше перед установкой защиты снимать ранее установленную(если она была):
S ub ProtectShWithOutline(wsSh As Worksheet) wsSh.Unrotect "1111" ‘снимаем прежнюю защиту wsSh.EnableOutlining = True ‘разрешаем группировку wsSh.Protect Password:="1111", UserInterfaceOnly:=True ‘защищаем лист с паролем "1111" End Sub
S ub ProtectShWithOutline(wsSh As Worksheet)
wsSh.Unrotect "1111" ‘снимаем прежнюю защиту
wsSh.EnableOutlining = True ‘разрешаем группировку
wsSh.Protect Password:="1111", UserInterfaceOnly:=True ‘защищаем лист с паролем "1111"
Если же защиту необходимо установить только на конкретные листы, имена которых заранее известны, то можно использовать чуть иной подход — использовать массивы:
P rivate 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
P rivate Sub Workbook_Open()
For Each sSh in arr
S ub ProtectShWithOutline(wsSh As Worksheet)
wsSh.Protect Password:="1111", AllowFiltering:=True, UserInterfaceOnly:=True
Для применения этого кода в своих книгах необходимо будет лишь изменить(добавить, удалить, вписать другие имена) имена листов в этой строке: Array("Январь", "Февраль", "Март")
Примечание: Описанный метод защиты имеет одно существенное ограничение: его невозможно использовать в книге с общим доступом(Рецензирование -Доступ к книге), т.к. при общем доступе существуют ограничения, среди которых и такое, которое запрещает изменять параметры защиты для книги в общем доступе.
Excel как группировать на защищенном листе
Как оставить возможность работать с группировкой/структурой на защищенном листе?
Наверняка многие уже сталкивались с ситуацией, когда необходимо защитить лист от внесения изменений в ячейки(Рецензирование(Review) —Защитить лист(Protect Sheet) — читать подробнее про защиту листа), на котором уже имеются сгруппированные в структуру данные. И при установке такой защиты теряется возможность работы с этой самой группировкой/структурой. Если не знаете, что такое структура(еще её называют группировка): это такие плюсики левее строк/выше столбцов, при нажатии на которые раскрываются скрытые строки/столбцы:
Но что делать, если нужна и защита и возможность структурой пользоваться? Т.е. чтобы пользователь мог просмотреть все в удобной форме, но не смог ничего изменить. Одновременно и просто и не очень.
Если вы не знакомы с макросами и VBA, то обязательно пройдите по ссылкам из инструкции ниже — эти знания потребуются, чтобы сделать все правильно и получить корректный результат. Итак, чтобы разрешить использовать структуру на защищенном листе необходимо:
- создать в книге стандартный модуль(Alt+F11 —Insert —Module)
- разместить в нем нижеприведенный код:
Код сам устанавливает защиту на лист(не надо перед его выполнением устанавливать защиту вручную!), но при этом разрешает использовать группировку.
Основную роль здесь играет параметр UserInterfaceOnly, который говорит Excel-ю, что коды VBA могут выполнять определенные действия, не снимая защиты методом Unprotect. А второй важный пункт — EnableOutlining = True. Он как раз и включает возможность использования группировки. Как ни странно, но без UserInterfaceOnly он не работает. Поэтому важно применять их оба.
Код выше устанавливает такую защиту только на активный лист книги. Но можно указать лист явно(например установить защиту на лист с именем Лист1 в активной книге):
Так же приведенный код можно еще чуть модернизировать и разрешить пользователю помимо изменения ячеек еще и использовать автофильтр:
Можно разрешить и иные действия(выделение незащищенных ячеек, выделение защищенных ячеек, форматирование ячеек, вставку строк, вставку столбцов и т.д. Чуть подробнее про доступные параметры можно узнать в статье Защита листов и ячеек в MS Excel). А как будет выглядеть строка кода с разрешенными параметрами можно узнать, записав макрорекордером установку защиты листа с нужными параметрами:
После этого получится строка вроде такой:
здесь я разрешил использовать автофильтр( AllowFiltering:=True ), вставлять строки( AllowInsertingRows:=True ) и столбцы( AllowInsertingColumns:=True ).Чтобы добавить возможность изменять данные ячеек только через код VBA, останется добавить параметр UserInterfaceOnly:=True и установить EnableOutlining = True:
и так же неплохо бы добавить и пароль для снятия защиты, т.к. запись макрорекордером не записывает пароль:
Самая большая ложка дегтя заключается в том, что параметр UserInterfaceOnly сбрасывается сразу после закрытия книги. Т.е. если установить таким образом защиту на лист и закрыть книгу, то при следующем открытии этой защиты уже не будет — останется лишь стандартная защита, а группировка работать не будет. Что ставит под сомнение полезность подобного подхода, потому как обычно такое применяется для других пользователей, которые как правило далеки от макросов и даже слушать не станут, что мы там будем им предлагать выполнить. Поэтому, если необходимо такую защиту видеть постоянно и не только у себя на компьютере, то данный макрос лучше всего прописывать на событие открытия книги(модуль ЭтаКнига(ThisWorkbook)). Т.е. приведенный ниже код в обязательном порядке должен быть именно в модуле ЭтаКнига(ThisWorkbook) на событие Workbook_Open. Это заставит код установки защиты на лист выполняться автоматически при открытии книги. Т.е. конечному пользователю не надо будет ничего нажимать для его запуска: открыл книгу — код сам запустился, все работает.
Собственно, сам код защиты, срабатывающий при открытии книги:
Правда куда чаще необходимо устанавливать одинаковую защиту на все листы книги. Сделать это можно кодом ниже, который так же должен быть размещен в модуле ЭтаКнига(ThisWorkbook):
Плюс во избежание ошибок лучше перед установкой защиты снимать ранее установленную(если она была):
Если же защиту необходимо установить только на конкретные листы, имена которых заранее известны, то можно использовать чуть иной подход — использовать массивы:
Для применения этого кода в своих книгах необходимо будет лишь изменить(добавить, удалить, вписать другие имена) имена листов в этой строке: Array(«Январь», «Февраль», «Март»). Записывать обязательно в кавычках.
Примечание: Описанный метод защиты имеет одно существенное ограничение: его невозможно использовать в книге с общим доступом(Рецензирование -Доступ к книге), т.к. при общем доступе существуют ограничения, среди которых и такое, которое запрещает изменять параметры защиты для книги в общем доступе.
Также см.:
Как защитить лист от пользователя, но не от макроса?
Защита листов и ячеек в MS Excel
Защита листов/снятие защиты
Снять защиту с листа(без пароля)
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика
Как сгруппировать и разгруппировать строки на защищенном листе?
Как мы все знаем, на защищенном листе существует множество ограничений для применения некоторых операций. Например, мы не можем переключаться между сгруппированными и разгруппированными данными. Есть ли способ сгруппировать или разгруппировать строки на защищенном листе?
Группировка и разгруппировка строк на защищенном листе с кодом VBA
Группировка и разгруппировка строк на защищенном листе с кодом VBA
Возможно, нет другого хорошего способа решить эту проблему, кроме использования кода VBA, сделайте следующее:
1. Активируйте свой рабочий лист, который вы хотите использовать, убедитесь, что рабочий лист еще не защищен.
2. Затем удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
3. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: группировка и разгруппировка строк на защищенном листе
4. Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно с напоминанием о вводе пароля для защиты текущего рабочего листа. Смотрите скриншот:
5. Затем нажмите OK, ваш лист был защищен, но вы можете развернуть и сжать символы структуры на этом защищенном листе, см. снимок экрана:
Внимание: Если ваш рабочий лист уже защищен, этот код не будет работать.
Защита листа и ячеек в Excel
На лист можно поставить защиту, при которой все могут просматривать содержание листа, но нельзя ничего в нем изменять. Так же можно заблокировать все, кроме диапазона ячеек для ввода данных. А иногда можем просто скрыть формулы или источники данных.
Как поставить защиту в Excel на лист
В таблице данных защитим ячейки листа от изменений, данных в шапке и строке итоговых значений.
- Выделите диапазон ячеек B2:B6 и вызовите окно «Формат ячеек» (CTRL+1). Перейдите на вкладку «Защита» и снимите галочку на против опции «Защищаемая ячейка». Нажмите ОК.
- Выберите инструмент «Рицензирование»-«Защитить лист».
- В появившемся диалоговом окне «Защита листа» установите галочки так как указано на рисунке. То есть 2 опции оставляем по умолчанию, которые разрешают всем пользователям выделять любые ячейки. А так же разрешаем их форматировать, поставив галочку напротив «форматирование ячеек». При необходимости укажите пароль на снятие защиты с листа.
Теперь проверим. Попробуйте вводить данные в любую ячейку вне диапазона B2:B6. В результате получаем сообщение: «Ячейка защищена от изменений». Но если мы захотим отформатировать любую ячейку на листе (например, изменить цвет фона) – нам это удастся без ограничений. Так же без ограничений мы можем делать любые изменения в диапазоне B2:B6. Как вводить данные, так и форматировать их.
Как видно на рисунке, в окне «Защита листа» содержится большое количество опций, которыми можно гибко настраивать ограничение доступа к данным листа.
Как скрыть формулу в ячейке Excel
Часто бывает так, что самое ценное на листе это формулы, которые могут быть достаточно сложными. Данный пример сохраняет формулы от случайного удаления, изменения или копирования. Но их можно просматривать. Если перейти в ячейку B7, то в строке формул мы увидим: «СУММ(B2:B6)» .
Теперь попробуем защитить формулу не только от удаления и редактирования, а и от просмотра. Решить данную задачу можно двумя способами:
- Запретить выделять ячейки на листе.
- Включить скрытие содержимого ячейки.
Рассмотрим, как реализовать второй способ:
- Если лист защищенный снимите защиту выбрав инструмент: «Рецензирование»-«Снять защиту листа».
- Перейдите на ячейку B7 и снова вызываем окно «Формат ячеек» (CTRL+1). На закладке «Защита» отмечаем опцию «Скрыть формулы».
- Включите защиту с такими самыми параметрами окна «Защита листа» как в предыдущем примере.
Теперь переходим на ячейку B7 и убеждаемся в том, что в строке формул ничего не отображается. Даже результат вычисления формулы.
Примечание. Закладка «Защита» доступна только при незащищенном листе.
Как скрыть лист в Excel
Допустим нам нужно скрыть закупочные цены и наценку в прайс-листе:
- Заполните «Лист1» так как показано на рисунке. Здесь у нас будут храниться закупочные цены.
- Скопируйте закупочный прайс на «Лист2», а в место цен в диапазоне B2:B4 проставьте формулы наценки 25%: =Лист1!B2*1,25.
- Щелкните правой кнопкой мышки по ярлычке листа «Лист1» и выберите опцию «Скрыть». Рядом же находится опция «Показать». Она будет активна, если книга содержит хотя бы 1 скрытый лист. Используйте ее, чтобы показать все скрытие листы в одном списке. Но существует способ, который позволяет даже скрыть лист в списке с помощью VBA-редактора (Alt+F11).
- Для блокировки опции «Показать» выберите инструмент «Рецензирование»-«Защитить книгу». В появившемся окне «Защита структуры и окон» поставьте галочку напротив опции «структуру».
- Выделите диапазон ячеек B2:B4, чтобы в формате ячеек установить параметр «Скрыть формулы» как описано выше. И включите защиту листа.
Внимание! Защита листа является наименее безопасной защитой в Excel. Получить пароль можно практически мгновенно с помощью программ для взлома. Например, таких как: Advanced Office Password Recovery – эта программа позволяет снять защиту листа Excel, макросов и т.п.
Полезный совет! Чтобы посмотреть скрытые листы Excel и узнать их истинное количество в защищенной книге, нужно открыть режим редактирования макросов (Alt+F11). В левом окне «VBAProject» будут отображаться все листы с их именами.
Но и здесь может быть закрыт доступ паролем. Для этого выбираем инструмент: «Tools»-«VBAProjectProperties»-«Protection» и в соответствующих полях вводим пароль. С другой стороны, если установленные пароли значит, книга скрывает часть данных от пользователя. А при большом желании пользователь рано или поздно найдет способ получить доступ этим к данным. Об этом следует помнить, когда Вы хотите показать только часть данных, а часть желаете скрыть! В данном случае следует правильно оценивать уровень секретности информации, которая предоставляется другим лицам. Ответственность за безопасность в первую очередь лежит на Вас.
Примечание. Группировка при защите листа Excel – не работает при любых настройках. Сделать доступную в полноценном режиме функцию группировки на защищенном листе можно только с помощью макросов.