Макрос защита листа с параметрами excel
Метод Worksheet.Protect (Excel)
Защищает таблицу, чтобы она не была изменена.
Синтаксис
выражение Переменная, представляюная объект “Таблица”.
Параметры
| Имя | Обязательный или необязательный | Тип данных | Описание |
|---|---|---|---|
| Password | Необязательный | Variant | Строка, которая указывает пароль, чувствительный к делу для таблицы или книги. Если этот аргумент опущен, можно отклонить таблицу или книгу без использования пароля. В противном случае необходимо указать пароль, чтобы отклонить таблицу или книгу. Если вы забудете пароль, вы не сможете отклонить таблицу или книгу. |
Используйте надежные пароли, содержащие строчные и прописные буквы, цифры и знаки. В ненадежных паролях не используются сочетания таких элементов. Надежный пароль: Y6dh!et5. Слабый пароль: House27. Длина паролей должна быть не меньше 8 символов. В парольной фразе лучше использовать 14 или более символов.
Очень важно запомнить пароль. Если вы его забудете, корпорация Майкрософт не сможет его восстановить. Храните пароли, записанные на бумаге, в безопасном месте вдали от информации, которую они защищают.
Примечания
В предыдущих версиях, если применить этот метод с аргументом UserInterfaceOnly, заданным для True, а затем сохранить книгу, при повторном возобновлении работы весь рабочий таблица (а не только интерфейс) будет полностью защищена. Чтобы повторно включить защиту пользовательского интерфейса после открытия книги, необходимо снова применить этот метод с помощью набора UserInterfaceOnly к True.
Если вы хотите внести изменения в защищенный таблицу, можно использовать метод Protect на защищенной таблице, если пароль предоставлен. Кроме того, другим методом было бы отклонить таблицу, внести необходимые изменения, а затем снова защитить таблицу.
Незащищенная означает, что ячейка может быть заблокирована (диалоговое окно Format Cells), но включена в диапазон, определенный в диалоговом окне Разрешить пользователям изменять диапазоны, и пользователь не защитил диапазон с помощью пароля или был проверен с помощью разрешений NT.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Как защитить лист от пользователя, но не от макроса?
Иногда бывает полезно защитить данные на листе от изменений другими пользователями, но при этом так же надо будет работать с данными на листе из VBA(т.е. вносить изменения с помощью кода). Обычная защита листа, конечно, подходит, хоть и есть небольшой недостаток: надо перед каждым обращением к листу снимать с него защиту, выполнять необходимые действия и защищать заново:
Sub Write_in_ProtectSheet() ‘снимаем защиту с листа Worksheets(“Лист1”).Unprotect ‘если лист защищен с паролем 1234: Worksheets(“Лист1”).Unprotect “1234” ‘действия на листе.Например,изменение значения ячейки А1 Cells(“A1”).Value = “www.excel-vba.ru” ‘устанавливаем защиту на лист Worksheets(“Лист1”).Protect ‘если лист был защищен с паролем 1234: Worksheets(“Лист1”).Protect “1234” End Sub
Но есть метод проще.
Если выполнить ниже приведенную строчку кода, то пользователю невозможно будет изменить данные на листе(кроме тех, которые Вы сами разрешите), однако код VBA(макрос) сможет преспокойно вносить любые изменения, не снимая защиту.
Sub Protect_for_User_Non_for_VBA() ActiveSheet.Protect Password:=”1111″, UserInterfaceOnly:=True End Sub
Sub Protect_for_User_Non_for_VBA() Sheets(2).Protect Password:=”1111″, UserInterfaceOnly:=True Sheets(“Лист1″).Protect Password:=”1111”, UserInterfaceOnly:=True End Sub
Так же приведенный код можно еще чуть модернизировать и разрешить пользователю помимо изменения ячеек еще и использовать автофильтр:
Sub Protect_for_User_Non_for_VBA() Sheets(2).Protect Password:=”1111″, UserInterfaceOnly:=True ‘на лист “Лист1” поставим защиту и разрешим пользоваться фильтром Sheets(“Лист1″).Protect Password:=”1111”, AllowFiltering:=True, UserInterfaceOnly:=True End Sub
Можно разрешить и другие действия(выделение незащищенных ячеек, выделение защищенных ячеек, форматирование ячеек, вставку строк, вставку столбцов и т.д. Чуть подробнее про доступные параметры можно узнать в статье Защита листов и ячеек в MS Excel). А как будет выглядеть строка кода с разрешенными параметрами можно узнать, записав макрорекордером установку защиты листа с нужными параметрами: 
После этого получится строка вроде такой:
здесь я разрешил использовать автофильтр( AllowFiltering:=True ), вставлять строки( AllowInsertingRows:=True ) и столбцы( AllowInsertingColumns:=True ).Чтобы добавить возможность изменять данные ячеек только через код VBA, останется добавить параметр UserInterfaceOnly:=True:
и так же неплохо бы добавить и пароль для снятия защиты, т.к. запись макрорекордером не записывает пароль:
Private Sub Workbook_Open() Sheets(“Лист1″).Protect Password:=”1111”, UserInterfaceOnly:=True End Sub
Этот код сработает только после того, как книга будет открыта. А это значит, чтобы увидеть результат необходимо после записи этого кода в ЭтаКнига сохранить книгу, закрыть её и открыть заново. Тогда в сам момент открытия книги код сработает и установит на “Лист1” правильную защиту.
Часто так же бывает необходимо устанавливать одинаковую защиту на все листы книги. Сделать это можно таким кодом, который так же должен быть размещен в модуле ЭтаКнига(ThisWorkbook):
Private Sub Workbook_Open() Dim wsSh As Object For Each wsSh In Me.Sheets Protect_for_User_Non_for_VBA wsSh Next wsSh End Sub Sub Protect_for_User_Non_for_VBA(wsSh As Worksheet) wsSh.Protect Password:=”1111″, UserInterfaceOnly:=True End Sub
Плюс во избежание ошибок лучше перед установкой защиты снимать ранее установленную(если она была):
Sub Protect_for_User_Non_for_VBA(wsSh As Worksheet) wsSh.Unrotect “1111” wsSh.Protect Password:=”1111″, UserInterfaceOnly:=True End Sub
Ну и если надо такую защиту установить только на конкретные листы, то убираем цикл и вызываем процедуру только для нужных листов. Если известны их имена, то можно прибегнуть к использованию массивов:
Private Sub Workbook_Open() Dim arr, sSh arr = Array(“Отчет”, “База”, “Бланк”) For Each sSh in arr Protect_for_User_Non_for_VBA Me.Sheets(sSh) Next End Sub Sub Protect_for_User_Non_for_VBA(wsSh As Worksheet) wsSh.Protect Password:=”1111″, AllowFiltering:=True, UserInterfaceOnly:=True End Sub
Для применения в своих задачах в данном коде необходимо лишь изменить(добавить, удалить, вписать другие имена) имена листов в этой строке: Array(“Отчет”, “База”, “Бланк”)
Статья помогла? Поделись ссылкой с друзьями!
Защита проекта VBA в MS Excel
Всем привет! Я обычный пользователь MS Excel. Не являющийся профессиональным программистом, но накопивший достаточно опыта, для установки и обхода защиты проектов VBA.
Дисклеймер:
В данной статье рассмотрены виды защиты проектов VBA, от несанкционированного доступа. Их сильные и слабые стороны – ранжирование.
Цель статьи показать слабые и сильные стороны каждого вида защиты проекта VBA в MS Office.
Демонстрация разработанных инструментов, в надстройке Macro Tools VBA, для снятия и установки той или иной защиты.
Все инструменты реализованы стандартными средствами VBA, без использования дополнительных библиотек.
Первый вид защиты — Обычный пароль
Время на снятие: мгновенно
Недостаток: быстрый доступ к запароленному модулю VBA
Самая легко снимающаяся защита. В интернете легко находится код, для снятия данной защиты.
Данную защиту можно снять следующим инструментом:
Второй вид защиты — Project is Unviewable
Время на снятие: от 10 до 15 мин (в ручную)
Недостаток: доступ к исходному коду модуля VBA
Один из самых распространённых видов защит. Встречается в 95% файлах с защитой модуля VBA. При попытке открыть проект, открывается диалоговое окно, с сообщением: Project is Unviewable.
Большинство пользователей Excel, не могут снять данную защиту, так как она имеет множество вариации и нюансов, для ее снятие нужно иметь представление о внутренней структуре файла Excel.
Основан, данный вид защиты, на изменение ключей:
Кратко, как создается данная защита
Для создания данной защиты нужно, разархивировать файл Excel. Перейти в архиве в папку xl, открыть файл vbaProject.bin, в конце файла находятся наши ключи, редактируем значения ключей на пусто, сохраняем файл. Переводим наш архив, обратно в файл Excel. Готово!
Это самый простой вариант данной защиты, но существует множество модификаций.
Алгоритм снятия защиты Project is Unviewable.
1) Разархивируем подопытный файл, переходим в файл …\xl\_rels\workbook.xml.rels
2) В файле workbook.xml.rels ищем строку, содержащую слово vbaProject, обычно имеет следующий вид: />. В этой строке нас интересует ключ Target,иего значение. Значение является название файла, в котором находится проект VBA. Иногда, защищающий меняет значения ключа на printerSettings.bin.Получается маскировка файла с проектом VBA под другой файл.
3) Открываем на редактирование файл, указанный в ключе Target, ищем в файле ключи CMG, DPB, GC. И меняем в их названиях любую букву на любую другую, например: CMC, DPC, CC. При поиске нужно быть аккуратным, так как защищающий может поместить в проект форму, подписью повторяющую один из ключей, например такую: DPB=«0B09CE0F8E108E108E». При ее изменении проект VBA, будет удален из книги Excel. Сохраняем и закрываем файл.
4) Переводим архив обратно в файл Excel.
6) Открываем файл. Если все правильно сделано то, Excel, будет ругаться на не правильные ключи, которые мы отредактировали, в пункте 3. Жмем, да, пока данные сообщения не закончатся и диалоговое окно закроется.
Если данное сообщение не появляется то, вы отредактировали не файл который содержит проект VBA.
7) Открываем проект VBA. После всего, проект VBA должен быть доступен.
8) Но иногда защита не снимается, тогда нужно сохранить файл, проверить, что он действительно сохранился! И проделать повторно операции с 1 по 7. Обычно так происходит когда в файле workbook.xml.rels в ключе Target установлено printerSettings.bin.При сохранение, Excel исправляет это на значение на vbaProject.bin
Данную защиту можно установить и снять следующим инструментом:
Третий вид защиты — Hidden Module, скрытые модули VBA
Время на снятие: от 15 до 20 мин (нужен редактор OLE — объектов, Structured Storage Viewer, например.
Недостаток: доступ к коду модуля VBA
Менее распространенный вид защиты обычно встречается в комбинации с защитой Project is Unviewable. При установке данной защиты модуль VBA не отображается в проекте книги Excel. О его существовании можно узнать, проанализировав код VBA (что требует время!) или открыть файл Excel в программе OpenOffice или LibreOffice (так же можно смотреть код при защите Project is Unviewable, но данный способ не дает возможность получить рабочий файл, без пароля).
Просмотр кода VBA в LibreOffice
Кратко, как создается данная защита
Для создания данной защиты нужно отредактировать файл с проектом VBA — vbaProject.bin или printerSettings.bin,в зависимости от настроек в файле …\xl\_rels\workbook.xml.rels. В конце файла удаляются строки вида: Module1=32, 32, 635, 330, Z. С нужными названиями модулей.
Для снятия данной защиты нужно в файле vbaProject.bin — восстановить удаленные записи модулей.
Данную защиту можно установить следующим инструментом.
Четвертый вид защиты — Обфускация кода
Время на снятие: неизвестно, зависит от объема кода и пере использования частей кода
Обфусцированный код VBA
Недостаток: необходимость тестирование файла после обфускации, на работоспособность
Крайне редкий вид защиты, основанный на изменении исходного кода VBA, в не удобочитаемый вид для человека. Удаляются все комментарии, форматирование кода, переименовываются названия всех переменных, процедур, функций, модулей и прочего. Злоумышленнику никогда не удастся восстановить первоначальный вид кода, и потребует достаточно много времени для, его восстановления в удобно читаемый вид для человека.
Для де-обфускации кода нужно иметь время, специализированное ПО.
Данную защиту можно установить следующим инструментом.
Пятый вид защиты — Перенос кода в dll
Время на снятие: неизвестно, зависит от языка программирования и квалификации
Недостаток: необходимости в дополнительном файле dll
Один из самых редких видов защиты. Основная идея перенос основного кода в отдельную библиотеку dll, написанную на любом другом языке программирования. Не распространённость данный вид защиты получил по следующей причине, необходимости за файлом Excel, «таскать» дополнительный файл, dll.
Для получения доступа к коду dll, нужно обладать специальными знаниями.
Защита данных в Microsoft Excel
Уровень 0. Защита от ввода некорректных данных в ячейку
Уровень 1. Защита ячеек листа от изменений
Мы можем полностью или выборочно запретить пользователю менять содержимое ячеек любого заданного листа. Для установки подобной защиты следуйте простому алгоритму:
Т.е., если мы хотим оставить пользователю возможность, например, форматировать защищенные и незащищенные ячейки, необходимо установить первых три флажка. Также можно разрешить пользователям использовать сортировку, автофильтр и другие удобные средства работы с таблицами.
Уровень 2. Выборочная защита диапазонов для разных пользователей
Если предполагается, что с файлом будут работать несколько пользователей, причем каждый из них должен иметь доступ в свою область листа, то можно установить защиту листа с разными паролями на разные диапазоны ячеек.
В появившемся окне необходимо нажать кнопку Создать (New) и ввести имя диапазона, адреса ячеек, входящих в этот диапазон и пароль для доступа к этому диапазону:
Повторите эти действия для каждого из диапазонов разных пользователей, пока все они не окажутся в списке. Теперь можно нажать кнопку Защитить лист (см. предыдущий пункт) и включить защиту всего листа.
Теперь при попытке доступа к любому из защищенных диапазонов из списка, Excel будет требовать пароль именно для этого диапазона, т.е. каждый пользователь будет работать “в своем огороде”.
Уровень 3. Защита листов книги
Если необходимо защититься от:
Уровень 4. Шифрование файла
Изменение параметров безопасности макросов в Excel
В Excel вы можете изменять параметры безопасности макросов, определяя, какие макросы и при каких обстоятельствах должны выполняться при открытии книги. Например, можно разрешить выполнение макроса в зависимости от наличия цифровой подписи надежного разработчика.
Дополнительные сведения о параметрах безопасности макросов в документах Microsoft Office см. в статье Включение и отключение макросов в файлах Office.
Ниже приведен список различных параметров безопасности макросов. Если установлена антивирусная программа, работающая с Microsoft Office, то помимо всех параметров книга, содержащая макросы, перед открытием проверяется также на наличие известных вирусов.
Отключить все макросы без уведомления. Выберите этот параметр, если не доверяете макросам. Все макросы в документе и предупреждения о безопасности макросов отключаются. Если у вас есть документы, содержащие неподписанные макросы, которым вы доверяете, эти документы можно поместить в надежное расположение. Документы из надежных расположений разрешается выполнять без проверки системой центра управления безопасностью.
Отключить все макросы с уведомлением. Этот параметр задан по умолчанию. Выберите этот вариант, если хотите отключить макросы, но при этом получать предупреждения о безопасности, если макросы есть. Так вы можете в каждом конкретном случае решать, стоит ли включать эти макросы.
Отключить все макросы кроме макросов с цифровой подписью. Этот параметр подобен параметру Отключить все макросы с уведомлением, не считая того, что макрос с цифровой подписью надежного издателя может выполняться, если вы уже доверяете этому издателю. Если издатель не считается надежным, вы получаете уведомление. Так вы можете выбрать, следует ли включить эти подписанные макросы и выразить доверие издателю. Все макросы без подписи отключаются без уведомления.
Включить все макросы (не рекомендуется, возможен запуск опасной программы). Выберите этот параметр, чтобы разрешить выполнение всех макросов. В таком случае ваш компьютер становится уязвимым для потенциально опасного кода, поэтому этот вариант не рекомендуется.
Доверять доступ к объектной модели проектов VBA Этот параметр используется для разработчиков и используется для преднамеренной блокировки или блокировки программного доступа к объектной модели VBA из любого клиента автоматизации. Другими словами, он обеспечивает параметр безопасности для кода, который создается для автоматизации программы Office и программного управления средой и объектной моделью Microsoft Visual Basic для приложений (VBA). Этот параметр настраивается для каждого пользователя и каждого приложения и по умолчанию не имеет доступа. Этот параметр безопасности затрудняет несанкционированный доступ к созданию кода “саморекопирования”, который может навредить системам конечных пользователей. Чтобы любой клиент автоматизации мог получить доступ к объектной модели VBA программным путем, пользователь, работающий с кодом, должен явно предоставить доступ. Чтобы включить доступ, выберите этот квадрат.
В Office используется технология Microsoft Authenticode, позволяющая создателям макросов добавлять цифровую подпись в файл или проект макроса. Сертификат, используемый для создания подписи, подтверждает, что макрос или документ создан автором подписи, а подпись подтверждает, что макрос или документ не был изменен.
Установив свой цифровой сертификат, вы можете подписывать файлы и макросы.
Получение цифрового сертификата для подписания
Цифровой сертификат можно получить в коммерческом центре сертификации (ЦС) или у администратора безопасности локальной сети либо ИТ-специалиста.
Дополнительные сведения о центрах сертификации, обслуживающих продукты Майкрософт, см. в списке участников программы корневых сертификатов Майкрософт.
Создание своего самозаверяющего цифрового сертификата
С помощью программы Selfcert.exe вы можете создать свой самозаверяющий сертификат.
Примечание: Поскольку созданный вами цифровой сертификат не выдается официальным доверенным центром сертификации, макросы, подписанные с помощью такого сертификата, называют макросами с собственной подписью. В Microsoft Office самозаверяющие сертификаты считаются надежными только на том компьютере, на котором они добавлены в хранилище личных сертификатов.
Дополнительные сведения о добавлении цифровой подписи к макросам см. в статье Подписание макроса.
Изменение параметров безопасности макросов
Вы можете изменять параметры безопасности макросов в центре управления безопасностью, если только системный администратор в вашей организации не запретил это, изменив настройки по умолчанию.
На вкладке Разработчик в группе Код нажмите кнопку Безопасность макросов.
Чтобы включить вкладку “Разработчик”, см. раздел Отображение вкладки “Разработчик”.
В категории Параметры макросов в разделе Параметры макросов выберите нужный вариант.
Примечание: Все изменения, внесенные в категории Параметры макросов в Excel, применяются только к Excel и не влияют на другие приложения Microsoft Office.
Доступ к центру управления безопасностью также можно получить из окна “Параметры Excel”. Для этого нажмите кнопку Параметры (Excel 2010–2016) или кнопку Microsoft Office 
(Excel 2007), а затем выберите центр управления > Центр управления Параметры > Макрос Параметры.
Дополнительные сведения о безопасности макросов см. в следующих статьях:
Устранение неполадок
Не удается изменить параметры безопасности макросов
Некоторые пользователи не могут изменять параметры центра управления безопасностью из-за групповых политик безопасности в их организациях. В таких случаях необходимо обратиться к ИТ-администратору организации.
Что случилось с параметрами безопасности “Очень высокий”, “Высокий”, “Средний” и “Низкий”?
Параметр Excel 2003
Эквивалент в Excel 2007/2010/2013/2016
Отключить все макросы без уведомления
В Excel 2003 макросы VBA выполняются только при условии, что выбран параметр “Доверять всем установленным надстройкам и шаблонам” (в Excel 2003 на вкладке “Надежные издатели” в диалоговом окне “Безопасность”) и макросы (с подписью или без) хранятся в определенной надежной папке на жестком диске пользователя.
Если эти условия не соблюдены, то при использовании параметра “Очень высокий” макросы VBA не выполняются в Excel 2003.
Отключить все макросы кроме макросов с цифровой подписью
В Excel 2003 могут выполняться только исполняемые файлы (EXE или COM), подписанные утвержденным надежным источником (то есть имеющие сертификат). В противном случае все исполняемые модули, связанные с документами или внедренные в них, автоматически отключаются без предупреждения, когда пользователь открывает документы.
При установке всех приложений Office 2003 по умолчанию задается параметр “Высокий”.
Отключить все макросы с уведомлением
В Excel 2003 пользователям при открытии документа предлагается включить или отключить исполняемые модули. На этом уровне необходимо принять сертификат для каждого исполняемого модуля, добавив сертификат в соответствующий сегмент реестра на компьютере под управлением Windows.
Последующие запросы на выполнение макроса от надежного источника принимаются автоматически (исполняемые модули выполняются без предварительного запроса пользователя).
Включить все макросы (не рекомендуется, возможен запуск опасной программы)
В Excel 2003 все макросы выполняются без ограничений. Этот уровень безопасности не защищает от вредоносных программ, не позволяет принимать сертификаты и не считается безопасным в целом. Этот уровень использовать не рекомендуется.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.