Excel позволяет защищать отдельные рабочие листы, как вы узнали из других выпусков ExcelTips. (вы выбираете Инструменты | Защита | Защитить лист.) Вы можете использовать этот подход для защиты отдельные рабочие листы независимо друг от друга, используя разные пароли. Это означает, что один пользователь может вносить изменения в один рабочий лист, используя один пароль, а другой может использовать другой пароль для внесения изменений в другой рабочий лист.
Что делать, если вы хотите полностью ограничить доступ к рабочим листам , тем не мение? Что, если вы даже не хотите, чтобы неавторизованный пользователь видел другой лист? Устроить эту потребность немного сложнее, но это можно сделать. Базовый подход будет следующим:
- Создайте книгу с тремя рабочими листами: один всегда будет открытым, один для пользователя 1 и третий для пользователя 2.
- Скройте рабочие листы для пользователя 1 и пользователя 2.
- Создайте форму, которая появляется при каждом открытии книги и запрашивает имя пользователя и пароль.
- Создайте код макроса, который разблокирует и отображает правильный рабочий лист на основе имени пользователя и пароля.
- Защитите всю книгу (Инструменты | Защита | Защитить книгу).
Шаги 1, 2 и 5 выполнить достаточно просто, и они были рассмотрены в других выпусках ExcelTips . Однако суть этого подхода – шаги 3 и 4. Вы можете создать пользовательскую форму, выполнив следующие действия:
- Нажмите Alt + F11 для отображения редактора VBA.
- В редакторе VBA выберите «Форма пользователя» в меню «Вставка». Отобразится новая пустая форма пользователя вместе с панелью инструментов формы.
- Используя элементы управления на панели инструментов формы, добавьте элемент управления TextBox, в котором пользователь будет вводить свое имя пользователя.
- Измените свойства элемента управления TextBox так, чтобы его имя было txtUser.
- Используя элементы управления на панели инструментов формы, добавьте элемент управления TextBox, в который пользователь будет вводить свой пароль.
- Измените свойства элемента управления TextBox так, чтобы его имя было txtPass.
- Сразу под элементами управления TextBox добавьте элемент управления CommandButton.
- Измените элемент управления свойства для элемента управления CommandButton, поэтому его имя – btnOK, а заголовок – в порядке.
Создав пользовательскую форму, вы готовы связать код макроса с элементами управления, которые вы только что разместили. Убедитесь, что форма пользователя выбрана, и нажмите F7 , чтобы отобразить окно кода для формы. Окно может содержать строку или две автоматически сгенерированного кода. Замените это следующим кодом:
Dim bOK2Use As BooleanPrivate Sub btnOK_Click () Dim bError As Boolean Dim sSName As String Dim p As DocumentProperty Dim bSetIt As Boolean bOK2Use = False bError = True If Len ( .Text)> 0 И Len (txtPass.Text)> 0 Тогда bError = False Выберите регистр txtUser.Text Case «user1» sSName = «u1sheet» Если txtPass.Text «u1pass» Тогда bError = True Case «user2» sSName = «u2sheet» Если txtPass. Text «u2pass» Then bError = True Case Else bError = True End Select End If If bError Then MsgBox «Invalid User Name or Password» Else ‘Установить свойство документа bSetIt = False для каждого p в ActiveWorkbook.CustomDocumentProperties If p.Name = «auth» Then p.Value = sSName bSetIt = True Exit For End If Next p If Not bSetIt Then ActiveWorkbook.CustomDocumentProperties.Add _ Name: = «auth», LinkToContent: = False, _ Type: = msoPropertyTypeString, Value: = sSName End If Sheets (sSName) .Visible = True Sheets (sSName) .Unprotect (txtPass.Text) Sheets (sSName) .Activate bOK2Use = True Unload UserForm1 End IfEnd SubPrivate Sub UserForm_Terminate () If Notlose (Falork2Use Then ActiveWorks) IfEnd Sub
Приведенный выше код выполняет несколько функций. Обратите внимание, что есть две процедуры: более длинная, которая запускается, когда пользователь нажимает кнопку OK в форме, и другая, которая запускается, когда форма закрывается. Когда пользователь нажимает кнопку ОК, процедура проверяет правильность комбинации имени пользователя и пароля. Если это не так, пользователь получает уведомление. Если это так, то авторизованное имя листа сохраняется в переменной документа, и соответствующий лист отображается без защиты.
Если вы хотите изменить допустимые имена пользователей, имена листов и пароли, вы можно сделать это, внося желаемые изменения в структуру Select Case в верхней части этого кода макроса.
Второй макрос в этом коде (UserForm_Terminate) вступает в игру, если пользователь пытается просто отклонить ваш форму без ввода имени пользователя и пароля. В этом случае, если процесс авторизации ранее не был завершен, книга просто закрывается.
В дополнение к приведенному выше коду вам также потребуется добавить следующие макросы в саму книгу. Они открывают форму пользователя при открытии книги и защищают рабочий лист при закрытии книги.
Private Sub Workbook_BeforeClose (Cancel As Boolean) Dim w As Worksheet Dim bSaveIt As Boolean bSaveIt = False For Каждый w в листах, если w.Visible, то выберите вариант w.Name Case «u1sheet» w.Protect («u1pass») w.Visible = False bSaveIt = True Случай «u2sheet» w.Protect («u2pass») w.Visible = False bSaveIt = True End Выберите End If Next w If bSaveIt Then ActiveWorkbook.CustomDocumentProperties («auth»). Удалить ActiveWorkbook.Save End IfEnd SubPrivate Sub Workbook_Open () UserForm1.ShowEnd SubPrivate Sub Workbook_SheetActivate (ByVal Sh As Object) If Sh.Name > «Main» Then If Sh.Name ActiveWorkbook.CustomDocumentProperties («auth»). Value Then Sh.Visible = False MsgBox «У вас нет авторизации для просмотра этого листа!» End If End IfEnd Sub
Когда пользователь решает закрыть книгу – они сделали свою работу – соответствующие рабочие листы снова защищены и скрыты. (Если вы измените имена пользовательских листов и пароли, вам также необходимо будет изменить их в структуре Select Case здесь.) Затем макрос удаляет соответствующее свойство документа и сохраняет книгу.
Другой интересным макросом здесь является процедура Workbook_SheetActivate. Это включено в случае, когда один пользователь пытается использовать Format | Лист | Показать, чтобы показать рабочий лист другого пользователя. В этом случае авторизованное имя листа пользователя (сохраненное в переменной документа при первоначальной авторизации пользователя) сравнивается с отображаемым листом. Если он не совпадает, то пользователю не разрешено просматривать рабочий лист. Также обратите внимание, что эта процедура ссылается на рабочий лист под названием «Main». Этот рабочий лист – третий рабочий лист, упомянутый в начале этого совета. Этот рабочий лист также отображается первым при открытии книги.
Источник: