Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Каждому пользователю свой лист/диапазон

Не раз и не два я сталкивался с вопросами: как защитить доступ к книге так, чтобы для каждого пользователя был доступен только свой лист/листы, а доступ к остальным листам был бы невозможен? Или скрыть отдельные столбцы с глаз пользователя? Сделать такое можно только через макросы. В этой статье хочу привести несколько примеров реализации подобных разграничений прав между пользователями.

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



 
Доступ пользователям только к определенным листам
Исходная задача: дать возможность пользователю видеть и работать только на определенных листах - тех, которые мы ему выделили. При этом он даже не подозревает, что есть другие листы. Как работает. Открываем файл - автоматом отображается лишь один лист "Main", доступный всем пользователям, жмем на кнопку, появляется форма:

В форме необходимо выбрать пользователя и указать пароль, соответствующий этому пользователю. Важно: Пароли и список доступных листов можно редактировать на очень скрытом листе "Users". Для каждого пользователя можно указать несколько листов. Указывать имена листов необходимо в точности такие же, какие они на самом деле. Это значит, что и регистр букв и каждый пробел должен быть учтен. Для разделения записей с несколькими листами используется точка-с-запятой(Лист1;Лист2;Лист3).
На листе "Main" пароли и фамилии указаны только для ознакомления и тестов. Менять данные для реальных задач необходимо на листе "Users".
Скачать пример

  Tips_Macro_Sheets_for_Users.xls (71,0 KiB, 3 630 скачиваний)



 
Доступ пользователю к определенным листам и возможность изменять только отдельные ячейки
Помимо того, что можно ограничить пользователю свободу выбора листов, ему можно еще и ограничить диапазоны ячеек, которые ему разрешено изменять. Иначе говоря, человек сможет работать только на Лист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".
Скачать пример

  Tips_Macro_Sheets_Rng_for_Users.xls (77,5 KiB, 2 040 скачиваний)



 
Доступ к определенным листам и скрытие указанных строк/столбцов
И еще чуть-чуть испортим жизнь пользователю: каждому пользователю видны только свои листы и виден только свой диапазон на этом листе. Точнее - строка или столбец. Все так же, как и в файлах выше(Пароли, список доступных листов и диапазонов можно редактировать на очень скрытом листе "Users". Для этого его необходимо отобразить, как описано в статье: Как сделать лист очень скрытым). Только на листе "Users" добавилось настроек: в самом правом столбце необходимо указать скрывать столбцы(C) или строки(R) указанного диапазона. Например, указаны диапазоны на Лист1 - А1:А10 и А15:А20, а на Лист2 - В1:В10 и В15:В20, а в правом столбце - R;C. Значит на Лист1 будут скрыты строки 1:10, 15:20, а на Лист2 столбец В. Почему так заумно? Потому что нельзя скрыть только отдельные ячейки - можно скрыть лишь столбцы или строки полностью.
На листе "Main" пароли и фамилии указаны только для ознакомления и тестов. Менять данные для реальных задач необходимо на листе "Users".
Скачать пример

  Tips_Macro_Sheets_Hide_Rng_for_Users.xls (79,0 KiB, 2 157 скачиваний)



 
Практический пример с использованием администратора
Все примеры выше имеют один маленький недостаток: при открытии файла виден один лист и надо жать на кнопку, чтобы выбрать пользователя. Это не всегда удобно. Плюс есть недостаток куда хуже: для изменения настроек всегда надо вручную отображать лист настроек, а может и другие листы. Поэтому ниже я приложил файл, форма в котором открывается сразу после открытия файла. Если выбрать "Пользователь" - admin, указать "Пароль" - 1, то все листы файла будут отображены. Таким образом, пользователь, назначенный администратором сможет легко и удобно менять настройки: добавлять и изменять пользователей, их пароли, листы и диапазоны для изменения. После внесения изменений надо просто закрыть файл - он сохраняется автоматически, скрывая все лишние листы.
Скачать пример

  Tips_Macro_UsersRulesOnStart.xls (72,0 KiB, 1 306 скачиваний)


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 76 коммент.
  1. Владимир:

    Дмитрий здравствуйте.
    Спасибо за примеры. Очень полезным для меня оказался 1й пример с общим листом и возможностью далее выбирать пользователя.
    Но вот как сделать так, что бы на main лист переносилась информация со всех листов. Тобишь, нужно что бы все пользователи могли видеть информацию со всех листов, а уже используя кнопку, определёные пользователи вносят инфу согласно прописаным доступам. (Иванов лист1, Петров лист2, и т.д.)

    • Владимир, для этого надо писать отдельный код. Можете адаптировать под себя код из этой статьи: Как собрать данные с нескольких листов или книг?

      • Владимир:

        Отлично, примерно то что мне нужно, но вот как убрать пустые ячейки.
        так как с нескольких документов он добавляет и заполненные ячейки и пусты.
        Получается между перенесённой информацией с нескольких листов, много пустых ячеек и нужно листать в низ, что бы увидеть информацию с другого файла.
        Не удобно, когда 10 файлов

        • Могу посоветовать Вам только почитать комментарии на странице с кодом - вопрос уже поднимался, я давал когда-то на него ответ. Надо определять последнюю заполненную ячейку по конкретному столбцу.

          • Владимир:

            Дмитрий, ещё такой вопрос: как сделать так, что бы данные переносились с теми же форматированиями ячеек.
            Ячейки документа отформатированы, под перенос по словам, а при использовании макроса, в новый лист переносятся только значения, без формата ячеек, соответственно весь текс просто в одну строку уходит.

  2. Владимир, может будете комментарии создавать для тех статей, в которых что-то непонятно? Я тогда хотя бы ответить смогу. Вот сейчас как понять про какое форматирование речь, если комментарий Вы создали в теме про разграничение диапазонов и листов пользователям и никакое форматирование здесь вообще не затрагивается? Или Вы решили здесь ветку своих личных вопросов создать? :)

  3. Гузалия:

    Есть необходимость реализации подобной задачи.
    Не удается разобраться в коде.

    Есть рабочий файл, который заполняет ответственный человек в организации.
    Он должен видеть 4 листа. Менеджер: 2 листа. А есть учредители/руководители. Они должны видеть эти 4 и еще 7 листов. Причем количество пользователей и листов может изменяться, как и их названия.
    Можно сделать три роли: Управляющий, Учредитель.
    Файл расположен на Яндекс.Диске, то есть к нему есть доступ к разных людей.

    При необходимости могу файл скинуть.

    Как сделать так, чтобы

    Ранее с макросами не работала.
    Вот этот код то работает, то не работает.
    Private Sub Workbook_Open()
    If Application.UserName = "Guzalia Nurieva" Then
    Worksheets("Статистика").Visible = True
    Worksheets("Справочник_рук").Visible = True
    Worksheets("ДиР пансионата").Visible = True
    Worksheets("Учет ДиР_общее").Visible = True
    Worksheets("ДиР КЦ").Visible = True
    Worksheets("ДиР КД и КЦ").Visible = True

    Else:
    Worksheets("Статистика").Visible = xlVeryHidden
    Worksheets("Справочник_рук").Visible = xlVeryHidden
    Worksheets("ДиР пансионата").Visible = xlVeryHidden
    Worksheets("Учет ДиР_общее").Visible = xlVeryHidden
    Worksheets("ДиР КЦ").Visible = xlVeryHidden
    Worksheets("ДиР КД и КЦ").Visible = xlVeryHidden
    End If

    End Sub

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти