Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (57) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

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

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

Если Вы не знакомы с макросами и VBA, то Вам сначала необходимо:

  1. создать стандартный модуль
  2. разместить в нем нижеприведенный код:

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

Примечание: данный макрос лучше всего прописывать на событие открытия книги(модуль ЭтаКнига(ThisWorkbook)), т.к. сразу после закрытия и открытия книги данная защита снимается.

Можно ставить защиту сразу на все листы книги при открытии таким кодом:

Данный код вставляется в модуль ЭтаКнига.

Ну и если Вам только на один лист надо установить, то убираем цикл и вызываем процедуру только для нужного листа:

Чтобы помимо этого можно было разрешить пользователям производить иные стандартные операции(форматирование ячеек, форматирование строк, форматирование столбцов, вставка строк и т.д.) достаточно просто записать макрос по установке защиты листа и посмотреть как будет выглядеть код. Код ниже вставлен в основной после записи макрорекордером установки защиты с возможностью форматировать столбцы:

Чтобы добавить в код установку пароля, записать его следует так:

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

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


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

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

Access Multex Outlook VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 92 коммент.
  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 class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти