Vba не работает структура при защите листа
Добрый день, господа!
Есть необходимость работы со структурой (группировкой) при защищенном листе.
На параллельных сайтах нашел вот такой код:
[vba]
[/vba]
, но он не совсем подходит. Не нужно никаких всплывающих запросов паролей при открытии книги и т.д.
Нужен просто кусок кода, который можно положить в модуль книги или листа (без разницы), дающий возможность работать со структурой, когда листы защищены.
Добрый день, господа!
Есть необходимость работы со структурой (группировкой) при защищенном листе.
На параллельных сайтах нашел вот такой код:
[vba]
[/vba]
, но он не совсем подходит. Не нужно никаких всплывающих запросов паролей при открытии книги и т.д.
Нужен просто кусок кода, который можно положить в модуль книги или листа (без разницы), дающий возможность работать со структурой, когда листы защищены.
Сообщение Добрый день, господа!
Есть необходимость работы со структурой (группировкой) при защищенном листе.
На параллельных сайтах нашел вот такой код:
[vba]
[/vba]
, но он не совсем подходит. Не нужно никаких всплывающих запросов паролей при открытии книги и т.д.
Нужен просто кусок кода, который можно положить в модуль книги или листа (без разницы), дающий возможность работать со структурой, когда листы защищены.
Спасибо заранее! Автор — ArkaIIIa
Дата добавления — 25.04.2016 в 16:34
Источник
Vba не работает структура при защите листа
спасибо огромное за совет. сделал как предложено:
Private Sub Workbook_Open()
Sheets(«имя листа»).EnableOutlining = True
Sheets(«имя листа»).Protect Contents:=True, userInterfaceOnly:=True
End Sub
при этом сразу идет запрос на введение пароля. и соответственно, если его не вводить история повторяется(«нельзя использовать данную команду на защищенном листе»)
просто не совсем знаком с VBA и функциями в нем.
какие неточности допустил? по helpy посмотрел, но ничего не понял.
Спасибо еще раз.. попробовал
но вопрос то в другом!
необходимо чтобы при включенной защите работала структура данных и также оставались под защитой остальные ячейки.
может я не достачтно точно описал требуемую задачу?
попробую еще раз:
необходимо защитить инфу от редактирования стандартными способами и скрыть формулы или иные преобразования — для этого использую защиту данных паролем от редактирования(разрешение стоит только на выделение данных)
при этом на листах есть структуры подчиненности(группировки данных). и если пароль включен — то структура не работает.
файл предлагается в пользование обычным пользователям — задача которых только смотреть
и еще автофильтр добавить туда же. он тоже не функционирует при включенной защите.
и при возможности им закрыть доступ на вход в VBA
а вообще в итоге вообще нужна полная зашита файла эксель от любого копирования и клонирования. есть какие то способы как это сделать?
реально ли все это?
Вложения
| Защита.rar (6.4 Кб, 550 просмотров) |
Огромное спасибо. сам бы вряд ли докапался бы.
примерный нагладно иллюстрирует задачу.
SAS888, могу попросить еще об одной доработке? (не знаю ответите или нет, но в любом случае спасибо)
задача:
1. имеющийся файл необходимо защитить от редакции страниц(ее параметров, колонтитулов итд)
2. защита от копирования(любого) определенных ячеек листа/листов, при возможности их выделения(защищенные и незащищенные)
Вложения
| Защита_2.rar (11.8 Кб, 408 просмотров) |
SAS888, утро доброе!
спасибо, попробовал, пример отличный. тока у меня глюк какой-то произошел и получилось следующее.
книга открывается и отображается в полусвернутом состоянии, а хотелось бы чтобы она на весь экран разворачивалась. при этом если самому запустить макрос(возврата состояния) ничего не менятеся, кроме доступа к параметрам страницы..
не знаю как произошел данный глюк. но это факть. могу скрин оставить. поможете?
Источник
Работа макроса на защищённом листе
Помощь в написании контрольных, курсовых и дипломных работ здесь.
Вложения
| Удобный поиск с добавлением.7z (28.6 Кб, 15 просмотров) |
Работа макроса на защищенном листе
Доброго дня. Помогите пожалуйста доработать макрос, чтобы он не ругался при работе на защищенном.
Добрый день, уважаемые. Такая проблема. Скажем есть макрос, который удаляет теги из ячеек. Sub.
Помогите сделать так чтобы макрос по датам срабатывал при защищенном листе.
Добавление строк на защищенном листе программно
Привет! Ситуация такая: в Excel есть защищаемая ячейка с формулой СУММ и под ней ячейки не.
spasen1973, ну и в каком месте ваши защищённые листы, попробовал вводить данные — вводятся.
Да это не главное. Где ваш поиск, который не работает или вы считаете, что мы должны разобрать ваш код по косточкам, восхитится им и сообразить где этот поиск?
Добавлено через 10 минут
и интересно, почему ваш выпадающий фруктовый список выпадает на листе городов, а на листе с фруктами (Списки) не выпадает?
Решение
я там переделывал страницы у себя, посмотрите мой Лист1 соответствует вашему?
Добавлено через 11 минут
spasen1973, ещё можно выводить вашу форму только на существующих значениях столбца и на первом пустом внизу столбца, чтобы не было реакции на любую пустую ячейку столбца и ваши категории будут вводиться подряд без пустых в средине. Тогда это будет так для лист1
Добавлено через 24 минуты
в коде кнопки закрыть тоже лучше восстановить защиту листа. Т.е на всех кнопках, из которых возможен выход из формы.
Добавлено через 42 минуты
проверил в кнопае закрыть восстановление защиты можно не ставить, всё равно идет закрытие через QueryClose
Источник
Vba не работает структура при защите листа
Как оставить возможность работать с группировкой/структурой на защищенном листе?
Для многих, наверное, не секрет, что если защитить лист от внесения изменений в ячейки и на листе имеется сгруппированные в структуру данные, то при установке обычной защиты(Рецензирование -Защитить лист) теряется возможность работы с этой структурой. Для тех, кто не совсем понимает, что такое структура(еще её называют группировка): это такие плюсики левее строк/выше столбцов, при нажатии на которые раскрываются скрытые строки/столбцы.
Однако часто бывает необходимо сделать так, чтобы наряду с защитой листа можно было еще и структурой пользоваться. Т.е. чтобы пользователь мог просмотреть все в удобной форме, но не смог ничего изменить. Так как же защитить лист и оставить возможность работы со структурой? Очень просто.
Если вы не знакомы с макросами и 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()
arr = Array(«Январь», «Февраль», «Март»)
For Each sSh in arr
S ub ProtectShWithOutline(wsSh As Worksheet)
wsSh.Protect Password:=»1111″, AllowFiltering:=True, UserInterfaceOnly:=True
Для применения этого кода в своих книгах необходимо будет лишь изменить(добавить, удалить, вписать другие имена) имена листов в этой строке: Array(«Январь», «Февраль», «Март»)
Примечание: Описанный метод защиты имеет одно существенное ограничение: его невозможно использовать в книге с общим доступом(Рецензирование -Доступ к книге), т.к. при общем доступе существуют ограничения, среди которых и такое, которое запрещает изменять параметры защиты для книги в общем доступе.
Источник