Макрос для защиты ячеек в excel

Обновлено: 04.07.2024

выражения. Защита (Пароль , DrawingObjects , Содержимое , Сценарии , UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows , AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

выражение Переменная, представляюная объект "Таблица".

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
Password	Необязательный	Variant	Строка, которая указывает пароль, чувствительный к делу для таблицы или книги. Если этот аргумент опущен, можно отклонить таблицу или книгу без использования пароля. В противном случае необходимо указать пароль, чтобы отклонить таблицу или книгу. Если вы забудете пароль, вы не сможете отклонить таблицу или книгу.

Используйте надежные пароли, содержащие строчные и прописные буквы, цифры и знаки. В ненадежных паролях не используются сочетания таких элементов. Надежный пароль: Y6dh!et5. Слабый пароль: House27. Длина паролей должна быть не меньше 8 символов. В парольной фразе лучше использовать 14 или более символов.

Примечания

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

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

Незащищенная означает, что ячейка может быть заблокирована (диалоговое окно Format Cells), но включена в диапазон, определенный в диалоговом окне Разрешить пользователям изменять диапазоны, и пользователь не защитил диапазон с помощью пароля или был проверен с помощью разрешений NT.

Поддержка и обратная связь

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

Microsoft Excel предоставляет пользователю несколько, условно выражаясь, уровней защиты - от простой защиты отдельных ячеек до шифрования всего файла шифрами крипто-алгоритмов семейства RC4. Разберем их последовательно.

Уровень 0. Защита от ввода некорректных данных в ячейку

Самый простой способ. Позволяет проверять что именно пользователь вводит в определенные ячейки и не разрешает вводить недопустимые данные (например, отрицательную цену или дробное количество человек или дату октябрьской революции вместо даты заключения договора и т.п.) Чтобы задать такую проверку ввода, необходимо выделить ячейки и выбрать на вкладке Данные (Data) кнопку Проверка данных (Data Validation) . В Excel 2003 и старше это можно было сделать с помощью меню Данные - Проверка (Data - Validation) . На вкладке Параметры из выпадающего списка можно выбрать тип разрешенных к вводу данных:

Уровень 1. Защита ячеек листа от изменений

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

Выделите ячейки, которые не надо защищать (если таковые есть), щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек(Format Cells) . На вкладке Защита(Protection) снимите флажок Защищаемая ячейка(Locked) . Все ячейки, для которых этот флажок останется установленным, будут защищены при включении защиты листа. Все ячейки, где вы этот флаг снимете, будут доступны для редактирования несмотря на защиту. Чтобы наглядно видеть, какие ячейки будут защищены, а какие - нет, можно воспользоваться этим макросом.
Для включения защиты текущего листа в Excel 2003 и старше - выберите в меню Сервис - Защита - Защитить лист(Tools - Protection - Protect worksheet) , а в Excel 2007 и новее - нажмите кнопку Защитить лист (Protect Sheet) на вкладке Рецензирование (Reveiw) . В открывшемся диалоговом окне можно задать пароль (он будет нужен, чтобы кто попало не мог снять защиту) и при помощи списка флажков настроить, при желании, исключения:

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

Уровень 2. Выборочная защита диапазонов для разных пользователей

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

Чтобы сделать это выберите на вкладке Рецензирование (Review) кнопку Разрешить изменение диапазонов (Allow users edit ranges) . В версии Excel 2003 и старше для этого есть команда в меню Сервис - Защита - Разрешить изменение диапазонов (Tools - Protection - Allow users to change ranges) :

В появившемся окне необходимо нажать кнопку Создать (New) и ввести имя диапазона, адреса ячеек, входящих в этот диапазон и пароль для доступа к этому диапазону:

Повторите эти действия для каждого из диапазонов разных пользователей, пока все они не окажутся в списке. Теперь можно нажать кнопку Защитить лист (см. предыдущий пункт) и включить защиту всего листа.

Теперь при попытке доступа к любому из защищенных диапазонов из списка, Excel будет требовать пароль именно для этого диапазона, т.е. каждый пользователь будет работать "в своем огороде".

Уровень 3. Защита листов книги

Если необходимо защититься от:

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

то вам необходима защита всех листов книги, с помощью кнопки Защитить книгу (Protect Workbook) на вкладке Рецензирование (Reveiw) или - в старых версиях Excel - через меню Сервис - Защита - Защитить книгу (Tools - Protection - Protect workbook) :

Уровень 4. Шифрование файла

При необходимости, Excel предоставляет возможность зашифровать весь файл книги, используя несколько различных алгоритмов шифрования семейства RC4. Такую защиту проще всего задать при сохранении книги, т.е. выбрать команды Файл - Сохранить как (File - Save As) , а затем в окне сохранения найти и развернуть выпадающий список Сервис - Общие параметры (Tools - General Options) . В появившемся окне мы можем ввести два различных пароля - на открытие файла (только чтение) и на изменение:

Здравствуйте! Позавчера мне светлые и умные головы этого форума очень хорошо помогли с решением проблемы в двух вопросах.
Следуя их инструкциям и указаниям, мною была доведена таблица до удовлетворяющего состояния. Теперь возник еще один (самый главный вопрос) - Можно ли защитить ячейки, и скрыть в них формулы, чтобы пользователи (а их кроме меня будет еще 2 человека), не внесли изменения в структуру таблиц, но при этом сохранить работоспособность макросов на добавление показателей из справочников.
Пользователю доступны для редактирования только ячейки со светло-синим фоном и выбор из справочников ( светло-зеленый фон ), все белые ячейки - недоступны. Если делать защиту листа с вводом пароля, тогда нарушается работоспособность макроса на добавление строк в таблицу. Удалось добиться скрытия формул и защиты ячеек и при этом работоспособности макроса только на вкладке "Производственные затраты и ФР" путем помещения следующего кода перед началом макроса:
[vba]

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"

[/vba]
, а на вкладке "Реализация и себестоимость" и "Сводный отчет" - нет.
Если кто-то сталкивался с этим вопросом, или есть готовое решение, помогите, пожалуйста. Спасибо.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"

[/vba]
, а на вкладке "Реализация и себестоимость" и "Сводный отчет" - нет.
Если кто-то сталкивался с этим вопросом, или есть готовое решение, помогите, пожалуйста. Спасибо. Account196

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"

[/vba]
, а на вкладке "Реализация и себестоимость" и "Сводный отчет" - нет.
Если кто-то сталкивался с этим вопросом, или есть готовое решение, помогите, пожалуйста. Спасибо. Автор - Account196
Дата добавления - 14.09.2018 в 11:16

Если вы работаете(или работали) в более-менее крупной компании, то 100% сталкивались с ситуацией, когда необходимо переслать свой файл другому сотруднику и при этом запретить ему изменять какие-то данные (чаще всего это ячейки с формулами, которые пользователь ни в коем случае не должен менять, а в идеале даже видеть. Видеть он должен только результат вычислений этих формул). В тоже время надо оставить возможность при необходимости изменить любые ячейки или данные, но исключительно после ввода пароля. Все это можно сделать при помощи стандартной защиты листов в Excel.

Установка защиты листов
Чтобы защитить лист необходимо перейти на вкладку Рецензирование (Review) -группа Изменения (Changes) -Защитить лист (Protect sheet) .
в Excel 2003 - Сервис-Защита-Защитить лист.
для версий Excel 2010 и выше так же можно щелкнуть правой кнопкой мыши на ярлыке нужного листа и выбрать Защитить лист (Protect sheet)
После нажатия появится окно:

В первом поле Пароль для отключения защиты листа необходимо указать пароль, который будет использоваться для снятия защиты с листа в случае необходимости. Если поле оставить пустым, то для снятия защиты с листа не потребуется вводить пароль и защиту сможет снять любой, кто знает как снимать защиту. Защита без пароля как правило применяется в случаях, когда файлом пользуются исключительно компетентные пользователи, которые отдают себе отчет в том, какие ячейки надо изменять, а какие не трогать. Защита в этом случае предназначена лишь от случайных неверных действий.
Второе поле несколько хитрее и имеет множество пунктов, которые можно отметить галочками. Пункты перечисляют действия, которые будут разрешены пользователю после установки защиты на лист. Это означает, что если установить галочку на пункт "вставку столбцов", то даже после установленной защиты на листе будет доступна возможность добавления новых столбцов без снятия защиты с листа. По умолчанию при первой установке защиты галочки установлены только на первых двух пунктах(выделение заблокированных ячеек и выделение незаблокированных ячеек). Большинство пунктов достаточно красноречивы и не нуждаются в подробном пояснении, но на некоторых стоит остановиться чуть подробнее. После установки защиты пользователю так же будет разрешено(если напротив пункта установлен флажок):

выделение заблокированных ячеек (Select locked cells) - разрешено выделять ячейки, для которых установлен атрибут Защищаемая ячейка (правая кнопка мыши на ячейке/диапазоне -Формат ячеек (Format cells) -вкладка Защита (Protection) -Защищаемая ячейка (Locked) ). Если отметить этот пункт, то пункт выделение незаблокированных ячеек будет отмечен автоматически, т.к. если разрешено выделение заблокированных ячеек, то конечно, должно быть разрешено выделять и незаблокированные.
выделение незаблокированных ячеек (Select unlocked cells) - будет разрешено выделять только те ячейки, для которых атрибут Защищаемая ячейка не установлен. Применяется вместе с отключением пункта выделение заблокированных ячеек, чтобы запретить пользователю после установки защиты даже выделять запрещенные к изменению ячейки. Таким образом пользователь будет вынужден перемещаться только по тем ячейкам, которые ему можно изменять. Подробнее про применение свойства "Защищаемая ячейка" можно ознакомиться в этой статье: Как разрешить изменять только выбранные ячейки?
форматирование ячеек (Format cells) - будет разрешено изменять форматы ячеек: цвет заливки, цвет шрифта, размер шрифта, имя шрифта, границы, отступы и т.п.
форматирование столбцов (Format columns) - несмотря на вроде понятное название при установке разрешает изменять ширину столбцов. При этом, если пункт форматирование ячеек не установлен, то изменять цвет шрифта, заливки и т.п. будет запрещено
форматирование строк (Format rows) - так же как и в случае с пунктом форматирование столбцов при установке разрешает изменять высоту строк, но при этом невозможно изменять цвет шрифта, заливки и т.п., если пункт форматирование ячеек не установлен
вставку столбцов (Insert columns) - разрешает вставку целых столбцов (вставлять отдельные ячейки при этом запрещено)
вставку строк (Insert rows) - разрешает вставку целых строк (вставлять отдельные ячейки при этом запрещено)
вставку гиперссылок (Insert hyperlinks) - разрешает создание гиперссылок на листе (Что такое гиперссылка?). Правда, при этом создать гиперссылки можно будет исключительно в незаблокированных ячейках.
удаление столбцов (Delete columns) - разрешает удаление целых столбцов. При этом удаление столбцов допускается только в том случае, если столбец не содержит заблокированных ячеек. Если хоть одна ячейка в столбце с атрибутом "Защищаемая ячейка", то удаление столбца невозможно. Так же невозможно удалять отдельные ячейки внутри столбцов, даже если все ячейки не заблокированные
удаление строк (Delete rows) - разрешает удаление целых строк. При этом удаление строк допускается только в том случае, если строка не содержит заблокированных ячеек. Если в строке есть хоть одна ячейка с атрибутом "Защищаемая ячейка", то удаление строки невозможно. Так же невозможно удалять отдельные ячейки внутри строк, даже если все ячейки в строке не заблокированные
сортировку (Sort) - один из "хитрых" пунктов. Хоть сам пункт сортировки активен и доступен для вызова, сама сортировка при этом разрешена только в том случае, если все ячейки внутри сортируемого диапазона не заблокированные. Если внутри диапазона будет хоть одна заблокированная ячейка (с атрибутом "Защищаемая ячейка"), то сортировка будет невозможна
использование автофильтра (Use Autofilter) - тоже "хитрый" пункт. Как следует из описания допускается только использование автофильтра. Это означает, что если автофильтр уже установлен на листе, то после защиты его можно будет использовать для отбора данных. Однако если фильтр не был установлен до установки защиты на лист - то установить фильтр будет уже невозможно без снятия защиты
использование отчетов сводной таблицы (Use PivotTable reports) - при установке будет возможно использовать сводную таблицу для анализа данных: перемещать поля внутри сводной таблицы, отбирать и фильтровать данные. Однако невозможно при этом будет изменить источник данных, обновлять сводную, изменять функции полей, добавлять вычисляемые поля, убирать и добавлять промежуточные итоги, менять макет отчета, стили и т.п.
изменение объектов (Edit objects) - будет возможно добавлять, выделять и даже удалять объекты на листе, а так же изменять их размеры и большинство свойств (цвета границ, заливки, эффекты свечения и стилей и пр.). К объектам в данном случае относятся Фигуры (Shapes) , Рисунки (Pictures) , объекты SmartArt, Диаграммы (Charts)
изменение сценариев (Edit scenarios) - если до установки защиты были созданы сценарии (Данные (Data) -Анализ Что-если (What-If Analysis) -Диспетчер сценариев (Scenario manager) ), то после установки защиты их можно будет изменять.

После установки нужных параметров и нажатия ОК:

Если после установки защиты пользователь должен иметь возможность выделять все ячейки на листе, но так же необходимо запретить ему доступ к просмотру формул , то перед установкой защиты в нужных ячейках необходимо проделать следующее: выделяем все необходимые ячейки -правая кнопка мыши -Формат ячеек (Format cells) -вкладка Защита (Protection) . Устанавливаем флажок на пункте Скрыть формулы (Hidden) (чаще всего используется вместе с установкой галочки на Защищаемая ячейка (Locked) ). После этого устанавливаем защиту.

Так же можно защищать только отдельные ячейки на листе : Как разрешить изменять только выбранные ячейки? Для чего это нужно? Как правило применяется в случаях, если в файле применяются формулы и расчеты на основании введенных пользователем данных. При этом пользователь не должен иметь доступ к ячейкам с формулами, но должен иметь возможность изменять данные определенных ячеек, от которых зависят сами формулы и их результат.

Если в файле присутствует группировка или структура (Данные (Data) -Группировать (Group) ), то её использование будет невозможно на защищенном листе. Она будет доступна только в том виде, в котором была до установки защиты. Хотя здесь тоже есть лазейка, но уже только с применением Visual Basic for Applications(VBA - встроенный в MS Office язык программирования): Как оставить возможность работать с группировкой/структурой на защищенном листе?

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

Снятие защиты с листа
Чтобы снять защиту с листа необходимо перейти на вкладку Рецензирование (Review) -группа Изменения (Changes) -Снять защиту листа (Unprotect sheet) . Если лист был защищен без пароля, то защита будет снята сразу. Если лист был защищен с указанием пароля, то появится окно с запросом пароля

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

Насколько стойкая защита листов в Excel
К сожалению или счастью защита листов в Excel совершенно не стойкая ко взлому. Защита с листа, если пароль не известен, снимается на раз-два даже при помощи VBA. В надстройке MulTEx есть специальная команда, которая поможет снять защиту с листа, если пароль был забыт или утерян: Снять защиту с листа(без пароля).
Но стоит учитывать тот факт, что защита листов изначально не планировалась как средство защиты своих расчетных алгоритмов и интеллектуальной собственности. Защита листов(как и книг) задумывалась как защита "от дурака" - т.е. дабы случайно или по неумению данные не были испорчены или удалены.
Плюс Microsoft все же совершенствует Excel и с выходом новых версий происходят изменения и в области защиты, что не может не радовать. Например, защита листов и книг начиная с версии Excel 2013 уже более стойкая(для тех кто в теме: алгоритм SHA-512 в 2013 и выше против SHA1 в ранних версиях). Это значит, что простым брутфорсом поломать такую защиту хоть и можно, но времени на это уйдет уже гораздо больше. Хотя для снятия защиты с листов в открытых форматах(.xlsx,.xlsm и им подобных) возможно и другими методами.

Читайте также: