Очень часто на своих тренингах и в форумах я слышу вопрос: как защитить доступ к книге так, чтобы для каждого пользователя был доступен только свой лист/листы? А другие ячейки или листы были недоступны для изменения или просмотра? Или скрыть отдельные столбцы с глаз пользователя? Часть подобного функционала предоставляется стандартными средствами Excel, а другая(например, доступность просмотра только конкретных листов) достигается только через макросы. В этой статье хочу привести несколько примеров реализации подобных разграничений прав между пользователями, их плюсы и минусы.
- Доступ пользователям только к определенным листам
- Доступ пользователю к определенным листам и возможность изменять только отдельные ячейки
- Доступ к определенным листам и скрытие указанных строк/столбцов
- Практический пример с использованием администратора
Разграничение доступа к ячейкам стандартными средствами
Для разграничения доступа к ячейкам на листе можно воспользоваться инструментом Разрешить изменение диапазонов (Allow Users to Edit Ranges) , расположенном на вкладке Рецензирование (Review) , группа Изменения (Changes) :
Как включить или отключить Учетную запись администратора в Windows 7
Это стандартный инструмент, для использования которого нет необходимости подключать что-то дополнительно и он относительно прост в использовании.
Данный инструмент позволяет назначить каждому отдельному диапазону ячеек свои пароли, диапазоны могут располагаться на разных листах книги или на одном листе: 
Например, сотрудники коммерческого отдела в общем файле бюджета(картинка выше) должны иметь возможность заполнять только ячейки строк со статьями выручки (строки 8-11, 13-14), а производственный отдел строки 18-22, в которых расположены статьи по расходам производственного отдела. При этом сотрудники коммерческого отдела не должны иметь возможность изменять данные статей другого отдела – каждый только данные своих статей.
Для начала необходимо для сотрудников каждого отдела создать отдельные диапазоны, к которым они будут иметь доступ. Для этого переходим на вкладку Рецензирование (Review) -группа Изменения (Changes) -Разрешить изменение диапазонов (Allow Users to Edit Ranges) . Появится диалоговое окно создания/изменения диапазонов: 
Нажимаем Создать (New) . Появится другое окно, в котором необходимо указать имя диапазона (Title) ( коммерческий ), доступные для изменения ячейки (Refers to cells) ( C8:N11;C13:N14 ) и вписать пароль (Range password) ( 1111 ):
После нажатия Ок появится окно подтверждения пароля. Необходимо указать тот же пароль, что был указан ранее для данного диапазона.
Примечание: если нажать на кнопку Разрешения (Permissions) , то можно установить доступ без пароля для конкретных групп пользователей, если группы настроены политикой доменной сети.
Точно так же создаем второй диапазон – «производственный», но для него указываем другой пароль(например – 2222 ). После этого у нас в главном окне управления диапазонами будет два диапазона:
Здесь можно еще раз проверить все ли правильно указано, при необходимости изменить (так же изменить диапазоны можно в любое время, вызвав данное окно с вкладки Рецензирование (Review) -Разрешить изменение диапазонов). После этого нажимаем Применить (Apply) .
Теперь, чтобы такая защита сработала необходимо непосредственно защитить лист. Это можно сделать либо сразу из этого же окна, нажав кнопку Защитить лист, либо закрыв окно перейти на вкладку Рецензирование (Review) и в группе Изменения (Changes) выбрать Защитить лист (Protect sheet) :
В появившемся окне проставляем галочки для тех действий, которые мы хотим разрешить делать пользователю на защищенном листе без ввода пароля(например, на картинке выше помимо стандартного выделения ячеек разрешена вставка столбцов. Подробнее про защиту листов и ячеек можно прочитать в статье — Защита листов и ячеек в MS Excel). Указываем пароль (например 3333 ), подтверждаем пароль в появившемся окне и нажимаем Ок. Лист защищен.
Что важно: не следует указывать здесь пароль, который совпадает хотя бы с одним из паролей для отдельных диапазонов. Думаю, понятно почему: чтобы защиту не могли снять те, кому этого не положено делать.
Теперь остается сообщить сотрудникам отделов их пароли: производственный — 2222 , коммерческий – 1111 .
При первой попытке изменить данные в ячейках C8:N11;C13:N14 — будет запрошен пароль на изменение ячеек созданного диапазона «коммерческий» ( 1111 ):
Если пользователю известен пароль для диапазона – его необходимо будет ввести лишь один раз. В дальнейшем для ввода данных в ячейки этого диапазона вводить пароль не придется до тех пор, пока файл не будет закрыт. После повторного открытия файла пароль необходимо будет указать заново.
Однако, если сотрудник другого отдела попытается изменить ячейки производственного отдела и пароль ему неизвестен – изменить данные этих ячеек не получится.
Также ни сотрудники коммерческого отдела, ни сотрудники производственного отдела не смогут изменить данные столбцов А и В(№ и наименование статьи), заголовки таблицы(строки с 1-ой по 7-ю) и строки с итоговыми формулами (12, 15 и т.д. – закрашенные зеленым). Они смогут изменять только те ячейки, которые перечислены в назначенных каждому отделу диапазонах. Внести данные в другие ячейки(не перечисленные в разрешенных диапазонах) можно будет исключительно сняв общий доступ с книги, а после этого защиту с листа –Рецензирование (Review) -группа Изменения (Changes) -Снять защиту листа (Unprotect sheet) . Но снять общую защиту сможет только тот, кто её создавал и кому известен «главный» пароль. Как правило это администратор или некий «смотрящий» файла и другие пользователи этот пароль не знают.
Плюс подобного метода в том, что такая защита может быть установлена для книги в общем доступе(подробнее про книги с общим доступом можно прочитать в статье — Ведение журнала сделанных в книге изменений).
Что необходимо учитывать для книг с общим доступом: создавать диапазоны для пользователей и устанавливать защиту на лист необходимо ДО назначения книге общего доступа, т.к. после того, как книге будет назначен общий доступ изменять параметры защиты листов и книги запрещено. При этом запрещены как установка защиты так и её снятие.
Минус данного метода в том, что нет дружественного интерфейса снятия защиты. Например, при попытке изменить какие-то ячейки одного из назначенных диапазонов нет никакой информации о том, что это за диапазон(коммерческий или производственный). Что в свою очередь может запутать пользователя. Так же данным методом невозможно скрыть листы, либо отдельные строки и столбцы. Можно лишь запретить изменение ячеек.
Разграничение прав доступа при помощи VBA
Самый большой минус всех методов ниже: они не будут работать при отключенных макросах (Что такое макрос и где его искать?). Во всех приложенных к статье файлах это предусмотрено и если макросы будут отключены, то пользователь увидит лист, предлагающий включить макросы. Подробнее см. в статье: Как запустить файл с включенными макросами?
Плюс подобного подхода — мы практически не ограничены в правилах: можем скрывать от отдельных пользователей любые листы, строки и столбцы, защищать отдельные ячейки и т.п. Я ниже приведу несколько вариантов реализации защиты кодами, а вам останется лишь выбрать тот, который больше подходит под задачу. Первые три больше демонстрационные, чтобы показать что можно сделать. А вот последний пример — Практический пример с использованием администратора — наиболее приближен к задачам, применяемым в работе и наиболее удобен для распространения среди пользователей.
Проект VBA во всех файлах открыт для просмотра и изменений . Однако перед распространением решений в реальности лучше его закрыть от просмотра и изменений — Как защитить проект VBA паролем.
Важно: приведенные ниже решения могут работать некорректно в книгах с общим доступом. А те решения, в которых устанавливается защита на листы вообще не будут работать, т.к. для книг с общим доступом невозможно изменять параметры защиты листов и книг.
Важно: файл может работать нестабильно в книгах с общим доступом.
Доступ пользователю к определенным листам и возможность изменять только отдельные ячейки
Помимо того, что можно ограничить пользователю свободу выбора листов, ему можно еще и ограничить диапазоны ячеек, которые ему разрешено изменять. Иначе говоря, человек сможет работать только на Лист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» :
Важно: защита диапазонов достигается за счет установки защиты листа. Поэтому файл не будет работать в книгах с общим доступом.
Доступ к определенным листам и скрытие указанных строк/столбцов
И еще чуть-чуть испортим жизнь пользователю: каждому пользователю видны только свои листы и виден только свой диапазон на этом листе. Точнее — строка или столбец. Все так же, как и в файлах выше(Пароли, список доступных листов и диапазонов можно редактировать на очень скрытом листе «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» :
Важно: защита отображения скрытых строк и столбцов достигается за счет установки защиты листа. Поэтому файл не будет работать в книгах с общим доступом.
Журналом документов закупки:
Списком внутренних складских документов:
Журналом заявок на расходование денежных средств:
Отчет по валовой прибыли также фильтруется по продажам текущего пользователя (несмотря на то, что отбор на форме не заполнен):
Вас может заинтересовать
- Как передать товар на комиссию в УТ 11
- Как запретить редактирование даты и номера документов
- Как настроить резервное копирование
Добавление прав доступа к чужим документам
Чаще всего доступ к чужим документам нужно запретить не всем пользователям, а только некоторым. Если хотите кому-то открыть такой доступ, то знайте, что новые ограничения не распространяются на:
- пользователей с полными правами,
- пользователей с ролью Доступ к чужим документам.
Покажу, как добавить нужным пользователям эту роль:
Сначала создаем профиль доступа, в нем выделяем нужную роль, потом для этого профиля добавляем группу доступа:
В группе доступа на вкладке Участники выбираем тех пользователей, которых новые ограничения коснуться не должны.
Для корректной работы продукта необходимо снять безопасный режим:
Дмитрий Медведков программист, консультант 1С
Работаю в 1С с 2012-го, специализируюсь на УТ 11
Имею 10 квалифиционных сертификатов 1C:Профессионал, 1С:Специалист и 1С:Специалист-консультант
Станислав 4 июня 2020 г.
Добрый день. Расширение не работает на журнал документов ПКО и РКО
Дмитрий Медведков, администратор 4 июня 2020 г.
Добрый вечер! уточните пожалуйста номер релиза вашей конфигурации.
Admin 16 июня 2020 г.
Не работает! Попробуйте подключится и проверить. Если не получится решить проблему, придётся возврат денежных средств попросить.
Дмитрий Медведков, администратор 16 июня 2020 г.
Добрый день. Скачайте программу AnyDesk и позвоните, я подключусь и посмотрю.
Чинков Владимир Алексеевич 17 июня 2020 г.
День добрый. Подскажите, будет ли ваша разработка работать на конфигурации УТ 11.3 (11.3.2.199) Обновлять ее до 11.4 пока нет возможности. Спасибо
Дмитрий Медведков, администратор 17 июня 2020 г.
Добрый день! нет, не будет. Релиз слишком старый.
Александр 5 июля 2020 г.
Добрый день. Для ЗУП 3.1 подойдет?
Дмитрий Медведков, администратор 6 июля 2020 г.
Добрый день. Нет. Обращайте пожалуйста внимание на список совместимых конфигураций.
Irina 24 августа 2020 г.
Добрый день, будут ли видны заказы клиента других пользователей в отчете «Поступление и отгрузка товаров (расшифровка)»?
Дмитрий Медведков, администратор 26 августа 2020 г.
Добрый день Ирина! Ограничение касается только журналов документов и отчета по валовой прибыли. В остальных местах ограничение работать не будет.
Ялымова Елена 24 января 2021 г.
Добрый день, 1С:УНФ подойдет?
Дмитрий Медведков, администратор 25 января 2021 г.
Добрый день! Нет, не подойдет
Золотов Андрей Владимирович 4 октября 2021 г.
Дмитрий, добрый день! У меня УТ 11.4.13.209, есть ряд расширений конфигурации сама конфигурация на поддержке. возможно будет вашу помощь при адаптации получить?
Дмитрий Медведков, администратор 4 октября 2021 г.
Добрый день! Да, думаю смогу вам помочь. Свяжитесь со мной по телефону или в скайпе для уточнения условий.
Яна Мокшина 7 декабря 2021 г.
Добрый день! На сделки распространяется действие расширения?
Дмитрий Медведков, администратор 7 декабря 2021 г.
Добрый день. Нет, т.к. сделка это не документ.
Wuw 2 февраля 2022 г.
Добрый день! Можно выбрать конкретные виды документов которые будут ограничиваться? Например заказы клиентов и РТУ можно смотреть других пользователей, а вот РКО и ПКО только свои.
Дмитрий Медведков, администратор 2 февраля 2022 г.
Добрый день! Нет, детализировать ограничение доступа по видам документов нельзя.
Maksim 29 сентября 2022 г.
Управление торговлей, редакция 11 (11.4.13.155) Расширения конфигурации: — Фильт документов по менеджеру (572.1.1.1) Почему в журнале документов Заказ клиента фильтр не сработал. Сам фильтр заблокирован, но стоит значение «все». Несколько раз переустанавливал расширение, но ошибка не уходит. При этом старые докумнеты других пользователей не видны, но новые отображаются (очень странно срабатывает) Что можно будет сделать?
Дмитрий Медведков, администратор 29 сентября 2022 г.
Добрый день. По всей видимости, проблема в кешированных настройках. Попробуйте в свойствах пользователя очистить сохраненные настройки формы списка заказов клиента. Если не сможете, могу подключиться к вам удаленно через AnyDesk в понедельник и помочь.
Maksim 2 октября 2022 г.
Да действительно, дело было в этом. Спасибо за быстрый ответ!
Источник: www.coderstar.ru