Наверняка многие уже сталкивались с ситуацией, когда необходимо защитить лист от внесения изменений в ячейки(Рецензирование(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
Защита листов/снятие защиты
Снять защиту с листа(без пароля)

125 комментариев

  1. Здравствуйте Дмитрий. К сожалению я не программист, а обычный юзер. И прошу Вашей помощи.
    Я создал таблицу расчетов, в которой есть группировки, закрепленные области, шапки у столбцов и итоговые ячейки с объединенными ячейками. Книга в расширении .xlsx содержит только один лист. Можно ли защитить некоторые столбцы ( они с формулами) таблицы, чтобы оставить возможность работать с группировками на защищенном листе? И в какой последовательности нужно действовать - сначала ставить защиту, а потом вписать макрос или наоборот? Кстати, пробовал простым способом ставить защиту на столбец с объединенными ячейками, но не разрешает.
    Буду признателен, если бы Вы смогли написать необходимый код.
    Заранее благодарен!

    1. А в чем моя помощь должна заключаться? Все коды уже приведены. Вы бы статью внимательно сначала прочитали - там все разжевано и добавить нечего. В какой последовательности что делать, что делает макрос и как он это делает. Плюс ссылка на статью по защите ячеек в Excel есть: Защита листов и ячеек в MS Excel - там можно посмотреть как ставить защиту на отдельные ячейки и какие нюансы при этом возникают.

  2. Дмитрий, добрый день.
    спасибо за статью, очень помогла.
    написала код на все листы книги. при открытии книги происходит как бы пересчет, знаете как будто книга "дрожит" :) не знаю как правильно описать. как будто много данных пересчитывает. как бы мне избавится от этого? код ниже. вроде ничего лишнего? важно чтоб у пользователей осталась возможность форматировать ячейки, вставлять строки и пользоваться фильтром.
    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.Unprotect "qaz"
    wsSh.EnableOutlining = True
    wsSh.Protect Password:="qaz", Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
    wsSh.Protect AllowFormattingCells:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="qaz"
    End Sub

    1. Private Sub Workbook_Open()
Dim wsSh As Object
Application.Screenupdating = false
For Each wsSh In Me.Sheets
ProtectShWithOutline wsSh
Next wsSh
Application.Screenupdating = true
End Sub

      2. Дмитрий, помогите, пожалуйста, еще с одним вопросом, возможно в другую тему надо...
        в книге есть ячейки с выпадающим списком (данные - проверка данных - список). при копировании строки эта проверка пропадает, т.е. выпадающего списка нет. но это только в защищенной книге, если книга не защищена - все отлично копируется. нашла что это баг ексель.
        можно решить сей вопрос с помощью vba? к уже существующему коду (выше писала).
        спасибо

  3. Спасибо!
    Позвольте уточнить, почему у меня может ругаться на ключевое слово "Me" в строке:
    ProtectShWithOutline Me.Sheets(sSh)

    поменял в коде только 2 листа
    Sub ProtectShWithOutline(wsSh As Worksheet)
    wsSh.EnableOutlining = True
    wsSh.Protect Password:="1111", AllowFiltering:=True, UserInterfaceOnly:=True
    End Sub

  4. Дмитрий спасибо, ваш код очень помогает. Только не могу додумать как добавить в него, чтобы можно было редактировать отдельный диапазон ячеек?

      1. Правильно ли я понял, сначала в книге устанавливаю диапазоны для изменения ячеек, а после накладываю макрос. Разве макрос не отменит предыдущее действие?

        1. Александр, поняли правильно. Одно но: всегда можно сначала попробовать что предлагают, а потом уже писать вопросы, если они возникнут :)
          Не бойтесь пробовать, за это только плюсуется в карму в случае с программированием :)

  5. Добрый день,
    У меня код всё работает всё супер. Но в файле есть запросы от внешних источников через query, которые в свою очередь обновляют пивоты и после обновляет основные таблицы. Проблема в том что чтобы обновить все данные приходиться снимать на каждом sheet пароль обновлять и после опять закрывать паролем, что можно сделать в таком случае?

  6. Добрый день Дмитрий!
    Воспользовался макросом:
    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
    Все работает как надо, только вот надо добавить разрешение на форматирование ячеек(нужно делать заливку цветом определенных ячеек), подскажите пожалуйста. я совсем не специалист в этом деле. Заранее спасибо.

    1. Игорь, если прочитаете статью внимательно и до конца - ответ на свой вопрос найдете. Просто запишите макрорекордером какие действия пользователю разрешается делать и все. Пункт Форматирование ячеек вроде не сложно найти в этой форме.

      1. Добрый день Дмитрий!
        Воспользовался макросом:
        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, AllowFormattingCells:=True
        End Sub
        Все работает как надо, добавил в него возможность форматирования ячеек. Но вот проблема. Можно ли как то прописать конкретные ячейки где можно форматировать ячейки при защите листа? Получается сейчас везде где нет галочки "защищаемая ячейка" можно менять формат. Записать макрорекордером у меня не получилось, потому что стандартным методом конкретные ячейки так защитить нельзя (защита формата ячеек).
        Заранее спасибо!

        1. Владимир, в этом макросе точно нельзя такое прописать. Да и другие макросы тоже не сильно помогут, т.к. само по себе событие изменения формата из VBA нельзя отследить.

          1. Спасибо Дмитрий! Буду искать компромисс. Я правильно Вас понял, что нельзя поставить какой то диапазон (например один столбец), где только в нем можно форматировать ячейки?

  7. Добрый день!
    Спасибо за макрос! Очень полезный.
    Но есть два вопрос по его работе:
    1. когда макрос включен группировка работает (кнопка +\- активная), но пользователь не может менять эту группировка. можно предоставить данную возможность?
    2. если в макрос добавить возможность форматирования строк, то это применяется ко всему листу. можно давать возможность только для определенных строк? в меню формат ячейки для нужно строки снял "защиту ячейки", но изменять ее высоту все равно не дает.

  8. Добрый день!
    При сохранении файла в шаблон с поддержкой макросов все работает, но при сохранении в обычный эксель все слетает.
    Подскажите, как быть?

    1. Елена, нечего сказать. Возможно, что-то не так делаете. Обычный Excel - это какой? Не все файлы поддерживают макросы. А может и в самом файле какие-то ошибки, приводящие к проблемам.

      1. Лист 13 (План-факт по дням) где нужна разгруппировка при блокировке листа:
        Sub ProtectShWithOutline()
        Sheets("ПЛАН-ФАКТ ПО ДНЯМ").EnableOutlining = True
        Sheets("ПЛАН-ФАКТ ПО ДНЯМ").Protect Password:="64618483", AllowFiltering:=True, UserInterfaceOnly:=True

        End Sub
        ЭтаКнига-Модуль-:
        Private Sub Workbook_Open()
        Sheets("ПЛАН-ФАКТ ПО ДНЯМ").EnableOutlining = True
        Sheets("ПЛАН-ФАКТ ПО ДНЯМ").Protect Password:="64618483", UserInterfaceOnly:=True
        End Sub
        Сохранила с макросами-открыла-сохранила в Книга ексель.xlsx.
        Подскажите, где ошибка?

          1. Уже поняла. Но прописывала формулы в макросе и сохраняла в xslx, все работает. Буду думать. А может Вы подскажете как зафиксировать значение в ячейке? Например, на одном листе каждый день меняются продажи с нарастающим итогом, на другом листе есть продажи за 1 неделю, надо просчитать продажи из 1 листа во втором за 2,3,4 недели, конкретный объем по каждой неделе. Например, на конец второй недели продано 230 штук, за первую неделю продано 100 шт, т.е. общий объем минус 1 неделя = продажи 2 недели, но нужно таким образом просчитать сколько за 3 неделю и т.д. Вот можно значение второй недели сделать статистическим при расчете 3 недели?

          2. До сохранения файла формата xlsx код еще в нем. После закрытия - он удаляется и при новом открытии файл уже без макросов. А обсуждаемый код как раз и должен запускаться при открытии книги, а он не запускается, т.к. отсутствует.
            А для остальных проблем и вопросов, не связанных со статьей, у нас есть форум. Там и файлы можно прикладывать, что значительно облегчает понимание задачи и проблемы.

  9. Большая беда, что при составлении рассматриваемых здесь модулей на многостраничном файле xlsb критически увеличивается время открытия файла
    (в сравнении с открытием файла со стандартной защитой листов, и разрешением, например:
    1. Выделение заблокированных ячеек
    2. Выделение незаблокированных ячеек
    3. Форматирование ячеек
    4. Форматирование столбцов
    5. Форматирование строк
    6. Использование автофильтра).

    Возможно, есть способ ускорить / побороть эти тормоза?

    1. Нет способов. Время увеличивается исключительно за счет того, что скорее всего используется код защиты всех листов. А ведь это означает, что при открытии книги код сначала снимет с каждого листа защиту, потом установит заново. А это время...Ускорить этот процесс никак не получится.
      А стандартная защита не нуждается в повторной её установке при открытии книги, поэтому и не влияет на время открытия книги.

  10. Уважаемый Дмитрий! На моём сайте Вы, слева, найдете "Тренажер расчета цепей RLC" в EXCE-ле. Там созданы две формы для студентов и лист защищен от их излишнего вмешательства. На листе расположен счетчик времени, который должен фиксировать время решения задачи. Но, к сожалению он останавливается и сбрасывается когда студент вводит расчетные данные. Т.е. цель его при таком режиме равна нулю. Помогите настроить счетчик. К кому бы не обращался - помочь не могут. С уважением и надеждой на помощь.

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.