Lost your password?


Хитрости »
Основные понятия (25)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (18)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (67)
Разное (42)
Баги и глюки Excel (4)

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

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

Если вы не знакомы с макросами и VBA, то обязательно пройдите по ссылкам из инструкции ниже. Итак, чтобы разрешить использовать структуру на защищенном листе необходимо:

  1. создать в книге стандартный модуль
  2. разместить в нем нижеприведенный код:
    Sub ProtectShWithOutline()
        ActiveSheet.EnableOutlining = True
        ActiveSheet.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
    End Sub
  3. Выполнить данный код(Alt+F8 -Protect_And_Structure)

Код сам устанавливает защиту на лист(не надо перед его выполнением устанавливать защиту вручную!), но при этом разрешает использовать группировку.
Основную роль здесь играет параметр 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()
    'на лист "Лист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

здесь я разрешил использовать автофильтр(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, Password:="1111"

Самая большая ложка дегтя заключается в том, что параметр UserInterfaceOnly сбрасывается сразу после закрытия книги. Т.е. если установить таким образом защиту на лист и закрыть книгу, то при следующем открытии защиты этой уже не будет - останется лишь стандартная защита. А значит группировка работать по прежнему не будет, что ставит под сомнение полезность подобного подхода, потому как обычно такое применяется для других пользователей, которые как правило далеки от макросов и даже слушать не станут, что мы там будем им предлагать выполнить. Поэтому, если необходимо такую защиту видеть постоянно и не только у себя на компьютере, то данный макрос лучше всего прописывать на событие открытия книги(модуль ЭтаКнига(ThisWorkbook)).
Сделать это можно таким кодом:

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)
    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

Если же защиту необходимо установить только на конкретные листы, имена которых заранее известны, то можно использовать чуть иной подход - использовать массивы:

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("Январь", "Февраль", "Март")

Примечание: Описанный метод защиты имеет одно существенное ограничение: его невозможно использовать в книге с общим доступом(Рецензирование -Доступ к книге), т.к. при общем доступе существуют ограничения, среди которых и такое, которое запрещает изменять параметры защиты для книги в общем доступе.

Также см.:
Как защитить лист от пользователя, но не от макроса?
Защита листов и ячеек в MS Excel
Защита листов/снятие защиты
Снять защиту с листа(без пароля)


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 121 комментарий
  1. Геннадий:

    А у меня не работает! Все равноне дает сворачивать и разворачивать созданные группы столбцов

  2. Так он и должен "давать" сворачивать и разворачивать структуру. При обычной защите этого сделать нельзя.

  3. Airi:

    У меня никакая защита не ставится((

  4. Andrey:

    Не работает, сворачивать и разворачивать группы столбцов не получается.
    to Дмитрий: Человек пробел не вставил

  5. Попробуйте добавить в конце: , UserinterfaceOnly:=True
    Я поправил в статье. Теперь при любых установках должно работать. Только советую прочитать статью "Как защитить лист от пользователя, но не от макроса?", чтобы понимать что означает строка UserinterfaceOnly
    Про пробел не понял.

  6. Дима:

    Уважаемый Дмитрий,
    подскажите, пожалуйста, а есть ли возможность аналогичного обхода для shared workbooks (общие книги)?
    Очень нужно.
    Спасибо.

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

  8. Pavel:

    Огромное спасибо, Дмитрий!

  9. Дмитрий:

    Все зашибись, но с такими модулями файл при открытии запрашивает пароль на снятие защиты листа. Вводишь пароль и, видимо, разблокируется только возможность группировки, защита ячеек остается включенной. НО!!! При этом дальнейшее снятие защиты листа производится уже без ввода пароля. Т.е. для работы с файлом придется пользователям сообщить пароль, а они уже далее смогут снять защиту со всего листа. Что же это за защита тогда?

  10. Дмитрий, проблема в том, что у меня озвученной проблемы нет. Если грамотно подойти к проблеме, то для того, чтобы установить защиту надо сначала снять уже имеющуюся. Так же кодом. Я лишь указал в статье, как можно сделать возможность работы со структурой на защищенном листе. Как это применить - дело творческое :-)
    "При этом дальнейшее снятие защиты листа производится уже без ввода пароля" - Прежде чем возмущаться посмотрите: а где же Вы в коде указываете пароль? Кажется нигде. А вот чтобы узнать где, можно выделить метод Protect и нажать клавишу F1 - тогда Вам будут доступны все аргументы метода.
    Или я не так понял проблему?

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2021 Excel для всех   Войти