Хитрости »
Основные понятия (22)
Сводные таблицы и анализ данных (7)
Графики и диаграммы (4)
Работа с VB проектом (10)
Power BI и Power Query (8)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (60)
Разное (36)

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

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

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

  1. создать в книге стандартный модуль
  2. разместить в нем нижеприведенный код:
  3. Выполнить данный код(Alt+F8 -Protect_And_Structure)

Код сам устанавливает защиту на лист(т.е. не надо перед его выполнение устанавливать защиту вручную), но при этом разрешает использовать группировку.
Основную роль здесь играет параметр UserInterfaceOnly, который говорит Excel-ю, что коды VBA могут выполнять определенные действия, не снимая защиты методом Unprotect. А второй важный пункт - EnableOutlining = True. Он как раз и включает возможность использования группировки. Как ни странно, но без UserInterfaceOnly он не работает. Поэтому важно применять их оба.
Код выше устанавливает такую защиту только на активный лист книги. Но можно указать лист явно(например установить защиту на лист с именем Лист1 в активной книге):

Так же приведенный код можно еще чуть модернизировать и разрешить пользователю помимо изменения ячеек еще и использовать автофильтр:

Можно разрешить и иные действия(выделение незащищенных ячеек, выделение защищенных ячеек, форматирование ячеек, вставку строк, вставку столбцов и т.д. Чуть подробнее про доступные параметры можно узнать в статье Защита листов и ячеек в MS Excel). А как будет выглядеть строка кода с разрешенными параметрами можно узнать, записав макрорекордером установку защиты листа с нужными параметрами:

После этого получится строка вроде такой:

здесь я разрешил использовать автофильтр( AllowFiltering:=True), вставлять строки( AllowInsertingRows:=True) и столбцы( AllowInsertingColumns:=True).Чтобы добавить возможность изменять данные ячеек только через код VBA, останется добавить параметр UserInterfaceOnly:=True и установить EnableOutlining = True:

и так же неплохо бы добавить и пароль для снятия защиты, т.к. запись макрорекордером не записывает пароль:

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

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

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

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

Для применения этого кода в своих книгах необходимо будет лишь изменить(добавить, удалить, вписать другие имена) имена листов в этой строке: Array("Январь", "Февраль", "Март")

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

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


Статья помогла? Не держи в себе, поделись ссылкой с друзьями!

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

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Печать Поиск данных Поиск решения Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных вебинар ссылки статьи тренинг
Обсуждение: 98 комментариев
  1. Сероб:

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

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

      Рейтинг: 0
  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

    Рейтинг: 0
    • Рейтинг: 0
      • Анна:

        Дмитрий, вы знали что вы гений?)))
        спасибо огромное!!!

        Рейтинг: 0
      • Анна:

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

        Рейтинг: 0
Поделитесь своим мнением

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


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

Тренинги

Заказать
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2017 Excel для всех  Войти
Авторизация
*
*
Регистрация
*
*
*
Пароль не введен
*
captcha
Перейти на страницу