Авторизация в Excel на VBA
Авторизация в Excel: основной алгоритм работы
Для начала, необходимо разработать алгоритм, по которому мы будем работать, и выглядеть он будет примерно так:
Авторизация в Excel: макет документа
Наш рабочий документ будет состоять из четырех листов:
- Лист с приветствием — единственный лист, который будет отображаться всем пользователям до авторизации
- Лист с дашбордом (визуализированным отчетом) — графики, диаграммы/гистограммы — изначально со свойством VeryHidden*
- Лист с данными — источник расчетов для дашборда — изначально со свойством VeryHidden
- Служебный/технический лист — для хранения логинов, паролей и служебной и вспомогательной информации — также, со свойством VeryHidden
*VeryHidden— свойство листа, при котором сам лист скрыт и включить его отображение можно только через использование режима разработчика. Для выбора данного свойства, необходимо в Excel зайти в меню «Разработчик» — «Visual Basic». Далее, необходимо выбрать нужный нам лист и в его свойствах («Properties») найти свойство Visible и установить в «2 — xlSheetVeryHidden».
После этого лист станет «очень скрытым». Отменить свойство VeryHidden можно точно так же через режим разработчика, либо используя необходимый нам макрос, о котором я напишу чуть дальше.
Авторизация в Excel: группы доступа
После создания макета документа, нам необходимо разработать несколько групп доступа и распределить и ограничить для них права. Предположим, что таких групп будет три:
- Администраторы (Admin): доступны все листы , в том числе служебный, а также отсутствуют какие-либо ограничения.
- Руководители и ответственные за данные (Head): доступны 2 рабочих листа + стоит пароль на изменение структуры книги.
- Рядовые сотрудники компании (Worker): доступен только 1 рабочий лист с дашбордом + стоит пароль на изменение структуры книги.
Авторизация в Excel: разработка макета формы
С этим пунктом не должно возникнуть никаких проблем. Элементов на форме авторизации должно быть не так уж и много:
- Поле ввода для логина
- Поле ввода для пароля
- Кнопка «Авторизация» (проверка логина и пароля)
- Различные подписи на форме
- По желанию: кнопка закрытия формы, кнопка восстановления пароля, логотипы, справка и все, что душе угодно
Для тех, кто не знает, как создавать формы в VBA: нужно зайти в меню «Разработчик» — «Visual Basic», нажать правой кнопкой по нашему проекту (VBAProject Название_Файла.xlsx), затем Insert — UserForm. Создается форма с названием UserForm1, на которой мы и будем размещать все наши объекты с помощью меню «ToolBox».
Если быстро набросать элементы, которые мы перечислили, должно получиться что-то вроде этого:
После добавления элементов, поменял их стандартные названия:
«UserForm1» переименовал в «Authorization».
«TextBox1» переименовал в «TextBox_Login».
«TextBox2» переименовал в «TextBox_Pass».
Также, в свойствах «TextBox_Pass», мы находим свойство «PasswordChar» и вводим любой символ, который мы хотим видеть вместо вводимых символов пароля — чаще всего используется символ «*».
Ничего лишнего, пока что все просто. Переходим далее.
Подготовка служебного листа
Итак, для начала, содержимое служебного листа будет выглядеть вот так:
Авторизация в Excel: особенности и написание программного кода на VBA
Данные пункты тесно взаимосвязаны между собой, поэтому я их объединил в один большой. Здесь мы будем писать программный код для всего нашего файла: как для формы, так и для некоторых событий.
Для начала, мы напишем код, который будет отображать различные листы в зависимости от роли пользователя после авторизации. Макрос user_group мы делаем приватным и вписывать его будем не в отдельный модуль, а в нашу готовую форму Authorization. Аргументом для макроса является переменная X, которая будет содержать название группы доступа в виде текстовой строки String:
Хочу обратить внимание на то, что у нашего файла будет стоять «защита структуры книги» с паролем «112», т.е. пользователь не сможет создавать, удалять и переименовывать листы нашего документа. И, чтобы изменить видимость листов, необходимо сначала снять эту защиту программно, а затем, в зависимости от роли пользователя, поставить обратно, что мы и делаем в нашем коде.
Далее, код для кнопки «Авторизация». При нажатии на данную кнопку, запускается несколько проверок:
В макросе CommandButton1_Click используется функция GetHash (строка 15), которая преобразует входящие данные в хеш. Данная функция взята с сайта ExcelVba.ru, за что выражаю им огромную благодарность. Код необходимо вставить в главный модуль книги:
Основной код написан, теперь переходим к более мелким.
Макрос, который запускает форму авторизации (можно установить на кнопку на главном листе «Main»):
Также, макрос можно использовать при событии «Open» — при открытии книги:
Стоит отметить, что у большинства пользователей запуск макросов по умолчанию отключен, именно поэтому на главном листе Main необходимо сделать что-то вроде «памятки» со справочной информацией о том, как активировать работу макросов.
Макрос для закрытия книги.
При закрытии книги, нам нужно снять защиту со структуры книги, затем скрыть все листы (кроме листа «Main»), а затем снова вернуть защиту структуры книги с паролем «112» (можно установить на какую-либо кнопку):
Да, данный макрос можно использовать при событии «BeforeClose» — перед закрытием книги. Однако, может возникнуть несколько проблем:
- Если пользователь сохраняется вручную до закрытия документа, закрывает документ, а затем при автоматическом запросе о сохранении нажимает «не сохранять». В таком случае, при новом запуске документа, скрытые листы будут видны всем. Да, вероятность такого события крайне мала, но она все-таки есть.
- Если добавим принудительное сохранение после скрытия всех листов, перед окончательным закрытием файла (как на примере ниже). В таком случае, если пользователь очень сильно ошибется с данными в файле и нажмет на крестик, чтобы начать все с нуля — файл все равно сохранится и восстановить исходные данные не получится.
Сохраняем файл в формате «.xlsb» или «.xlsm» и все, наша «Авторизация на VBA» готова. Помимо этого, можно защитить проект еще сильнее — о способах защиты написано моей отдельной статье.
Также, по желанию, мы можем сделать отдельную форму для администраторов, для добавления новых пользователей на лист «Settings», либо форму для восстановления пароля по какому-либо ключевому слову, но в данном примере такие функции мы реализовывать не будем.
[expert_bq id=»1570″]Все, что осталось сделать это вставить в местах, в которых необходимо выводить данные, элементы Текстовое поле , которые доступны на только что добавленной панели инструментов. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Экспортировать макросы и модули нельзя. При экспорте формы, отчета или таблицы с подчиненными формами, отчетами или таблицами экспортируется только главная форма, отчет или таблица. Все подчиненные формы, отчеты и таблицы следует экспортировать в Excel отдельно.
Работа с базами данных в Visual Basic
- Администраторы (Admin): доступны все листы , в том числе служебный, а также отсутствуют какие-либо ограничения.
- Руководители и ответственные за данные (Head): доступны 2 рабочих листа + стоит пароль на изменение структуры книги.
- Рядовые сотрудники компании (Worker): доступен только 1 рабочий лист с дашбордом + стоит пароль на изменение структуры книги.
В окне Properties есть две вкладки. На вкладке Alphabetic свойства выбранного объекта отображаются в алфавитном порядке. На вкладке Categorized эти свойства сгруппированы по категориям. Обе вкладки отображают одни и те же свойства.
