Каждому пользователю свой лист/диапазон
Очень часто на своих тренингах и в форумах я слышу вопрос: как защитить доступ к книге так, чтобы для каждого пользователя был доступен только свой лист/листы? А другие ячейки или листы были недоступны для изменения или просмотра? Или скрыть отдельные столбцы с глаз пользователя? Часть подобного функционала предоставляется стандартными средствами Excel, а другая(например, доступность просмотра только конкретных листов) достигается только через макросы. В этой статье хочу привести несколько примеров реализации подобных разграничений прав между пользователями, их плюсы и минусы.
- Доступ пользователям только к определенным листам
- Доступ пользователю к определенным листам и возможность изменять только отдельные ячейки
- Доступ к определенным листам и скрытие указанных строк/столбцов
- Практический пример с использованием администратора
Для разграничения доступа к ячейкам на листе можно воспользоваться инструментом Разрешить изменение диапазонов
Это стандартный инструмент, для использования которого нет необходимости подключать что-то дополнительно и он относительно прост в использовании.
Данный инструмент позволяет назначить каждому отдельному диапазону ячеек свои пароли, диапазоны могут располагаться на разных листах книги или на одном листе:
Например, сотрудники коммерческого отдела в общем файле бюджета(картинка выше) должны иметь возможность заполнять только ячейки строк со статьями выручки (строки 8-11, 13-14), а производственный отдел строки 18-22, в которых расположены статьи по расходам производственного отдела. При этом сотрудники коммерческого отдела не должны иметь возможность изменять данные статей другого отдела – каждый только данные своих статей.
Для начала необходимо для сотрудников каждого отдела создать отдельные диапазоны, к которым они будут иметь доступ. Для этого переходим на вкладку Рецензирование
Нажимаем Создать
После нажатия Ок появится окно подтверждения пароля. Необходимо указать тот же пароль, что был указан ранее для данного диапазона.
Точно так же создаем второй диапазон – "производственный", но для него указываем другой пароль(например –
Здесь можно еще раз проверить все ли правильно указано, при необходимости изменить (так же изменить диапазоны можно в любое время, вызвав данное окно с вкладки Рецензирование
Теперь, чтобы такая защита сработала необходимо непосредственно защитить лист. Это можно сделать либо сразу из этого же окна, нажав кнопку Защитить лист, либо закрыв окно перейти на вкладку Рецензирование
В появившемся окне проставляем галочки для тех действий, которые мы хотим разрешить делать пользователю на защищенном листе без ввода пароля(например, на картинке выше помимо стандартного выделения ячеек разрешена вставка столбцов. Подробнее про защиту листов и ячеек можно прочитать в статье - Защита листов и ячеек в MS Excel). Указываем пароль (например
Что важно: не следует указывать здесь пароль, который совпадает хотя бы с одним из паролей для отдельных диапазонов. Думаю, понятно почему: чтобы защиту не могли снять те, кому этого не положено делать.
Теперь остается сообщить сотрудникам отделов их пароли: производственный -
При первой попытке изменить данные в ячейках
Если пользователю известен пароль для диапазона – его необходимо будет ввести лишь один раз. В дальнейшем для ввода данных в ячейки этого диапазона вводить пароль не придется до тех пор, пока файл не будет закрыт. После повторного открытия файла пароль необходимо будет указать заново.
Однако, если сотрудник другого отдела попытается изменить ячейки производственного отдела и пароль ему неизвестен – изменить данные этих ячеек не получится.
Также ни сотрудники коммерческого отдела, ни сотрудники производственного отдела не смогут изменить данные столбцов А и В(№ и наименование статьи), заголовки таблицы(строки с 1-ой по 7-ю) и строки с итоговыми формулами (12, 15 и т.д. – закрашенные зеленым). Они смогут изменять только те ячейки, которые перечислены в назначенных каждому отделу диапазонах. Внести данные в другие ячейки(не перечисленные в разрешенных диапазонах) можно будет исключительно сняв общий доступ с книги, а после этого защиту с листа –Рецензирование
Важно: приведенные ниже решения могут работать некорректно в книгах с общим доступом. А те решения, в которых устанавливается защита на листы вообще не будут работать, т.к. для книг с общим доступом невозможно изменять параметры защиты листов и книг.
Доступ пользователям только к определенным листам
Исходная задача: дать возможность пользователю видеть и работать только на определенных листах - тех, которые мы ему выделили. При этом он даже не подозревает, что есть другие листы. Как работает. Открываем файл - автоматом отображается лишь один лист "Main", доступный всем пользователям, жмем на кнопку, появляется форма:
В форме необходимо выбрать пользователя и указать пароль, соответствующий этому пользователю.
Важно: Пароли и список доступных листов можно редактировать на очень скрытом листе "Users". Для каждого пользователя можно указать несколько листов. Указывать имена листов необходимо в точности такие же, какие они на самом деле. Это значит, что и регистр букв и каждый пробел должен быть учтен. Для разделения записей с несколькими листами используется точка-с-запятой(Лист1;Лист2;Лист3).
На листе "Main" перечислены имена пользователей, пароли для них и доступные для просмотра листы. Данная информация указаны только для ознакомления и тестов. Менять данные для реальных задач необходимо на листе "Users".Важно: файл может работать нестабильно в книгах с общим доступом.Скачать пример 
Tips_Macro_Sheets_for_Users.xls (84,5 KiB, 10 865 скачиваний)Доступ пользователю к определенным листам и возможность изменять только отдельные ячейки
Помимо того, что можно ограничить пользователю свободу выбора листов, ему можно еще и ограничить диапазоны ячеек, которые ему разрешено изменять. Иначе говоря, человек сможет работать только на Лист1 и Лист2 и вносить изменения только в указанные для каждого из листов ячейки. Файл с примером работает так же, как и пример выше: открываем книгу - видим только один лист "Main", жмем кнопку. Появляется форма, выбираем пользователя. Появятся только разрешенные листы и на этих листах можно изменять только те ячейки, который мы разрешим в настройках. При этом диапазоны для изменения можно указать для каждого листа разные.Важно: Пароли, список доступных листов и диапазонов можно редактировать на очень скрытом листе "Users". Для этого его необходимо отобразить, как описано в статье: Как сделать лист очень скрытым.
Чтобы разрешить изменять диапазоны на Лист1 -А1:А10 иА15:А20 , а на Лист2 -В1:В10 иВ15:В20 , необходимо на листе "Users" указать листы:Лист1;Лист2 и диапазоны:A1:A10,A15:A20;B1:B10,B15:B20
На листе "Main" пароли и фамилии указаны только для ознакомления и тестов. Менять данные для реальных задач необходимо на листе "Users".
Пароль на листы указывается напрямую в коде. Для изменения пароля необходимо перейти в редактор VBA(Alt+F11), раскрыть папку Modules, выбрать там модуль sPublicVars и изменить значение 1234 в строке: Public Const sPWD As String = "1234":
Важно: защита диапазонов достигается за счет установки защиты листа. Поэтому файл не будет работать в книгах с общим доступом.Скачать пример Tips_Macro_Sheets_Rng_for_Users.xls (86,0 KiB, 4 975 скачиваний)Доступ к определенным листам и скрытие указанных строк/столбцов
И еще чуть-чуть испортим жизнь пользователю: каждому пользователю видны только свои листы и виден только свой диапазон на этом листе. Точнее - строка или столбец. Все так же, как и в файлах выше(Пароли, список доступных листов и диапазонов можно редактировать на очень скрытом листе "Users". Для этого его необходимо отобразить, как описано в статье: Как сделать лист очень скрытым).
На листе "Users " доступны следующие настройки: в самом правом столбце необходимо указать скрывать столбцы(C) или строки(R) указанного диапазона.
Например, указаны диапазоны на Лист1 -А1:А10 иА15:А20 , а на Лист2 -В1:В10 иВ15:В20 , а в правом столбце -R;C . Значит на Лист1 будут скрыты строки1:10 ,15:20 , а на Лист2 столбец В. Почему так заумно? Потому что нельзя скрыть только отдельные ячейки - можно скрыть лишь столбцы или строки полностью.
На листе "Main" пароли и фамилии указаны только для ознакомления и тестов. Менять данные для реальных задач необходимо на листе "Users".
Пароль на листы указывается напрямую в коде. Для изменения пароля необходимо перейти в редактор VBA(Alt+F11), раскрыть папку Modules, выбрать там модуль sPublicVars и изменить значение 1234 в строке: Public Const sPWD As String = "1234":
Важно: защита отображения скрытых строк и столбцов достигается за счет установки защиты листа. Поэтому файл не будет работать в книгах с общим доступом.Скачать пример Tips_Macro_Sheets_Hide_Rng_for_Users.xls (100,0 KiB, 4 659 скачиваний)Практический пример с использованием администратора
Все примеры выше имеют один маленький недостаток: при открытии файла виден один лист и надо жать на кнопку, чтобы выбрать пользователя. Это не всегда удобно. Плюс есть недостаток куда хуже: для изменения настроек всегда надо вручную отображать лист настроек, а может и другие листы. Поэтому ниже я приложил файл, форма в котором открывается сразу после открытия файла:
Если выбрать "Пользователь" -admin , указать "Пароль" -1 , то все листы файла будут отображены. Другим пользователям будут доступны только назначенные листы. Таким образом, пользователь, назначенный администратором сможет легко и удобно менять настройки и права доступа пользователей: добавлять и изменять пользователей, их пароли, листы для работы(они доступны на листеUsers , как и в файлах выше). После внесения изменений надо просто закрыть файл - он сохраняется автоматически, скрывая все лишние листы.
При этом если пользователя нет в списке или пароли ему неизвестны, то при нажатии кнопкиОтмена или закрытии формы крестиком файл так же закроется. Таким образом к файлу будет доступ только тем пользователям, которые перечислены в листе Users, что исключает доступ к файлу посторонних лиц.
Если макросы будут отключены, то пользователь увидит лишь один лист - с инструкцией о том, как включить макросы. Остальные листы будут недоступны.
В реальных условиях не лишним будет закрыть доступ к проекту VBA паролем: Как защитить проект VBA паролемВажно: файл может работать нестабильно в книгах с общим доступом.Скачать пример Tips_Macro_UsersRulesOnStart.xls (72,0 KiB, 6 475 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Дмитрий мне ваше решение с пользователями очень пригодилось. Спасибо большое. Единственная проблема нет совместного доступа, а это так необходимо. Может быть у Вас есть какие ни будь идеи? Очень нужно.
К сожалению вариант с общим доступом в такой интерпретации невозможен, т.к. нельзя изменять параметры защиты книги в общем доступе(ограничения, наложенные самим MS Excel)
А возможно ли определение пользователя, открывшего защищенную книгу и занесение его логина в определенную ячейку на каком-либо листе?
Возможно. Получить имя пользователя можно так:
Дальше сами.
прошу прощения - не корректно спосил
Интересует пользователь с листа Users
Здравствуйте, при знятии защиты с листа пароль с листа main не подходит
пример №2. Как разрешить доступ к диапазонам на всей книге, а не на определенных листах?
Ответ дан на форуме:
Загрузил файл, все работало, но сейчас что то сломал
Run-time errore 13, как быть
помогите пожалуйста
Выглядит так
Private Sub cmndbCancel_Click()
Unload Me
End Sub
Private Sub cmndbOK_Click()
Dim rFndRng As Range
Dim oSheet As Worksheet
Dim sUserRange As String, sUserSheet As String, sSheets, li As Long
Application.ScreenUpdating = 0
With ThisWorkbook.Sheets("Users")
If cmbUsers "" Then
Set rFndRng = .Columns(1).Find(what:=cmbUsers, lookat:=xlWhole)
If Not rFndRng Is Nothing Then
sUserSheet = .Cells(rFndRng.Row, 3)
sSheets = Split(sUserSheet, ";")
If Me.txtbKod = CStr(.Cells(rFndRng.Row, 2)) Then
For Each oSheet In ThisWorkbook.Sheets
If oSheet.Name "Main" Then oSheet.Visible = 2
Next
For Each oSheet In ThisWorkbook.Sheets
If oSheet.Name "Users" Then
For li = 0 To UBound(sSheets)
If oSheet.Name = sSheets(li) Then
Sheets(sSheets(li)).Visible = -1
End If
Next li
End If
Next
Else
MsgBox "Íåâåðíî óêàçàí êîä!", vbCritical, "Îøèáêà": Exit Sub
End If
End If
Else
MsgBox "Íåîáõîäèìî óêàçàòü ôàìèëèþ!", vbCritical, "Íåò äàííûõ": Exit Sub
End If
End With
bClose = False
Application.ScreenUpdating = 1
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim li As Long
With ThisWorkbook.Sheets("Users")
For li = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
cmbUsers.AddItem .Cells(li, 1)
Next li
End With
bClose = True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If bClose = True Then ThisWorkbook.Close True
End Sub
Здравствуйте!
Вопрос по поводу 3-го варианта: при выставлении ограничений видимости столбцов выдает ошибку Run-time error 1004. В столбцах присутствует множество примечаний. Подскажите как исправить ошибку, плз!? Может надо скрыть примечания и куда это прописать?
Спасибо!
Все получилось! Дмитрий спасибо большое за статью и за помощь по почте где указали поле user.
Вы лучший!
Здравствуйте! Хотела спросить на счет "ПРАКТИЧЕСКОГО ПРИМЕРА С ИСПОЛЬЗОВАНИЕМ АДМИНИСТРАТОРА". Обычный пользователь после ввода пароля может через вкладку visual basic просмотреть книги, выделив xlSheetVisible. Что можно сделать, чтобы только администратору был доступ к Visual Basic. Спасибо
Прочитайте эту статью:
там написано как защитить проект от просмотра.
Дмитрий, вопрос по одновременному использованию несколькими пользователями. Если файл кем то уже открыт и открывает другой пользователь, то при закрытии вторым- файл сохраняет изменения и сохраняется в другую папку( Мои документы). Можно ли дописать проверку- открыт ли кем то уже файл и если это так, то при закрытии вторым просто файл закрывать, без сохранения, чтобы не плодились копии в других папках?
Здесь, полагаю, может помочь один из кодов из этой статьи:
Вот не могу сообразить, как организовать эту проверку, ведь первоначальный макрос на открытие книги записан в Private Sub, а Check_Open_Book надо вставлять в стандартный модуль.