Lost your password?


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

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

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

  1. создать в книге стандартный модуль(Alt+F11 -Insert -Module)
  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)). Т.е. приведенный ниже код в обязательном порядке должен быть именно в модуле ЭтаКнига(ThisWorkbook) на событие Workbook_Open. Это заставит код установки защиты на лист выполняться автоматически при открытии книги. Т.е. конечному пользователю не надо будет ничего нажимать для его запуска: открыл книгу - код сам запустился, все работает.
Собственно, сам код защиты, срабатывающий при открытии книги:

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

Плюс во избежание ошибок лучше перед установкой защиты снимать ранее установленную(если она была):

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 ссылки статистика
Обсуждение: 125 комментариев
  1. Юрий:

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

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

  3. Юрий:

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

  4. Юрий:

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

    • Ирина:

      Пожалуйста, скинтье код с паролем весь, полдня голову ломаю, без пароля все ок, а вот с паролем ничего(((( очень нужно
      спс

      • Ирина, код с паролем есть в статье, в самом низу. Что не получается?

        • Ирина:

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

        • Ирина:

          Все!! получилось! Я код в модуль вставляла, и потом в "эта книга" и все ок спасибо огромное!!!!!

          • Ирина:

            ((( извините, что достаю, но когда книга была открыта второй раз, все сбилось... Пароль требует, а группировка не работает. В чем моя ошибка?

          • Ирина, а я как должен понять, в чем ошибка, если Вы не пишите ничего, кроме того, что не работает? Статью внимательно прочитали? Код где расположен? Код как выглядит?

  5. Дмитрий:

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

  6. Дмитрий:

    Поставил Saved = True после установки защиты, вроде стало как надо.
    Насколько грамотно такое решение?

  7. Дмитрий, это правильно решение и самое оптимальное в данном случае. Сам бы посоветовал именно так.

    • Иван:

      Я правильно понимаю, что результатом этого решения является отсутствие запроса на сохранение при закрытии листа, даже если изменения в данных листа присутствуют?

  8. Сергей:

    Дмитрий, благодарю, что вы так долго поддерживаете эту тему, отвечая на главный вопрос жизни "а у меня не работает, что делать?"
    Вот и у меня. Все перечитал 5 раз, и статью и чат. Все перепробовал. Не работает йцукен.
    Я не программист, просто юзер. Прошу помочь.

    После всех танцев с бубном. В заблокированном файле строки не раскрываются. Excel 2013.

  9. Сергей, не уверен, что все перепробовали. И не понял, что значит "Не работает йцукен".
    Скорее всего Вы код вставляли не в модуль книги. А надо именно в него.

    • Татьяна:

      Дмитрий, здравствуйте! Очень надеюсь, что моя проблема, с которой я столкнулась - решаема, и Вы мне поможете советом)) У меня есть файл, который содержит в себе данные по прибыли (планы, факты по клиентам, по месяцам) менеджеров и отделов, который с помощью макросов обновляется. Затем этот файл "нарезается" на отдельные книги по менеджерам и отделам, чтобы каждый человек мог видеть свою часть и запланировать свои продажи на след месяц (создается новая книга, туда вставляется нужный кусок и эта книга сохраняется под нужным именем), но тут возникла потребность защитить лист (чтобы они не смогли менять поставленные им планы руководителем), но с возможностью работать с некоторыми ячейками и структурой, и т к файл сохраняется в новой книге, то выше описанный способ не подходит т к это другая книга. Есть ли еще какие-то варианты? Заранее спасибо!

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

  10. Динара:

    Дмитрий, спасибо, очень полезная информация
    но не поняла как оставить возможность пользователю вставлять строки (копировать строки) после защиты листа,
    Если можно, пожалуйста, напишите код и куда именно "после основного" его вставить.
    В качестве основного я использовала код защиты сразу на все листы книги
    "Private Sub Workbook_Open()
    Dim wsSh As Object
    For Each wsSh In Me.Worksheets
    Protect_and_Structure wsSh
    Next wsSh
    End Sub
    Sub Protect_and_Structure(wsSh As Worksheet)
    wsSh.Unprotect
    wsSh.EnableOutlining = True
    wsSh.Protect Contents:=True, Scenarios:=True, UserinterfaceOnly:=True
    End Sub

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 Яндекс.Метрика
© 2024 Excel для всех   Войти