Excel позволяет защищать отдельные листы, как вы узнали из других выпусков ExcelTips. (вы выбираете Инструменты | Защита | Защитить лист.) Вы можете использовать этот подход для защиты отдельных листов независимо, используя разные http: // имя пользователя-пароль. .com / 14 / what-make-a-good-password [пароли].

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

Но что, если вы хотите полностью ограничить доступ к рабочим листам?

Что делать, если вы даже не хотите, чтобы неавторизованный пользователь видел другой лист? Эту потребность немного сложнее удовлетворить, но это можно сделать. Основной подход был бы следующим:

  1. Создайте книгу с тремя рабочими листами: один будет всегда открытым, один для пользователя 1 и третий для пользователя 2.

  2. Скройте рабочие листы для пользователя 1 и пользователя 2.

  3. Создайте форму, которая появляется при каждом открытии книги и запрашивает имя пользователя и пароль.

  4. Создайте код макроса, который разблокирует и отображает правильный рабочий лист на основе имени пользователя и пароля.

  5. Защитите всю книгу (Инструменты | Защита | Защитить книгу).

Шаги 1, 2 и 5 выполнить достаточно просто, и они были рассмотрены в других выпусках ExcelTips. Однако суть этого подхода — шаги 3 и 4. Вы можете создать пользовательскую форму, выполнив следующие шаги:

  1. Нажмите Alt + F11, чтобы открыть редактор VBA.

  2. В редакторе VBA выберите User Form из меню Insert. Отобразится новая пустая форма пользователя вместе с панелью инструментов формы.

  3. Используя элементы управления на панели инструментов формы, добавьте элемент управления TextBox, в котором пользователь будет вводить свое имя пользователя.

  4. Измените свойства элемента управления TextBox так, чтобы его имя было txtUser.

  5. Используя элементы управления на панели инструментов формы, добавьте элемент управления TextBox, в котором пользователь будет вводить свой пароль.

  6. Измените свойства элемента управления TextBox так, чтобы его имя было txtPass.

  7. Сразу под элементами управления TextBox добавьте элемент управления CommandButton.

  8. Измените свойства элемента управления CommandButton, чтобы его имя было btnOK, а заголовок — в порядке.

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

Dim bOK2Use As Boolean

Private 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(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then         bError = False         Select Case txtUser.Text             Case "user1"

sSName = "u1sheet"

If txtPass.Text <> "u1pass" Then bError = True             Case "user2"

sSName = "u2sheet"

If txtPass.Text <> "u2pass" Then bError = True             Case Else                 bError = True         End Select     End If     If bError Then         MsgBox "Invalid User Name or Password"

Else         'Set document property         bSetIt = False         For Each p In 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 If End Sub

Private Sub UserForm_Terminate()

If Not bOK2Use Then         ActiveWorkbook.Close (False)

End If End Sub

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

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

Второй макрос в этом коде (UserForm_Terminate) вступает в игру, если пользователь пытается просто закрыть вашу форму без ввода имени пользователя и пароля. В этом случае, если процесс авторизации ранее не был завершен, книга просто закрывается.

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim w As Worksheet     Dim bSaveIt As Boolean

bSaveIt = False     For Each w In Worksheets         If w.Visible Then             Select Case w.Name                 Case "u1sheet"

w.Protect ("u1pass")

w.Visible = False                     bSaveIt = True                 Case "u2sheet"

w.Protect ("u2pass")

w.Visible = False                     bSaveIt = True             End Select         End If     Next w     If bSaveIt Then         ActiveWorkbook.CustomDocumentProperties("auth").Delete         ActiveWorkbook.Save     End If End Sub

Private Sub Workbook_Open()

UserForm1.Show End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.Name <> "Main" Then         If Sh.Name <> ActiveWorkbook.CustomDocumentProperties("auth").Value Then             Sh.Visible = False             MsgBox "You don't have authorization to view that sheet!"

End If     End If End Sub

Когда пользователь решает закрыть книгу — работа завершена — соответствующие рабочие листы снова защищаются и скрываются. (Если вы измените имена и пароли пользовательских листов, вам также необходимо будет изменить их здесь в структуре Select Case.) Затем макрос удаляет соответствующее свойство документа и сохраняет книгу.

Еще один интересный макрос — процедура Workbook_SheetActivate.

Это включено в случае, когда один пользователь пытается использовать Format | Лист | Показать, чтобы показать рабочий лист другого пользователя. В этом случае авторизованное имя листа пользователя (сохраненное в переменной документа, когда пользователь был первоначально авторизован) сравнивается с отображаемым листом. Если он не совпадает, то пользователю не разрешено просматривать рабочий лист. Также обратите внимание, что эта процедура ссылается на рабочий лист под названием «Main». Этот рабочий лист — третий рабочий лист, упомянутый в начале этого совета.

Этот рабочий лист также отображается первым при открытии книги.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (1952 г.) применим к Microsoft Excel 97, 2000, 2002 и 2003.