Vba excel защита листа от удаления
Иногда бывает полезно защитить данные на листе от изменений другими пользователями, но при этом так же надо будет работать с данными на листе из 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
Основную роль здесь играет параметр UserInterfaceOnly . Если его установить в True , то это говорит Excel-ю, что коды VBA могут выполнять действия по изменению ячеек, не снимая защиты методом Unprotect. Однако сама защита листа при этом не снимается и вручную изменить данные ячеек, не сняв защиту с листа, невозможно.
Код выше устанавливает такую защиту только на активный лист книги. Но можно указать лист явно(например установить защиту на лист с именем Лист1 в активной книге и лист, идущий вторым по порядку в книге( Sheets(2) )):
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). А как будет выглядеть строка кода с разрешенными параметрами можно узнать, записав макрорекордером установку защиты листа с нужными параметрами:
После этого получится строка вроде такой:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True
здесь я разрешил использовать автофильтр( AllowFiltering:=True ), вставлять строки( AllowInsertingRows:=True ) и столбцы( AllowInsertingColumns:=True ).Чтобы добавить возможность изменять данные ячеек только через код VBA, останется добавить параметр UserInterfaceOnly:=True:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True
и так же неплохо бы добавить и пароль для снятия защиты, т.к. запись макрорекордером не записывает пароль:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"
Этот метод всем хорош, все отлично, но. Параметр UserInterfaceOnly сбрасывается сразу после закрытия книги. Т.е. если установить таким образом защиту на лист и закрыть книгу, то при следующем открытии защиты этой уже не будет - останется лишь стандартная защита. Поэтому, если необходимо такую защиту видеть постоянно, то данный макрос лучше всего прописывать на событие открытия книги(модуль ЭтаКнига(ThisWorkbook)).
Сделать это можно таким кодом:
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("Отчет", "База", "Бланк")
Примечание: Метод защиты через UsefInterface всем хорош, но есть одно ограничение: метод невозможно использовать в книге с общим доступом(Рецензирование -Доступ к книге), т.к. при общем доступе существуют ограничения, среди которых и такое, которое запрещает изменять параметры защиты для книги в общем доступе.
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Всем Добрый день, очень странный момент. Excel 2003. Если устанавливаю значение EnableSelection через код:
ActiveSheet.EnableSelection = xlUnlockedCells
То при сохранении файла и повторном открытии значение данного параметра скидывается. Все ячейки доступны для выбора.
Если же это делаю через интерфейс excel(сервис ->установить защиту) то все нормально. После закрытия/открытия всё сохраняется.
Пробовал записать макроркордером установку защиты, ну в общем то он тоже самое и записывает:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Так же пробую именно этим кодом установить недоступность для выделения, свойство устанавливается, но после сохранение/закрытия/повторного открытия так же сбрасывается на ноль (все ячейки доступны для выделения).
В excel 2007 таких проблем нет. Всё корректно сохраняется.
Может быть у кого то есть мысли по данной ситуации? Благодарю за помощь.
Нашел ответ, может кому пригодиться. Данный баг возникает в локализованной версии excel 2003 без сервис пака. Установка SP3 решила проблему. Нашел пользователей с подобной проблемой на другом ресурсе.
А как можно защитить только отдельные листы из всей книги?
Влад, вызывайте процедуру защиты(Protect_for_User_Non_for_VBA) несколько раз, передавая в неё нужный лист по очереди. Или цикл по листам с проверкой на имя. Все зависит от ситуации и имен листов.
Добрый день! Подскажи Пробовал защитить первый лист по вышеописанной процедуре в котором есть таблица, собираются данные с других листов книги макросом записанным в первом листе. - не работает, пропадают данные с первого листа ( сводного ), но если снять защиту с листа первого, и сделать запись в др листах книги, то таблица отображается в первозданном виде. В первом листе и на др листах шапки таблицы одинаковые, есть выпадающиеся списки, в отдельных столбцах, - в других прописаны разные формулы. Пробовал записать на первый лист код в другой книге на чистом листе все работает.
Private Sub Workbook_Open()
'включаем защиту первого листа для пользователя, но не макроса
Worksheets("Лист1").Protect Password:="123", UserInterfaceOnly:=True
End Sub
Помогите решить проблему за ранее спасибо!
Подскажите, почему данный подход не работает, когда пытаюсь записывать изменения в ячейке в комментарий. на строке AddComment выдает ошибку, лист защищен. Но я же вношу комментарий через VBA и по идее защита не должна сработать. Причем значение в ячейку вносится, а вот комментарий уже нет.
Потому что комментарии - это объекты. И они несколько отдельный элемент защиты. При установке защиты разрешите изменение объектов и все будет работать.
Дмитрий, дорый день, благодарю за полезную информацию, а подскажите, пож-та, возможно ли сделать так, чтобы защита работала следующим образом: формулы были скрыты, но при этом оставалась возможность не только добавления столбцов и работы с группированными данными, но и возможность удаления столбцов? или это невозможно решить в рамках VBA? не могу найти подходящий макрос в интернете(
Option Explicit Private Sub Workbook_Open() Dim wsSh As Worksheet Set wsSh = Me.Sheets("рынок") wsSh.EnableOutlining = True wsSh.Protect Password:="978", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True End Sub
вот этот не работает так как нужно(
Заранее благодарю за Ваш ответ, Маша
Мари, дело в том, что для возможности удаления строк и столбцов на защищенном листе необходимо, чтобы все ячейки в удаляемой строке или столбце имели атрибут "Не защищаемая". Я подробно рассматривал эти моменты в статье(видео прилагается): Защита листов и ячеек в MS Excel
Добрый день! А подскажите такую же команду только для Word. Здесь нет данной программы и функция не работает.
Вероника, в Word защита совершенно иначе работает и там просто нет аналогичной возможности.
Но как же тогда обеспечить работу макросов в защищенном режиме? Это вообще не возможно в word?
Вероника, во-первых непонятно, какую защиту Вы применяете в Word. От этого многое зависит. Во-вторых: можно кодом сначала снять защиту, потом поставить обратно. В Word также для документа есть методы Protect и UnProtect со своими параметрами. Их можно также просмотреть, записав макрорекордером установку защиты с нужными параметрами.
Добрый день! Дмитрий,вышеописанный Вами способ по защите от редактирования прекрасно работает. Но. Как быть если это все нужно реализовать в книге с общим доступом? Это возможно?
Обратите внимание на примечание в конце статьи. Там я четко написал, что при общем доступе данный метод использовать не получится.
Спрошу по-другому. Существует-ли способ добиться того же результата в книге с общим доступом? Или это в принципе невозможно?
Поделитесь своим мнением
Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Как автоматически защитить все рабочие листы при закрытии книги Excel?
Обычно вы можете вручную защитить лист или всю книгу паролем с помощью функции «Защитить лист» или «Защитить книгу» в Excel. Но если вы хотите автоматически защитить все рабочие листы паролем при закрытии книги, что вы можете сделать? Эта статья покажет вам метод VBA для этого.
Quickly Protect multiple/all worksheets with password at the same time in current workbook:
The Protect Worksheet utility of Kutools for Excel helps you easily protect multiple or all worksheets with password at the same time. See below screenshot:
- Enable the Protect Worksheet utility
- Check the worksheets you will protect at once
- Specify password
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Защитите все листы при закрытии книги Excel с помощью кода VBA
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Чтобы автоматически защитить все листы при закрытии книги, примените следующий код VBA.
1. В книге, все листы которой вы хотите защитить при закрытии файла, нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. в Microsoft Visual Basic для приложений окно, дважды щелкните значок Эта рабочая тетрадь на левой панели, а затем скопируйте ниже код VBA в окно кода. Смотрите скриншот:
Код VBA: защитить все листы при закрытии книги
Tips:
2) If you just want to automatically protect a specified worksheet when closing the workbook, the below VBA code can help you.
3. Then press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.
After the above step, all worksheets or specified worksheet will be protected automatically when closing the workbook.
Notes:
2. Please go to the certain worksheet, click Review > Unprotect Sheet. And then type in the password you specified in the VBA code to unprotect this worksheet.
выражения. Protect (Password, DrawingObjects, Contents, Scenarios, 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 и обратная связь.
Если вы работаете(или работали) в более-менее крупной компании, то 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 и им подобных) возможно и другими методами.
Иногда бывает полезно защитить данные на листе от изменений другими пользователями, но при этом так же надо будет работать с данными на листе из 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
Основную роль здесь играет параметр UserInterfaceOnly . Если его установить в True , то это говорит Excel-ю, что коды VBA могут выполнять действия по изменению ячеек, не снимая защиты методом Unprotect. Однако сама защита листа при этом не снимается и вручную изменить данные ячеек, не сняв защиту с листа, невозможно.
Код выше устанавливает такую защиту только на активный лист книги. Но можно указать лист явно(например установить защиту на лист с именем Лист1 в активной книге и лист, идущий вторым по порядку в книге( Sheets(2) )):
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). А как будет выглядеть строка кода с разрешенными параметрами можно узнать, записав макрорекордером установку защиты листа с нужными параметрами:
После этого получится строка вроде такой:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True
здесь я разрешил использовать автофильтр( AllowFiltering:=True ), вставлять строки( AllowInsertingRows:=True ) и столбцы( AllowInsertingColumns:=True ).Чтобы добавить возможность изменять данные ячеек только через код VBA, останется добавить параметр UserInterfaceOnly:=True:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True
и так же неплохо бы добавить и пароль для снятия защиты, т.к. запись макрорекордером не записывает пароль:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True, Password:="1111"
Этот метод всем хорош, все отлично, но. Параметр UserInterfaceOnly сбрасывается сразу после закрытия книги. Т.е. если установить таким образом защиту на лист и закрыть книгу, то при следующем открытии защиты этой уже не будет - останется лишь стандартная защита. Поэтому, если необходимо такую защиту видеть постоянно, то данный макрос лучше всего прописывать на событие открытия книги(модуль ЭтаКнига(ThisWorkbook)).
Сделать это можно таким кодом:
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("Отчет", "База", "Бланк")
Примечание: Метод защиты через UsefInterface всем хорош, но есть одно ограничение: метод невозможно использовать в книге с общим доступом(Рецензирование -Доступ к книге), т.к. при общем доступе существуют ограничения, среди которых и такое, которое запрещает изменять параметры защиты для книги в общем доступе.
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Всем Добрый день, очень странный момент. Excel 2003. Если устанавливаю значение EnableSelection через код:
ActiveSheet.EnableSelection = xlUnlockedCells
То при сохранении файла и повторном открытии значение данного параметра скидывается. Все ячейки доступны для выбора.
Если же это делаю через интерфейс excel(сервис ->установить защиту) то все нормально. После закрытия/открытия всё сохраняется.
Пробовал записать макроркордером установку защиты, ну в общем то он тоже самое и записывает:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Так же пробую именно этим кодом установить недоступность для выделения, свойство устанавливается, но после сохранение/закрытия/повторного открытия так же сбрасывается на ноль (все ячейки доступны для выделения).
В excel 2007 таких проблем нет. Всё корректно сохраняется.
Может быть у кого то есть мысли по данной ситуации? Благодарю за помощь.
Нашел ответ, может кому пригодиться. Данный баг возникает в локализованной версии excel 2003 без сервис пака. Установка SP3 решила проблему. Нашел пользователей с подобной проблемой на другом ресурсе.
А как можно защитить только отдельные листы из всей книги?
Влад, вызывайте процедуру защиты(Protect_for_User_Non_for_VBA) несколько раз, передавая в неё нужный лист по очереди. Или цикл по листам с проверкой на имя. Все зависит от ситуации и имен листов.
Добрый день! Подскажи Пробовал защитить первый лист по вышеописанной процедуре в котором есть таблица, собираются данные с других листов книги макросом записанным в первом листе. - не работает, пропадают данные с первого листа ( сводного ), но если снять защиту с листа первого, и сделать запись в др листах книги, то таблица отображается в первозданном виде. В первом листе и на др листах шапки таблицы одинаковые, есть выпадающиеся списки, в отдельных столбцах, - в других прописаны разные формулы. Пробовал записать на первый лист код в другой книге на чистом листе все работает.
Private Sub Workbook_Open()
'включаем защиту первого листа для пользователя, но не макроса
Worksheets("Лист1").Protect Password:="123", UserInterfaceOnly:=True
End Sub
Помогите решить проблему за ранее спасибо!
Подскажите, почему данный подход не работает, когда пытаюсь записывать изменения в ячейке в комментарий. на строке AddComment выдает ошибку, лист защищен. Но я же вношу комментарий через VBA и по идее защита не должна сработать. Причем значение в ячейку вносится, а вот комментарий уже нет.
Потому что комментарии - это объекты. И они несколько отдельный элемент защиты. При установке защиты разрешите изменение объектов и все будет работать.
Дмитрий, дорый день, благодарю за полезную информацию, а подскажите, пож-та, возможно ли сделать так, чтобы защита работала следующим образом: формулы были скрыты, но при этом оставалась возможность не только добавления столбцов и работы с группированными данными, но и возможность удаления столбцов? или это невозможно решить в рамках VBA? не могу найти подходящий макрос в интернете(
Option Explicit Private Sub Workbook_Open() Dim wsSh As Worksheet Set wsSh = Me.Sheets("рынок") wsSh.EnableOutlining = True wsSh.Protect Password:="978", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True End Sub
вот этот не работает так как нужно(
Заранее благодарю за Ваш ответ, Маша
Мари, дело в том, что для возможности удаления строк и столбцов на защищенном листе необходимо, чтобы все ячейки в удаляемой строке или столбце имели атрибут "Не защищаемая". Я подробно рассматривал эти моменты в статье(видео прилагается): Защита листов и ячеек в MS Excel
Добрый день! А подскажите такую же команду только для Word. Здесь нет данной программы и функция не работает.
Вероника, в Word защита совершенно иначе работает и там просто нет аналогичной возможности.
Но как же тогда обеспечить работу макросов в защищенном режиме? Это вообще не возможно в word?
Вероника, во-первых непонятно, какую защиту Вы применяете в Word. От этого многое зависит. Во-вторых: можно кодом сначала снять защиту, потом поставить обратно. В Word также для документа есть методы Protect и UnProtect со своими параметрами. Их можно также просмотреть, записав макрорекордером установку защиты с нужными параметрами.
Добрый день! Дмитрий,вышеописанный Вами способ по защите от редактирования прекрасно работает. Но. Как быть если это все нужно реализовать в книге с общим доступом? Это возможно?
Обратите внимание на примечание в конце статьи. Там я четко написал, что при общем доступе данный метод использовать не получится.
Спрошу по-другому. Существует-ли способ добиться того же результата в книге с общим доступом? Или это в принципе невозможно?
Поделитесь своим мнением
Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Читайте также: