Версия для печати

Как оставить возможность работать со структурой на защищенном листе?

6504 Написать комментарий К комментариям
Что умеет Excel

 

Если Вы хотите защитить лист от внесения изменений и на листе имеется сгруппированные в структуру данные, то при установке обычной защиты теряется возможность работы с этой структурой. Для тех, кто не совсем понимает, что такое структура(еще её называют группировка): это такие плюсики левее строк, при нажатии на которые раскрываются скрытые строки. Так вот частенько очень необходимо, чтобы наряду с защитой листа можно было еще и структурой пользоваться. Т.е. чтобы пользователь мог просмотреть все в удобной форме, но не смог ничего изменить. Так как же защитить лист и оставить возможность работы со структурой? Очень просто.

Если Вы не знакомы с макросами и VBA, то Вам сначала необходимо:

  1. создать стандартный модуль
  2. разместить в нем нижеприведенный код: 
    Sub Protect_and_Structure()
    ActiveSheet.EnableOutlining = True
    ActiveSheet.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub
    Sub Protect_and_Structure()
    ActiveSheet.EnableOutlining = True
    ActiveSheet.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub

Выполнив данный код, Вы защитите активный лист, не потеряв возможности работать со структурой.Естественно можно указать любой лист вместо активного.

Примечание: данный макрос лучше всего прописывать на событие открытия книги(модуль ЭтаКнига(ThisWorkbook)), т.к. сразу после закрытия и открытия книги данная защита снимается.

Можно ставить защиту сразу на все листы книги при открытии таким кодом:

Private Sub Workbook_Open()
    Dim wsSh As Object
    For Each wsSh In Me.Sheets
        Protect_and_Structure wsSh
    Next wsSh
End Sub
Sub Protect_and_Structure(wsSh As Object)
    wsSh.Unprotect
    wsSh.EnableOutlining = True
    wsSh.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub
Private Sub Workbook_Open()
    Dim wsSh As Object
    For Each wsSh In Me.Sheets
        Protect_and_Structure wsSh
    Next wsSh
End Sub
Sub Protect_and_Structure(wsSh As Object)
    wsSh.Unprotect
    wsSh.EnableOutlining = True
    wsSh.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub

Данный код вставляется в модуль ЭтаКнига.

Ну и если Вам только на один лист надо установить, то убираем цикл и вызываем процедуру только для нужного листа:

Private Sub Workbook_Open()
       Protect_and_Structure Me.Sheets("Лист1")
End Sub
Sub Protect_and_Structure(wsSh As Object)
       wsSh.Unprotect
       wsSh.EnableOutlining = True
       wsSh.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub
Private Sub Workbook_Open()
       Protect_and_Structure Me.Sheets("Лист1")
End Sub
Sub Protect_and_Structure(wsSh As Object)
       wsSh.Unprotect
       wsSh.EnableOutlining = True
       wsSh.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub

Чтобы помимо этого можно было разрешить пользователям производить иные стандартные операции(форматирование ячеек, форматирование строк, форматирование столбцов, вставка строк и т.д.) достаточно просто записать макрос по установке защиты листа и посмотреть как будет выглядеть код. Код ниже вставлен в основной после записи макрорекордером установки защиты с возможностью форматировать столбцы:

Sub Protect_and_Structure(wsSh As Object)
       wsSh.Unprotect
       wsSh.EnableOutlining = True
       wsSh.Protect Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, UserinterfaceOnly:=True
End Sub
Sub Protect_and_Structure(wsSh As Object)
       wsSh.Unprotect
       wsSh.EnableOutlining = True
       wsSh.Protect Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, UserinterfaceOnly:=True
End Sub

Чтобы добавить в код установку пароля, записать его следует так:

Sub Protect_and_Structure(wsSh As Object)
       wsSh.Unprotect
       wsSh.EnableOutlining = True
       wsSh.Protect Password:="1234", Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub
Sub Protect_and_Structure(wsSh As Object)
       wsSh.Unprotect
       wsSh.EnableOutlining = True
       wsSh.Protect Password:="1234", Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub

Также см.:
→Как защитить лист от пользователя, но не от макроса?
→Защита листов/снятие защиты

Поддержать автора сайта
Поделиться ссылкой
  1. Денис
    4 Февраль 2012 в 04:42 | #1
    Ответить | Цитата

    Привет!
    Есть файл со структурой, в которой все листы защищены паролем, к примеру 11111 .Для защиты используется следующий код:

    Private Sub Workbook_Open()
    Dim wsSh As Object
    For Each wsSh In Me.Sheets
        Protect_and_Structure wsSh
    Next wsSh
End Sub
Sub Protect_and_Structure(wsSh As Object)
    wsSh.Unprotect "11111"
    wsSh.EnableOutlining = True
    wsSh.Protect Password:="11111", Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub
    Private Sub Workbook_Open()
    Dim wsSh As Object
    For Each wsSh In Me.Sheets
        Protect_and_Structure wsSh
    Next wsSh
End Sub
Sub Protect_and_Structure(wsSh As Object)
    wsSh.Unprotect "11111"
    wsSh.EnableOutlining = True
    wsSh.Protect Password:="11111", Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub

    столкнулся с проблемой того, что, для некоторых пользователей, для которых информация в файле должена быть защищена от редактирования, взащищенном листе безпрепятсвенно просматривают пароль в редакторе VBA и соответсвенно имеют доступ к редактированию.
    Вопрос — возможно ли решить эту проблему, (заблокировать просмотр кодов в VBA редакторе, в защищенном листе /книге) кроме того, как исключить работу со структурой в защищенном файле ?
    Спасибо.

  2. Дмитрий(Админ)
    4 Февраль 2012 в 11:49 | #2
    Ответить | Цитата

    А что мешает поставить пароль на проект VBA?В окне проекта VBA: Tools-VBA Project Properties-вкладка Protection

    А исключить работу со структурой еще проще — защищать лист как обычно, а не описанным в статье методом. Если хотите сделать это только для определенных пользователей, то как минимум необходимо знать точно этих пользователей(учетная запись на ПК, как минимум).

  3. Денис
    4 Февраль 2012 в 12:57 | #3
    Ответить | Цитата

    Дмитрий(Админ) :
    А что мешает поставить пароль на проект VBA?В окне проекта VBA: Tools-VBA Project Properties-вкладка Protection
    А исключить работу со структурой еще проще — защищать лист как обычно, а не описанным в статье методом. Если хотите сделать это только для определенных пользователей, то как минимум необходимо знать точно этих пользователей(учетная запись на ПК, как минимум).

    Дмитрий, спасибо. В принципе ничего не мешает , если знать об этой возможности. попробовал работает. По поводу структуры — возможно непонятно написал — как раз в этом и была проблема — что нужна возможность работать со структурой в защищенном листе, но об возможности защищать паролем от промотра кодов в редакторе VBA я незнал, в этом и заключатся вопрос, он решен. Еще раз, большое Вам человеческое спасибо.

  4. S_Brodsky
    18 Февраль 2012 в 19:55 | #4
    Ответить | Цитата

    Скажите пожалуйста, какую команду необходимо добавить в макрос, для того чтобы пользователь мог добавить строки в защищённый лист не снимаяя защиту?

  5. Дмитрий(Админ)
    18 Февраль 2012 в 20:15 | #5
    Ответить | Цитата

    Запишите макрорекордером установку защиты с нужными параметрами и посмотрите код.

  6. S_Brodsky
    18 Февраль 2012 в 21:20 | #6
    Ответить | Цитата 
    Private Sub Workbook_Open()
    Dim wsSh As Object
    For Each wsSh In Me.Sheets
        Protect_and_Structure wsSh
    Next wsSh
End Sub
Sub Protect_and_Structure(wsSh As Object)
    wsSh.Unprotect Password:="admin"
    wsSh.EnableOutlining = True
    wsSh.Protect Password:="admin", DrawingObjects:=True, Contents:=True, Scenarios:=False, AllowInsertingColumns:=True, AllowInsertingRows:=True, UserInterfaceOnly:=True
End Sub
    Private Sub Workbook_Open()
    Dim wsSh As Object
    For Each wsSh In Me.Sheets
        Protect_and_Structure wsSh
    Next wsSh
End Sub
Sub Protect_and_Structure(wsSh As Object)
    wsSh.Unprotect Password:="admin"
    wsSh.EnableOutlining = True
    wsSh.Protect Password:="admin", DrawingObjects:=True, Contents:=True, Scenarios:=False, AllowInsertingColumns:=True, AllowInsertingRows:=True, UserInterfaceOnly:=True
End Sub

    Всё вставил, появился пункт в контекстном меню, но при нажатии Добавить скопированные ячейки выдаёт сообщение что необходимо снять защиту

  7. Дмитрий(Админ)
    18 Февраль 2012 в 21:29 | #7
    Ответить | Цитата

    S_Brodsky, вставка строк и вставка строк скопированных — разные вещи. Вставить скопированные строки при активной защите листа нельзя — какая же это тогда защита, если любой может просто взять и скопировать то, что хочет?

  8. S_Brodsky
    18 Февраль 2012 в 21:47 | #8
    Ответить | Цитата

    подскажите, а есть ли выход из ситуации или это невозможно в Excel??

  9. Дмитрий(Админ)
    18 Февраль 2012 в 22:26 | #9
    Ответить | Цитата

    Невозможного нет, но реализация желаемого Вам выходит далеко за рамки данной статьи.

  10. Сергей
    29 Март 2012 в 13:33 | #10
    Ответить | Цитата

    Здравствуйте Дмитрий!
    Помогите, пожалуйста, решить такую проблему — при защите листа (обычным способом) не обновляется сводная таблица. Хотя, когда устанавливал защиту, поставил галочку на разрешении использовать отчеты сводных таблиц, но команда «обновить» — неактивна. Может это можно обойти макросом?

  11. Сергей
    29 Март 2012 в 16:59 | #11
    Ответить | Цитата

    да, и с ячеек всего диапазона сводной таблицы снимаю защиту — но не помогает все равно

  12. Дмитрий(Админ)
    30 Март 2012 в 09:09 | #12
    Ответить | Цитата

    Сергей, первое: какое отношение Ваш вопрос имеет к этой теме?
    Второе: если установлена защита, то обновить данные сводной нельзя. Можно лишь изменять расположение элементов по существующим данным, сворачивать в группы и совершать иные действия для изменения вида, но никак не для изменения исходных данных таблицы.

  13. Сергей
    30 Март 2012 в 11:53 | #13
    Ответить | Цитата

    Спасибо. Сорри что не по теме, но нигде больше не нашел подходящих тем и грамотных консультаций.

  14. Kirchos
    3 Май 2012 в 14:31 | #14
    Ответить | Цитата

    хинт можно посадить на кнопку порядок дейсвий: снять защиту — обновить пивот — поставить защиту

Страницы комментариев

Комментарий будет добавлен после проверки администратором.
Комментарии, не имеющие отношения к комментируемой статье, будут удаляться без уведомления и объяснения причин. Если есть вопрос по проблеме в Excel- добро пожаловаться на Форум