Трюк №93. Выполнение процедур на защищенных рабочих листах Excel

Макросы 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 и позволяя макросу работать, одновременно запрещая любые другие изменения.