Vba excel установить защиту листа: Как защитить лист от пользователя, но не от макроса?

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

Хитрости »

1 Май 2011       Дмитрий       159818 просмотров

Иногда бывает полезно защитить данные на листе от изменений другими пользователями, но при этом так же надо будет работать с данными на листе из 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 всем хорош, но есть одно ограничение: метод невозможно использовать в книге с общим доступом(Рецензирование -Доступ к книге), т.к. при общем доступе существуют ограничения, среди которых и такое, которое запрещает изменять параметры защиты для книги в общем доступе.

Также см.:
Как разрешить изменять только выбранные ячейки?
Защита листов/снятие защиты
Как оставить возможность работать со структурой на защищенном листе?

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

    Видеоуроки

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

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

Метод Worksheet.

Protect (Excel) | Microsoft Learn

Twitter




LinkedIn




Facebook




Адрес электронной почты

• 
  Статья
• 
  07/13/2022
• Чтение занимает 3 мин
• 
  

Защищает лист, чтобы его нельзя было изменить.

Синтаксис

expression.Protect (Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

Выражение Переменная, представляющая объект Worksheet .

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
Password	Необязательный	Variant	Строка, указывавая пароль с учетом регистра для листа или книги. Если этот аргумент опущен, можно снять защиту листа или книги без использования пароля. В противном случае необходимо указать пароль, чтобы снять защиту листа или книги. Если вы забыли пароль, вы не сможете снять защиту с листа или книги. Используйте надежные пароли, содержащие строчные и прописные буквы, цифры и знаки. В ненадежных паролях не используются сочетания таких элементов. Надежный пароль: Y6dh!et5. Слабый пароль: House27. Длина паролей должна быть не меньше 8 символов. В парольной фразе лучше использовать 14 или более символов. Очень важно запомнить пароль. Если вы его забудете, корпорация Майкрософт не сможет его восстановить. Храните пароли, записанные на бумаге, в безопасном месте вдали от информации, которую они защищают.
Объекты DrawingObject	Необязательный	Variant	Значение true для защиты фигур. Значение по умолчанию — True.
Contents	Необязательный	Variant	Значение true для защиты содержимого. Для диаграммы это защищает всю диаграмму. Для листа это защищает заблокированные ячейки. Значение по умолчанию — True.
Scenarios	Необязательный	Variant	Значение true для защиты сценариев. Этот аргумент действителен только для листов. Значение по умолчанию — True.
UserInterfaceOnly	Необязательный	Variant	Значение true для защиты пользовательского интерфейса, но не макросов. Если этот аргумент опущен, защита применяется как к макросам, так и к пользовательскому интерфейсу.
AllowFormattingCells	Необязательный	Variant	Значение true позволяет пользователю форматировать любую ячейку на защищенном листе. Значение по умолчанию — False.
AllowFormattingColumns	Необязательный	Variant	Значение True позволяет пользователю форматировать любой столбец на защищенном листе. Значение по умолчанию — False.
AllowFormattingRows	Необязательный	Variant	Значение true позволяет пользователю форматировать любую строку на защищенном листе. Значение по умолчанию — False.
AllowInsertingColumns	Необязательный	Variant	Значение true позволяет пользователю вставлять столбцы на защищенный лист. Значение по умолчанию — False.
AllowInsertingRows	Необязательный	Variant	Значение true позволяет пользователю вставлять строки на защищенный лист. Значение по умолчанию — False.
AllowInsertingHyperlinks	Необязательный	Variant	Значение true позволяет пользователю вставлять гиперссылки на защищенный лист. Значение по умолчанию — False.
AllowDeletingColumns	Необязательный	Variant	Значение true позволяет пользователю удалять столбцы на защищенном листе, где каждая удаляемая ячейка в столбце разблокирована. Значение по умолчанию — False.
AllowDeletingRows	Необязательный	Variant	True позволяет пользователю удалять строки на защищенном листе, где каждая удаляемая ячейка строки разблокирована. Значение по умолчанию — False.
AllowSorting	Необязательный	Variant	Значение true позволяет пользователю сортировать данные на защищенном листе. Каждая ячейка в диапазоне сортировки должна быть разблокирована или незащищена. Значение по умолчанию — False.
AllowFiltering	Необязательный	Variant	Значение true позволяет пользователю задавать фильтры на защищенном листе. Пользователи могут изменять критерии фильтра, но не могут включить или отключить автоматический фильтр. Пользователи могут устанавливать фильтры для существующего автоматического фильтра. Значение по умолчанию — False.
AllowUsingPivotTables	Необязательный	Variant	True позволяет пользователю использовать сводная таблица отчетов на защищенном листе. Значение по умолчанию — False.

Примечание

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

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

Примечание

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

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

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

Защитный лист VBA | Защитите лист Excel паролем, используя VBA

. Мы можем защитить лист Excel , используя код VBA , который не позволяет пользователю вносить какие-либо изменения в данные рабочего листа. Все, что они могут сделать, это просто прочитать отчет. Для этого у нас есть встроенный метод VBA под названием «Защитить».

Как мы защищаем наши рабочие листы в Excel; Точно так же мы можем использовать VBA для защиты наших рабочих листов. Есть два способа защитить лист с помощью инструкции .protect. Один с паролем, другой без пароля. Синтаксис для защиты рабочего листа следующий: Worksheets().Protect Password.

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

Содержание

• Защитный лист Excel VBA
  • Синтаксис
  • Как защитить лист с помощью кода VBA?
    • Шаг 1: Выберите лист, который необходимо защитить
    • Шаг 2: Определите переменную рабочего листа
    • Шаг 3: Дайте ссылку на рабочий лист
    • Шаг 4: Выберите метод защиты
    • Шаг 5: Введите пароль
    • Шаг 6: Запустите код
  • Рекомендуемые статьи

Синтаксис

Параметров защиты. Это не похоже на снятие защиты с листа. Давайте посмотрим на синтаксис метода Protect с паролем.

Не пугайтесь синтаксиса. Вместо этого взгляните на объяснение каждого аргумента ниже.

• Имя рабочего листа: Во-первых, мы должны указать, какой рабочий лист мы будем защищать.
• Пароль: Нам нужно ввести пароль, который мы используем для защиты. Если мы проигнорируем этот параметр, Excel заблокирует лист без пароля. При снятии защиты он будет снимать защиту без запроса пароля.
• Примечание: Запомните пароль, который вы даете, потому что, если вы забудете, вам придется пройти через различные трудные пути.
• Объект рисования: Если вы хотите защитить объекты на листе, вы можете передать аргумент как ИСТИНА или ЛОЖЬ. Значение по умолчанию верно.
• Contents: Чтобы защитить содержимое рабочего листа, установите для параметра значение TRUE или FALSE. Значение по умолчанию неверно. Таким образом, он будет защищать только заблокированные ячейки. И значение по умолчанию — ИСТИНА.
• Сценарии: Если есть какие-либо анализы «что, если» в excelАнализ «что, если» в ExcelАнализ «что, если» в Excel — это инструмент для создания различных моделей, сценариев и таблиц данных. Это позволяет изучить, как изменение значений влияет на результаты на листе. Тремя компонентами анализа «что, если» являются «Диспетчер сценариев», «Поиск цели» в Excel и «Таблица данных» в Excel. Дополнительные сценарии мы также можем защитить. Чтобы защитить ИСТИНА или ЛОЖЬ. Значение по умолчанию верно.
• Только пользовательский интерфейс: Если вы хотите защитить пользовательский интерфейс, отличный от макросов, это должно быть TRUE. Если этот аргумент опущен, он защитит макросы и пользовательский интерфейс. Если вы установите для аргумента значение TRUE, он защитит только пользовательский интерфейс. Значение по умолчанию неверно.
• Разрешить форматирование ячеек: Если вы хотите разрешить пользователю форматировать ячейку, вы можете установить для параметра значение ИСТИНА или ЛОЖЬ. Значение по умолчанию неверно.
• Разрешить форматирование столбцов: Если вы хотите разрешить пользователю форматировать любой столбец в защищенном листе, вы можете установить для параметра значение TRUE или FALSE. Значение по умолчанию неверно.
• Разрешить форматирование строк: Если вы хотите разрешить пользователю форматировать любую строку в защищенном листе, вы можете установить для параметра значение TRUE или FALSE. Значение по умолчанию неверно.
• Разрешить Вставлять столбцы в VBA: если вы хотите разрешить пользователю вставлять новые столбцы, вам нужно установить для этого параметра значение TRUE. Значение по умолчанию неверно.
• Разрешить вставку строк: Если вы хотите разрешить пользователю вставлять новые строкиВставка новых строкЧтобы вставить строки, мы используем метод рабочего листа с командой вставки для вставки строки, мы также предоставляем ссылку на строку, где мы хотим вставить другую строку, аналогичную для столбцов. Подробнее, вы должны установить это значение TRUE. Значение по умолчанию неверно.
• Разрешить вставку гиперссылок: Если вы хотите разрешить пользователю вставлять гиперссылки, установите для этого параметра значение TRUE. Значение по умолчанию неверно.
• Разрешить удаление столбцов: Если вы хотите разрешить пользователю удалять столбцы в VBAУдалить столбцы в VBAВ VBA удалить столбцы очень просто. Чтобы выбрать столбец, мы должны сначала использовать свойство COLUMNS, а затем построить синтаксис для метода удаления столбца в VBA следующим образом: Столбцы (ссылка на столбец). Удалить читать дальше, тогда вам нужно установить это в TRUE. Значение по умолчанию неверно.
• Разрешить удаление строк: Если вы хотите разрешить пользователю удалять строки, установите для этого параметра значение TRUE. Значение по умолчанию неверно.
• Разрешить сортировку: Если вы хотите разрешить пользователю сортировать данные, установите для этого параметра значение TRUE. Значение по умолчанию неверно.
• Разрешить фильтрацию: Если вы хотите разрешить пользователю фильтровать данные, установите для этого параметра значение TRUE. Значение по умолчанию неверно.
• Разрешить использование сводных таблиц: Если вы хотите разрешить пользователю использовать сводные таблицы. Используйте сводные таблицы. рабочий лист. Он может суммировать, сортировать, группировать и реорганизовывать данные, а также выполнять над ними другие сложные вычисления. Подробнее, тогда вам нужно установить для этого параметра значение TRUE. Значение по умолчанию неверно.

Как защитить лист с помощью кода VBA?

Вы можете скачать этот шаблон Excel для защиты VBA здесь — Шаблон Excel для защиты VBA

Шаг 1: выберите лист, который необходимо защитить

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

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

Шаг 2: определение переменной рабочего листа

После упоминания имени рабочего листа поставьте точку, но мы не видим ни одного списка IntelliSense для работы. Таким образом, это усложняет работу. Чтобы получить доступ к списку IntelliSense, определите переменную как рабочий лист.

Код:

 Sub Protect_Example1()
  Dim Ws As рабочий лист
End Sub 

Шаг 3. Дайте ссылку на рабочий лист

Теперь установите ссылку на рабочий лист для переменной как  Worksheets («Мастер-лист») .

Код:

 Sub Protect_Example1()
   Dim Ws As рабочий лист
   Установите Ws = Рабочие листы («Мастер-лист»)
End Sub 

Теперь переменная «Ws» содержит ссылку на рабочий лист с именем «Главный лист». Используя эту переменную, мы можем получить доступ к списку IntelliSense.

Шаг 4. Выберите метод защиты

Выберите метод «Защита» из списка IntelliSense.

Шаг 5: Введите пароль

Укажите пароль в двойных кавычках.

Код:

 Sub Protect_Example1()
   Dim Ws As рабочий лист
   Установите Ws = Рабочие листы («Мастер-лист»)
   Пароль Ws.Protect: = "Мой пароль"
End Sub 

Шаг 6: Запустите код

Запустите код вручную или используйте горячую клавишу F5. Затем он защитит лист с именем «Мастер-лист».

Когда лист защищен, если мы хотим его изменить, он показывает сообщение об ошибке, как показано ниже.

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

 Sub Protect_Example2()
  Dim Ws As рабочий лист
  Для каждого W в ActiveWorkbook.Worksheets
  Пароль Ws.Protect: = "Мой пароль"
  Следующая Вт
End Sub 

Примечание: Используйте другие параметры для экспериментов.

Рекомендуемые статьи

Эта статья представляет собой руководство по VBA Protect Sheets. Здесь мы узнаем, как использовать методы Protect в VBA для защиты или блокировки листа Excel с помощью пароля, а также рассмотрим практический пример и загружаемый шаблон. Ниже вы можете найти несколько полезных статей по Excel VBA: –

• VBA Do Loop
• Переименовать лист в VBA
• Активировать лист с помощью кода VBA
• Использовать функцию рабочего листа в VBA

Защита и снятие защиты листов VBA (более 25 примеров)

пользователь Excel. Нет ничего хуже, чем когда кто-то, кто не знает, что делает, перепечатывает необходимые формулы и значения ячеек. Еще хуже, когда этим человеком оказываемся мы; достаточно одного случайного нажатия клавиши, и вдруг весь рабочий лист заполняется ошибками. В этом посте мы рассмотрим использование VBA для защиты и снятия защиты с листов.

Защита не является надежной, но предотвращает случайное изменение неосведомленным пользователем.

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

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

Загрузите файл примера : Щелкните ссылку ниже, чтобы загрузить файл примера, используемый для этого поста:

0016 VBA Protect and Unprotect Sheets.zip Download

Contents

Адаптация кода для ваших целей

Если не указано иное, каждый пример ниже основан на одном конкретном рабочем листе. Каждый код включает листов («Лист1»). , это означает, что действие будет применено к этому конкретному листу. Например, следующее защищает Sheet1.

 Sheets("Sheet1").Protect 

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

Использование активного листа

Активным листом является тот лист, который в данный момент используется в окне Excel.

 ActiveSheet.Protect 

Применение листа к переменной

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

 Dim ws As Рабочий лист
Установите ws = Листы ("Лист1")
ws.Protect 

Далее в этом посте мы рассмотрим примеры кода для циклического просмотра каждого листа и быстрого применения защиты.

Защита и снятие защиты: основные примеры

Давайте начнем с нескольких простых примеров защиты и снятия защиты листов в Excel.

Защита листа без пароля

 Sub ProtectSheet()
'Защитить рабочий лист
Листы("Лист1").Защитить
End Sub 

Снять защиту листа (без пароля)

 Sub UnProtectSheet()
'Снять защиту листа
Листы("Лист1"). Снять защиту
End Sub 

Защита и снятие защиты с помощью пароля

Добавление пароля для обеспечения дополнительного уровня защиты с помощью VBA достаточно просто. Пароль в этих примерах жестко зашит в макрос; это может быть не лучшим для вашего сценария. Возможно, лучше применить строковую переменную или захватить пароли пользователей с помощью InputBox.

Лист защиты VBA с паролем

 Sub ProtectSheetWithPassword()
'Защитить лист паролем
Sheets("Sheet1").Защитить пароль:="myPassword"
Конец суб 

VBA Снять защиту с листа паролем

 Sub UnProtectSheetWithPassword()
'Снять защиту листа паролем
Sheets("Sheet1").Снять защиту с пароля:="myPassword"
End Sub 

ПРИМЕЧАНИЕ. Для изменения настроек нет необходимости снимать защиту, а затем повторно защищать лист. Вместо этого просто защитите снова с новыми настройками.

Использование пароля на основе пользовательского ввода

Использование пароля, включенного в код, может частично свести на нет преимущества наличия пароля. Поэтому коды в этом разделе предоставляют примеры использования VBA для защиты и снятия защиты на основе пользовательского ввода. В обоих случаях нажатие Отмена эквивалентно отсутствию пароля.

Защита вводом пользователем пароля

 Sub ProtectSheetWithPasswordFromUser()
'Защитить лист паролем
Sheets("Sheet1").Protect Password:=InputBox("Введите защитный пароль:")
End Sub 

Снять защиту с помощью введенного пользователем пароля

 Sub UnProtectSheetWithPasswordFromUser()
'Защитить лист паролем
Листы("Лист1").Снять защиту _
    Password:=InputBox("Введите защитный пароль:")
Конец суб 

Обнаружение ошибок при вводе неверного пароля

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

Приведенный ниже код перехватывает ошибку и выводит пользовательское сообщение.

 Sub CatchErrorForWrongPassword()
'Продолжить, даже если обнаружена ошибка
При ошибке Возобновить Далее
'Применить неверный пароль
Sheets("Sheet1"). Снять защиту с пароля:="incorrectPassword"
'Проверить, не произошла ли ошибка
Если Err.Number <> 0 Тогда
    MsgBox "Введен неверный пароль"
    Выйти из подпрограммы
Конец, если
'Сбросить, чтобы отображались обычные сообщения об ошибках
При ошибке Перейти к 0
Конец суб 

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

Применение защиты к различным частям рабочего листа

VBA обеспечивает возможность защиты 3 аспектов рабочего листа:

• Содержимое — то, что вы видите на сетке
• Объекты — фигуры и диаграммы, находящиеся на лицевой стороне grid
• Сценарии — сценарии, содержащиеся в разделе What If Analysis ленты

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

Защитить содержимое

 Sub ProtectSheetContents()
'Применить только защиту содержимого рабочего листа
Sheets("Sheet1").Защитить пароль:="myPassword", _
    DrawingObjects:=False, _
    Содержание:=Верно, _
    Сценарии: = Ложь
End Sub 

Защитить объекты

 Sub ProtectSheetObjects()
'Применить только защиту объектов рабочего листа
Sheets("Sheet1"). Защитить пароль:="myPassword", _
    DrawingObjects:=Истина, _
    Содержание: = Ложь, _
    Сценарии: = Ложь
End Sub 

Сценарии защиты

 Sub ProtectSheetScenarios()
'Применить только защиту сценария рабочего листа
Sheets("Sheet1").Защитить пароль:="myPassword", _
    DrawingObjects:=False, _
    Содержание: = Ложь, _
    Сценарии: = Верно
End Sub 

Защитить содержимое, объекты и сценарии

 Sub ProtectSheetAll()
'Применить защиту рабочего листа к содержимому, объектам и сценариям
Sheets("Sheet1").Защитить пароль:="myPassword", _
    DrawingObjects:=Истина, _
    Содержание:=Верно, _
    Сценарии: = Верно
Конец суб 

Применение защиты к нескольким листам

Как мы видели, защита применяется по одному листу за раз. Таким образом, зацикливание — отличный способ быстро применить настройки к большому количеству листов. Примеры в этом разделе относятся не только к Листу1, как в предыдущих примерах, но и включают все рабочие листы или все выбранные рабочие листы.

Защитить все листы в активной книге

 Sub ProtectAllWorksheets()
'Создать переменную для хранения рабочих листов
Dim ws As рабочий лист
'Перебрать каждый рабочий лист в активной книге
Для каждого ws в ActiveWorkbook.Worksheets
    'Защитить каждый рабочий лист
    ws.Защитить пароль: = "мой пароль"
Следующий мс
Конец суб 

Защитить выбранные листы в активной книге

 Sub ProtectSelectedWorksheets()
Dim ws As рабочий лист
Dim sheetArray как вариант
'Захват выбранных листов
Установить SheetArray = ActiveWindow.SelectedSheets
'Перебрать каждый рабочий лист в активной книге
Для каждого ws в массиве листов
    При ошибке Возобновить Далее
    'Выберите рабочий лист
    ws.Select
    'Защитить каждый рабочий лист
    ws.Защитить пароль: = "мой пароль"
    При ошибке Перейти к 0
Следующий мс
листМассив.Выбрать
End Sub 

Снять защиту со всех листов в активной книге

 Sub UnprotectAllWorksheets()
'Создать переменную для хранения рабочих листов
Dim ws As рабочий лист
'Перебрать каждый рабочий лист в активной книге
Для каждого ws в ActiveWorkbook. Worksheets
'Снять защиту с каждого рабочего листа
ws.Unprotect Password:="myPassword"
Следующий мс
End Sub 

Проверка защищенности рабочего листа

Коды в этом разделе проверяют, был ли применен каждый тип защиты.

Проверить, защищено ли содержимое листа

 Sub CheckIfSheetContentsProtected()
'Проверить, защищено ли содержимое рабочих листов
If Sheets("Sheet1").ProtectContents Then MsgBox "Защищенное содержимое"
Конец суб 

Проверить, защищены ли объекты Sheet

 Sub CheckIfSheetObjectsProtected()
'Проверить, защищены ли объекты рабочего листа
If Sheets("Sheet1").ProtectDrawingObjects Then MsgBox "Защищенные объекты"
End Sub 

Проверить, защищены ли сценарии листа

 Sub CheckIfSheetScenariosProtected()
'Проверить, защищены ли сценарии рабочего листа
If Sheets("Sheet1").ProtectScenarios Then MsgBox "Защищенные сценарии"
End Sub 

Изменение заблокированного или разблокированного состояния ячеек, объектов и сценариев

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

Если лист не защищен, настройка блокировки не действует. Каждый объект становится заблокированным на защиту.

Во всех примерах в этом разделе каждый объект/элемент блокируется при защите. Чтобы установить разблокированный, измените значение на False .

Блокировка ячейки

 Sub LockACell()
'Изменение параметров блокировки или разблокировки ячеек
Листы("Лист1").Range("A1").Заблокировано = Истина
Конец суб 

Заблокировать все ячейки

 Sub LockAllCells()
'Изменение параметров блокировки или разблокировки ячеек все ячейки
Листы("Лист1").Cells.Locked = Истина
End Sub 

Блокировка графика

 Sub LockAChart()
'Изменение параметров блокировки или разблокировки диаграмм
Листы("Лист1").ChartObjects("Диаграмма 1").Заблокировано = Истина
End Sub 

Блокировка формы

 Sub LockAShape()
'Изменение параметра блокировки или разблокировки фигур
Листы("Лист1"). Фигуры("Прямоугольник 1").Заблокировано = Истина
End Sub 

Блокировка сценария

 ПодзамокASScenario()
'Изменение опции блокировки или разблокировки сценария
Sheets("Sheet1").Scenarios("scenarioName").Locked = True
End Sub 

Разрешение выполнения действий даже при защите

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

Разрешить действия листа при защите

 Sub AllowSheetActionsWhenProtected()
'Разрешение определенных действий, даже если рабочий лист защищен
Sheets("Sheet1").Защитить пароль:="myPassword", _
    DrawingObjects:=False, _
    Содержание:=Верно, _
    Сценарии: = Ложь, _
    AllowFormattingCells:=Истина, _
    АлловФорматтингКолоннс: = Истина, _
    АлловФорматтингРовс:=Истина, _
    Алловинсертингколумнс: = Ложь, _
    Алловинсертингровс:=ложь, _
    AllowInsertingHyperlinks:=False, _
    АлловДелетингКолоннс:=Истина, _
    АлловДелетингРовс:=Истина, _
    Разрешить сортировку: = Ложь, _
    Разрешить фильтрацию: = Ложь, _
    Разрешить использование сводных таблиц: = ложь
Конец суб 

Разрешить выбор любых ячеек

 Sub AllowSelectionAnyCells()
'Разрешение выбора заблокированных или разблокированных ячеек
Листы("Лист1"). EnableSelection = xlNoRestrictions
End Sub 

Разрешить выбор разблокированных ячеек

 Sub AllowSelectionUnlockedCells()
'Разрешить выбор только разблокированных ячеек
Листы("Лист1").EnableSelection = xlUnlockedCells
End Sub 

Не разрешать выбор любых ячеек

 Sub NoSelectionAllowed()
'Не разрешать выделение любых ячеек
Листы("Лист1").EnableSelection = xlNoSelection
Конец суб 

Разрешение кода VBA вносить изменения, даже когда он защищен

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

 Sub AllowVBAChangesOnProtectedSheet()
'Включить изменения на листе с помощью кода VBA, даже если он защищен
Sheets("Sheet1").Защитить пароль:="myPassword", _
    Усеринтерфацеонли: = Истина
End Sub 

К сожалению, этот параметр не сохраняется в рабочей книге. Его нужно запускать каждый раз, когда открывается рабочая книга. Поэтому вызов кода в событии Workbook_Open модуля Workbook, пожалуй, лучший вариант.

Разрешение использования функции «Группировать и разгруппировать»

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

 Sub AllowGroupingAndUngroupOnProtectedSheet()
'Разрешить пользователю группировать и разгруппировать, пока он защищен
Sheets("Sheet1").Защитить пароль:="myPassword", _
    Усеринтерфацеонли: = Истина
Листы("Листы1").EnableOutlining = Истина
End Sub 

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

Заключение

Вау! Это было много примеров кода; надеюсь, это охватывает все, что вам когда-либо понадобится для использования VBA для защиты и снятия защиты листов.

Связанные посты:

• Офисные скрипты — Рабочая книга и защита рабочих листов
• Код VBA для защиты пароля. Я Марк, и я запускаю Excel Off The Grid.

  Мои родители рассказали мне, что в 7 лет я объявил, что стану квалифицированным бухгалтером. Либо я был экстрасенсом, либо у меня не было воображения, как это и произошло. Однако мое путешествие по-настоящему началось только в 35 лет.

  В 2015 году я устроился на новую работу, на которой регулярно работал после 22:00. В результате я редко видел своих детей в течение недели. Итак, я начал искать секреты автоматизации Excel. Я обнаружил, что, создав небольшое количество простых инструментов, я могу комбинировать их по-разному, чтобы автоматизировать почти все свои обычные задачи. Это означало, что я мог работать меньше часов (и мне повысили зарплату!). Сегодня я обучаю этим техникам других специалистов в рамках нашей программы обучения, чтобы они тоже могли проводить меньше времени на работе (и больше времени со своими детьми и любимым делом).

  ---

  Вам нужна помощь в адаптации этого поста к вашим потребностям?

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

  Но если вы все еще испытываете трудности, вам следует:

  1. Почитайте другие блоги или посмотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, открыв для себя собственные решения.
  2. Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
  3. Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
  4. Используйте Excel Rescue, моего партнера-консультанта. Они помогают, предоставляя решения небольших проблем Excel.