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

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

25034
Написать комментарий К комментариям
Что умеет 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 "1234"
       wsSh.EnableOutlining = True
       wsSh.Protect Password:="1234", Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub
Sub Protect_and_Structure(wsSh As Object)
       wsSh.Unprotect "1234"
       wsSh.EnableOutlining = True
       wsSh.Protect Password:="1234", Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
End Sub

Хочу обратить внимание, что тогда и снимать защиту тоже следует с указанием пароля, как показано выше(wsSh.Unprotect "1234"). Если изначально пароль не установлен, то следует его установить вручную и запустить код, либо просто удалить строку wsSh.Unprotect "1234".

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



Поддержать автора сайта
  1. 29 Октябрь 2013 в 22:45 | #1

    Либо код помещается не в модуль ЭтаКнига, либо что-то записали не так.
    А ссылка не открывается — Доступ запрещен.

  2. Маргарита
    5 Ноябрь 2013 в 22:24 | #2

    Дмитрий, здравствуйте еще раз. ПОсомтрите пожалуйста здесь. я уже везде попробовала прописать:
    [IMG]http://pic4net.com/di-UF3TOO.jpg[/IMG]

  3. 5 Ноябрь 2013 в 22:38 | #3

    Маргарита — проще было бы в форум обратиться. Там хоть файл можно выложить. А на скрине — не надо прописывать везде — может возникнуть конфликт процедур. Код должен быть только в модуле ЭтаКнига. А не в модулях класса(ClassModule) или обычных(Modules) или модулях листа.

  4. Ильнур
    20 Февраль 2014 в 20:29 | #4

    Сделал все так как написали, но этот скрипт у меня не работает:
    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

    У меня xls2007. не могли бы вы взглянуть на мой фаил и подсказать что я делаю не так?

  5. Ильнур
    20 Февраль 2014 в 20:31 | #5

    @MadHouse
    Пропишите пожалуйста скрипт целиком, этот работает только для одной страницы:
    Sub Workbook_Open()

    With ActiveSheet
    .EnableOutlining = True
    .Protect Password:=1234, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
    End With

    End Sub

  6. 20 Февраль 2014 в 21:16 | #6

    Ильнур, прочитайте статью целиком и внимательно. Код должен располагаться в модуле книги.

  7. Ильнур
    21 Февраль 2014 в 00:10 | #7

    @Дмитрий(Админ)
    уже вдоль и поперек прочитал, код записываю в модуль «ЭтаКнига». первый код работает (для одной страницы), а второй нет…

  8. 21 Февраль 2014 в 00:39 | #8

    А я не знаю где Вы второй код взяли. Он вообще только для активного листа. В моей статье такой код не фигурирует. В самом конце есть код с применением пароля — и выглядит он иначе. Так что советую не вдоль и поперек читать, а слева-направо сверху вниз :-) (не примите за оскорбление — просто шутка).

  9. Юрий
    4 Март 2014 в 13:25 | #9

    Макрос работает, но если дать книге общий доступ — выключается. Или что-то делаю не так?

  10. 4 Март 2014 в 13:31 | #10

    Юрий, зайдите на сайт Microsoft и найдите ограничения для книг общего доступа. В общем доступе нельзя изменять имеющиеся параметры защиты листов и книг. Это означает, что код в модуле книги не будет применен и защита не будет установлена должным образом.
    В принципе, для получения ответа достаточно было бы пролистать комментарии — вопрос уже поднимался(не далее первой страницы комментариев, кстати).

  11. Юрий
    1 Апрель 2014 в 19:16 | #11

    Добрый день!
    К сожалению, последний код (с возможностью защиты паролем) не работает. Вставил его в модуль «Эта книга». Но при нажатии кнопки «Макросы», данный макрос не появляется в списке:(

  12. 1 Апрель 2014 в 19:18 | #12

    Юрий, прочитайте внимательно. Код в ЭтаКнига вставляется специально для того, чтобы он выполнялся автоматически при открытии книги, а не кнопками.

  13. Юрий
    2 Апрель 2014 в 14:54 | #13

    Дмитрий, не работает при открытии книги. @Дмитрий(Админ)

  14. Юрий
    2 Апрель 2014 в 15:11 | #14

    Дмитрий, прошу прощения, не сразу понял, что надо сначала вставить код «Private Sub Workbook_Open()
    Protect_and_Structure Me.Sheets(«Лист1″)
    «. Все заработало!@Юрий

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

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

Комментарий будет добавлен после проверки администратором.