Как разрешить группировку на защищенном листе
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как разрешить группировку на защищенном листе
Здравствуйте!
При защите листа, раскрытие и скрытие, группированных строк и столбцов, становится невозможным. Есть ли способ решить проблему?
Здравствуйте!
При защите листа, раскрытие и скрытие, группированных строк и столбцов, становится невозможным. Есть ли способ решить проблему? SergeantTLT
Сообщение Здравствуйте!
При защите листа, раскрытие и скрытие, группированных строк и столбцов, становится невозможным. Есть ли способ решить проблему? Автор — SergeantTLT
Дата добавления — 14.01.2015 в 11:14
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Замечаний: 0% ±
Excel 2019
Этот вопрос неоднократно встречается у папы «Google» на разных форумах, в том числе и на нашем:
#Я_НАУЧУСЬ_ИСПОЛЬЗОВАТЬ_ПОИСК
Защищаете с помощью макроса:
[vba]
Sub Защитить()
Const MyPassword = «123»
With ActiveSheet
.EnableOutlining = True
.Protect Password:=MyPassword, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
End With
End Sub
[/vba]
и все отлично работает.
Этот вопрос неоднократно встречается у папы «Google» на разных форумах, в том числе и на нашем:
#Я_НАУЧУСЬ_ИСПОЛЬЗОВАТЬ_ПОИСК
Защищаете с помощью макроса:
[vba]
Sub Защитить()
Const MyPassword = «123»
With ActiveSheet
.EnableOutlining = True
.Protect Password:=MyPassword, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
End With
End Sub
[/vba]
и все отлично работает. DJ_Marker_MC
К сообщению приложен файл: marker.xls (37.0 Kb)
Сообщение Этот вопрос неоднократно встречается у папы «Google» на разных форумах, в том числе и на нашем:
#Я_НАУЧУСЬ_ИСПОЛЬЗОВАТЬ_ПОИСК
Защищаете с помощью макроса:
[vba]
Sub Защитить()
Const MyPassword = «123»
With ActiveSheet
.EnableOutlining = True
.Protect Password:=MyPassword, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
End With
End Sub
[/vba]
и все отлично работает. Автор — DJ_Marker_MC
Дата добавления — 14.01.2015 в 13:10
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Замечаний: 20% ±
Excel 2010
Большое спасибо.
Меня интересовал способ без использования макросов. Видимо такого способа нет.
Хотя оно к лучшему: с Вашего поста началось моё изучение VBA.
Прошу прощения. Выявилась проблема: после сохранения книги, с включенной защитой листа, и последующим открытием, защита опять таки не даёт раскрывать группированные столбцы. Приходится снимать защиту и по новой включать.
Подскажите, пожалуйста как обойти данную проблему.
Большое спасибо.
Меня интересовал способ без использования макросов. Видимо такого способа нет.
Хотя оно к лучшему: с Вашего поста началось моё изучение VBA.
Прошу прощения. Выявилась проблема: после сохранения книги, с включенной защитой листа, и последующим открытием, защита опять таки не даёт раскрывать группированные столбцы. Приходится снимать защиту и по новой включать.
Подскажите, пожалуйста как обойти данную проблему. SergeantTLT
Сообщение отредактировал SergeantTLT — Среда, 14.01.2015, 15:48
Сообщение Большое спасибо.
Меня интересовал способ без использования макросов. Видимо такого способа нет.
Хотя оно к лучшему: с Вашего поста началось моё изучение VBA.
Прошу прощения. Выявилась проблема: после сохранения книги, с включенной защитой листа, и последующим открытием, защита опять таки не даёт раскрывать группированные столбцы. Приходится снимать защиту и по новой включать.
Подскажите, пожалуйста как обойти данную проблему. Автор — SergeantTLT
Дата добавления — 14.01.2015 в 15:33
Как оставить возможность работать с группировкой/структурой на защищенном листе?
Наверняка многие уже сталкивались с ситуацией, когда необходимо защитить лист от внесения изменений в ячейки(Рецензирование (Review) —Защитить лист (Protect Sheet) — читать подробнее про защиту листа), на котором уже имеются сгруппированные в структуру данные. И при установке такой защиты теряется возможность работы с этой самой группировкой/структурой. Если не знаете, что такое структура(еще её называют группировка): это такие плюсики левее строк/выше столбцов, при нажатии на которые раскрываются скрытые строки/столбцы:
Но что делать, если нужна и защита и возможность структурой пользоваться? Т.е. чтобы пользователь мог просмотреть все в удобной форме, но не смог ничего изменить. Одновременно и просто и не очень.
Если вы не знакомы с макросами и VBA, то обязательно пройдите по ссылкам из инструкции ниже — эти знания потребуются, чтобы сделать все правильно и получить корректный результат. Итак, чтобы разрешить использовать структуру на защищенном листе необходимо:
- создать в книге стандартный модуль( Alt + F11 —Insert —Module)
- разместить в нем нижеприведенный код:
Sub ProtectShWithOutline() ActiveSheet.EnableOutlining = True ActiveSheet.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True End Sub
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() 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
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
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
и так же неплохо бы добавить и пароль для снятия защиты, т.к. запись макрорекордером не записывает пароль:
ActiveSheet.EnableOutlining = True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"
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
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
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
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
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(«Январь», «Февраль», «Март») . Записывать обязательно в кавычках.
Примечание: Описанный метод защиты имеет одно существенное ограничение: его невозможно использовать в книге с общим доступом(Рецензирование -Доступ к книге), т.к. при общем доступе существуют ограничения, среди которых и такое, которое запрещает изменять параметры защиты для книги в общем доступе.
Статья помогла? Поделись ссылкой с друзьями!
Как разрешить группировку на защищенном листе
| если я закрываю книгу и открываю ее снова, то при попытке раскрыть группу, у меня вылазит сообщение, что данное действие нельзя произвести на защищенном листе |
Все правильно. Опция UserInterfaceOnly:=True не сохраняется. Поэтому, при открытии книги ее нужно повторять. Т.е. снять защиту и вновь установить ее с нужными параметрами. Так, например, пусть в книге есть макрос, устанавливающий защиту:
Sub Защитить() Const MyPassword = "abcd" With ActiveSheet .Unprotect Password:=MyPassword .EnableOutlining = True .Protect Password:=MyPassword, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True End With End SubТогда в модуль «Эта книга» вставьте код:
Private Sub Workbook_Open() Защитить End SubЧем шире угол зрения, тем он тупее.