Как на защищенном листе разрешить группировку

Как на защищенном листе разрешить группировку

У меня следующая ситуация: надо сделать так, чтоб информацию в ячейках нельзя было изменять, но в тоже время можно было пользоваться функцией группировка. Я поставила защиту на лист, теперь форматировать данные нельзя, но и группировка не работает.

Помогите, пожалуйста, разобраться!

Вложения

Группы.rar (6.3 Кб, 502 просмотров)

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

Сделала так как написали, но потом при открытии этого файла защищенного появляется окно:
Compile error:
Sub of Function not defined
Затем закрываю все это и группировка так и не работает.
Что то не так сделала?

Защита листа и ячеек в Excel

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

Как поставить защиту в Excel на лист

В таблице данных защитим ячейки листа от изменений, данных в шапке и строке итоговых значений.

1. Выделите диапазон ячеек B2:B6 и вызовите окно «Формат ячеек» (CTRL+1). Перейдите на вкладку «Защита» и снимите галочку на против опции «Защищаемая ячейка». Нажмите ОК.
2. Выберите инструмент «Рицензирование»-«Защитить лист».
3. В появившемся диалоговом окне «Защита листа» установите галочки так как указано на рисунке. То есть 2 опции оставляем по умолчанию, которые разрешают всем пользователям выделять любые ячейки. А так же разрешаем их форматировать, поставив галочку напротив «форматирование ячеек». При необходимости укажите пароль на снятие защиты с листа.

Теперь проверим. Попробуйте вводить данные в любую ячейку вне диапазона B2:B6. В результате получаем сообщение: «Ячейка защищена от изменений». Но если мы захотим отформатировать любую ячейку на листе (например, изменить цвет фона) – нам это удастся без ограничений. Так же без ограничений мы можем делать любые изменения в диапазоне B2:B6. Как вводить данные, так и форматировать их.

Как видно на рисунке, в окне «Защита листа» содержится большое количество опций, которыми можно гибко настраивать ограничение доступа к данным листа.

Как скрыть формулу в ячейке Excel

Часто бывает так, что самое ценное на листе это формулы, которые могут быть достаточно сложными. Данный пример сохраняет формулы от случайного удаления, изменения или копирования. Но их можно просматривать. Если перейти в ячейку B7, то в строке формул мы увидим: «СУММ(B2:B6)» .

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

1. Запретить выделять ячейки на листе.
2. Включить скрытие содержимого ячейки.

Рассмотрим, как реализовать второй способ:

1. Если лист защищенный снимите защиту выбрав инструмент: «Рецензирование»-«Снять защиту листа».
2. Перейдите на ячейку B7 и снова вызываем окно «Формат ячеек» (CTRL+1). На закладке «Защита» отмечаем опцию «Скрыть формулы».
3. Включите защиту с такими самыми параметрами окна «Защита листа» как в предыдущем примере.

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

Примечание. Закладка «Защита» доступна только при незащищенном листе.

Как скрыть лист в Excel

Допустим нам нужно скрыть закупочные цены и наценку в прайс-листе:

1. Заполните «Лист1» так как показано на рисунке. Здесь у нас будут храниться закупочные цены.
2. Скопируйте закупочный прайс на «Лист2», а в место цен в диапазоне B2:B4 проставьте формулы наценки 25%: =Лист1!B2*1,25.
3. Щелкните правой кнопкой мышки по ярлычке листа «Лист1» и выберите опцию «Скрыть». Рядом же находится опция «Показать». Она будет активна, если книга содержит хотя бы 1 скрытый лист. Используйте ее, чтобы показать все скрытие листы в одном списке. Но существует способ, который позволяет даже скрыть лист в списке с помощью VBA-редактора (Alt+F11).
4. Для блокировки опции «Показать» выберите инструмент «Рецензирование»-«Защитить книгу». В появившемся окне «Защита структуры и окон» поставьте галочку напротив опции «структуру».
5. Выделите диапазон ячеек B2:B4, чтобы в формате ячеек установить параметр «Скрыть формулы» как описано выше. И включите защиту листа.

Внимание! Защита листа является наименее безопасной защитой в Excel. Получить пароль можно практически мгновенно с помощью программ для взлома. Например, таких как: Advanced Office Password Recovery – эта программа позволяет снять защиту листа Excel, макросов и т.п.

Полезный совет! Чтобы посмотреть скрытые листы Excel и узнать их истинное количество в защищенной книге, нужно открыть режим редактирования макросов (Alt+F11). В левом окне «VBAProject» будут отображаться все листы с их именами.

Но и здесь может быть закрыт доступ паролем. Для этого выбираем инструмент: «Tools»-«VBAProjectProperties»-«Protection» и в соответствующих полях вводим пароль. С другой стороны, если установленные пароли значит, книга скрывает часть данных от пользователя. А при большом желании пользователь рано или поздно найдет способ получить доступ этим к данным. Об этом следует помнить, когда Вы хотите показать только часть данных, а часть желаете скрыть! В данном случае следует правильно оценивать уровень секретности информации, которая предоставляется другим лицам. Ответственность за безопасность в первую очередь лежит на Вас.

Примечание. Группировка при защите листа Excel – не работает при любых настройках. Сделать доступную в полноценном режиме функцию группировки на защищенном листе можно только с помощью макросов.

Как на защищенном листе разрешить группировку

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

Нажмите Alt + F11, чтобы активировать редактор Visual Basic.

Дважды щелкните ThisWorkbook в разделе «Объекты Microsoft Excel» в проводнике проекта с левой стороны.

Скопируйте следующий код в появившийся модуль:

Private Sub Workbook_Open ()
С рабочими листами («Сводка Emp»)
.EnableOutlining = Истина
.Защитить UserInterfaceOnly:=True
Конец с
End Sub

Этот код будет выполняться автоматически при каждом открытии книги.

Я получил этот код для работы. Но когда я закрываю и снова открываю, я должен перейти на вкладку разработчика, выбрать кнопку макросов, выбрать «Выполнить» и ввести пароль.

Есть ли способ удалить пароль из кода ИЛИ код автозапуска, который автоматически запустит этот марко и введет пароль?

Кому-то это может понадобиться, я думаю, что понял, как заставить это работать.

Во-первых, ваш код должен быть написан в «ThisWorkbook» в разделе «Объекты Microsoft Excel», как предлагает @peachyclean.
Во-вторых, возьмите код, который написал @Sravanthi, и вставьте в указанное выше место.

Подпрограмма Workbook_Open()
‘Обновление 20140603
Dim xWs как рабочий лист
Установите xWs = Application.ActiveSheet
Dim xPws как строка
xPws = «rfc» »Application.InputBox(«Пароль:», xTitleId, «», Тип:=2)
xWs.Protect Пароль:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = Истина
End Sub

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

Для моего решения я изменил примененный пароль, поэтому вы можете переписать любой пароль ЗДЕСЬ:
xPws = «WRITEANYPASSWORDHERE» »Application.InputBox(«Пароль:», xTitleId, «», Тип:=2)

Кроме того, я не хотел, чтобы защищаемый лист был активен при открытии файла, поэтому я изменил эту часть:
Установите xWs = Application.ActiveSheet ->
Установите xWs = Application.Worksheets(«WRITEANYSHEET’SNAMEHERE»)

Теперь это работает как шарм, лист с именем «WRITEANYSHEET’SNAMEHERE» защищен, но применима группировка. В долгосрочной перспективе, я думаю, проблема будет заключаться в том, что если я захочу изменить этот файл и сохранить решение, мне нужно снять защиту с этого листа, чтобы он работал при следующем открытии. Я думаю, вы можете написать другой макрос для автоматического снятия защиты при закрытии 🙂

Excel как группировать на защищенном листе

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

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

Но что делать, если нужна и защита и возможность структурой пользоваться? Т.е. чтобы пользователь мог просмотреть все в удобной форме, но не смог ничего изменить. Одновременно и просто и не очень.
Если вы не знакомы с макросами и VBA, то обязательно пройдите по ссылкам из инструкции ниже — эти знания потребуются, чтобы сделать все правильно и получить корректный результат. Итак, чтобы разрешить использовать структуру на защищенном листе необходимо:

1. создать в книге стандартный модуль(Alt+F11 —Insert —Module)
2. разместить в нем нижеприведенный код:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Видеоуроки

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

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

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

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

Группировка и разгруппировка строк на защищенном листе с кодом VBA

Группировка и разгруппировка строк на защищенном листе с кодом VBA

Возможно, нет другого хорошего способа решить эту проблему, кроме использования кода VBA, сделайте следующее:

1. Активируйте свой рабочий лист, который вы хотите использовать, убедитесь, что рабочий лист еще не защищен.

2. Затем удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

3. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.

Код VBA: группировка и разгруппировка строк на защищенном листе

4. Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно с напоминанием о вводе пароля для защиты текущего рабочего листа. Смотрите скриншот:

5. Затем нажмите OK, ваш лист был защищен, но вы можете развернуть и сжать символы структуры на этом защищенном листе, см. снимок экрана:

Внимание: Если ваш рабочий лист уже защищен, этот код не будет работать.