Excel защищенный лист макросы работали

Правильная защита макросом

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

Worksheets("Лист1").Unprotect Password:="123"
'тут макрос делает действия
Worksheets("Лист1").Protect Password:="123"

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

Есть гораздо более легкий и красивый способ решить задачу.

Нажмите Alt+F11, чтобы попасть в редактор Visual Basic. Затем найдите в левом верхнем углу в окне Project Explorer (если его не видно, то нажмите Ctrl+R) модуль ЭтаКнига (ThisWorkbook) и откройте двойным щелчком:

Скопируйте туда этот код:

Private Sub Workbook_Open()
    'включаем защиту первого листа для пользователя, но не макроса
    Worksheets("Лист1").Protect Password:="123", UserInterfaceOnly:=True
    
    'второй лист защищаем аналогично, но с возможностью пользоваться группировкой
    Worksheets("Лист2").EnableOutlining = True
    Worksheets("Лист2").Protect Password:="555", UserInterfaceOnly:=True
End Sub

Эта процедура будет автоматически запускаться при открытии файла и ставить защиту на заданные листы книги. Причем параметр UserInterfaceOnly, который мы дополнительно ввели, указывает Excel, что защита не должна распространяться на действия выполняемые макросом, а только на операции пользователя. Для второго листа все еще веселее — строка с параметром EnableOutlining разрешает пользоваться группировкой (символы плюс-минус для сворачивания-разворачивания строк и столбцов) на защищенном листе.

Всего три строчки кода, зато как удобно!

Ссылки по теме

• Что такое макросы и куда копировать их код
• Как поставить защиту листа, книги или всего файла

Макросы Excel представляют собой замечательный способ экономии времени и исключения ошибок. Однако рано или поздно вы пытаетесь выполнить ваш любимый макрос Excel на рабочем листе, к которому была применена защита, с паролем или без, и получаете ошибку выполнения. Избежать ее можно при помощи следующего трюка.

Если вы когда-либо пытались выполнить макрос Excel на защищенном рабочем листе, то знаете, что, как только этот рабочий лист упоминается в макросе, макрос, вероятнее всего, перестает работать и появляется ошибка выполнения. Перед началом работы рекомендуем хорошенько отдохнуть, к вашим услугам гостиницы краснодара недорого и с полным пансионом. А теперь к делу. Один из способов обойти это — использовать код, например, следующий, чтобы снять защиту, а потом снова защитить рабочий лист, как это показано в листинге 7.21.

1
2
3
4
5
6

//Листинг 7.21
Sub MyMacro()
Sheet1.Unprotect Password:="Secret"
//ВАШ КОД
Sheet1.Protect Password:="Secret"
End Sub

Как можно видеть, этот код снимает защиту листа Sheet1 с паролем Secret, выполняет код, а затем снова включает защиту паролем. Этот код работает, но у него есть несколько недостатков. Например, он может вызвать ошибку и остановиться до того, как дойдет до строки кода Sheet1.Protect Password:=«Secret». Это, конечно же, приведет к тому, что рабочий лист останется незащищенным. Еще один недостаток заключается в том, что потребуется похожий код для всех макросов и всех рабочих листов.

Еще один способ обойти проблему — использовать UserlnterFaceOnly, необязательный аргумент метода Protect, которому можно присвоить значение True (значение по умолчанию равно False). Если значение этого аргумента равно True, Excel позволяет всем макросам Excel VBA выполняться на рабочих листах, защищенных с паролем или без пароля.

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

Чтобы избежать неразберихи, используйте событие Workbook_Open, которое выполняется при открытии рабочей книги. Так как это событие принадлежит объекту Workbook ЭтаКнига (ThisWorkbook), необходимо поместить следующий код в частный модуль объекта ЭтаКнига (ThisWorkbook). Для этого в версиях Excel для Windows правой кнопкой мыши щелкните значок Excel и в контекстном меню выберите команду Исходный текст (View Code). На Macintosh откройте объект Workbook в окне Projects в VBE. Затем введите код, показанный в листинге 7.22.

1
2
3
4
5
6
7
8

//Листинг 7.22
Private Sub WorkbookOpen()
//Если у вас разные пароли для всех рабочих листов.
 
Sheets(l).Protect Password:="Secret". UserInterFaceOnly:=True
Sheets(2).Protect Password:="Carrot". UserInterFaceOnly:=True
//Повторить необходимое количество раз.
End Sub

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

1
2
3
4
5
6
7
8

//Листинг 7.23
Private Sub WorkbookOpen()
Dim wSheet As Worksheet
 
	For Each wSheet In Worksheets
		wSheet.Protect Password:="Secret", UserInterFaceOnly:=True
	Next wSheet
End Sub

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

Хитрости »

1 Май 2011              162379 просмотров

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

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

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

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

    Видеоуроки

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



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

К сожалению, предложенные упрощенные варианты защиты листов не сработали почему-то. Пришлось макрос дорабатывать исходя из автозаписи макроса и потребностей:
1) надо блокировать листы выборочно (т.е. нельзя запустить цикл по перебору всех листов)
2) все таки блокировку включать макросом, а не вручную
В результате получилось следующее

Sub Protection()
Worksheets(«Instrukcijos»).Activate
Dim Msgs, Style
Dim pw As String
    Msgs = «Would you like to protect all sheets in active workbook?»
    Style = vbYesNo + vbDefaultButton2

        On Error GoTo Ups

        If MsgBox(Msgs, Style) = vbYes Then
        pw = InputBox(«Please enter password to protect all sheets in current workbook»)
        ActiveWorkbook.Protect (pw)
Application.ScreenUpdating = False
Worksheets(«Duomenu tikrinimas»).Activate
    Cells.Select
    Selection.Locked = False
    ‘Selection of Range for protection from changing
    Union(Range(«A1:C241,D6:E6,D105:E105,D145:E145,D152:E152,D157:E157,D161:E161» _
        ), Range(«D166:E166,D170:E170,D173:E173,D177:E177,D183:E183,D192:E192,D207:E207,D219:E219» _
        ), Range(«D228:E228,D234:E234,D238:E238»)).Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    Sheets(«Duomenu tikrinimas»).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = False
Worksheets(«Instrukcijos»).Activate
    Cells.Select
    Selection.Locked = False
    ‘Selection of Range for protection from changing
    Range(«A1:H1,B3:B17,C6,C8:C10,C13,D15:D17,B22:C25,A28:C28,A29:B169,A172:B195»).Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    Sheets(«Instrukcijos»).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = False
Worksheets(«Balansas, pna»).Activate
    Cells.Select
    Selection.Locked = False
    ‘Selection of Range for protection from changing
    Union(Range(«C167:D167,C171:D171,C174:D174,C176:D176,C178:D179,E1:F179,A1:B150,A155:B178» _
        ), Range(«C1:D11,C18:D18,C27:D29,C32:D32,C35:D35,C41:D41,C44:D46,C54:D54,C58:D58» _
        ), Range(«C60:D60,C65:D65,C68:D69,C72:D73,C79:D81,C86:D86,C89:D89,C93:D94» _
        ), Range(«C97:D97,C105:D105,C113:D114,C118:D118» _
        ), Range(«C131:D131,C135:D135,C139:D139,C150:D151,C159:D160,C163:D164»)).Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    Sheets(«Balansas, pna»).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
Worksheets(«Instrukcijos»).Activate
   MsgBox («All sheets were protected successfully»)
    End If
Exit Sub

Ups: MsgBox («Incorrect password (protection already exists)»)

    Worksheets(«Instrukcijos»).Activate
End Sub

Для снятия защиты у меня получился следующий макрос
Sub Unprotection()
   Worksheets(«Instrukcijos»).Activate
    Dim Msgs, Style
    Dim pw As String, ps As String, i As Integer
    Msgs = «Would you like to unprotect all sheets in active workbook?»
    Style = vbYesNo + vbDefaultButton2

        On Error GoTo Ups

        If MsgBox(Msgs, Style) = vbYes Then
        pw = InputBox(«Please enter password to unprotect all sheets in current workbook»)
        ActiveWorkbook.Unprotect (pw)
        Application.ScreenUpdating = True
    For i = 1 To ActiveWorkbook.Sheets.Count
        Application.ScreenUpdating = False
        Sheets(i).Unprotect (pw)
    Next i
        Application.ScreenUpdating = True
        MsgBox («All sheets were unprotected successfully»)
    End If
Exit Sub

Ups: MsgBox («Incorrect password (protection already exists)»)
Worksheets(«Instrukcijos»).Activate
End Sub

Остался нерешенным следующий вопрос — макрос защищает от редактирования ячейку, где стоит простая ссылка (чтобы ее случайно не стерли) — но нужна необходимость возможности перехода по этой ссылке — как это можно реализовать не снимая защиты?

Skip to content

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Шаг 1: Защищает лист с паролем
Sheets("Отчет1").Protect Password:="Akademia_Excel"
'Шаг 2: Сохраняем книгу
ActiveWorkbook.Save
End Sub