Отслеживание входа пользователей в книгу Excel

Как понятно из заголовка, мы сделаем так, чтобы открытие рабочего файла на общем сетевом диске не осталось бесследным. Макрос будет фиксировать на отдельном (скрытом) листе имя пользователя, открывшего файл, а также дату-время открытия и закрытия файла.


Этап 1. Создаем "Лог"

Добавим в нашу книгу новый лист, куда будет записываться информация о всех пользователях и назовем его, например, Лог. На нем создадим простую шапку будущего журнала учета посетителей:

Отслеживание входа пользователей в книгу Excel Microsoft Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Этап 2. Макросы фиксации входа-выхода

Теперь добавим макросы для записи на лист Лог даты-времени и имен пользователей при открытии и закрытии книги. Для этого нужно открыть редактор Visual Basic с помощью сочетания Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) и найти в левом верхнем углу панель Project (если она не отображается, то включить ее можно сочетанием клавиш Ctrl+R):

Отслеживание входа пользователей в книгу Excel Microsoft Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Двойным щелчком откройте модуль ЭтаКнига (ThisWorkbook) и вставьте туда пару наших макросов для обработки событий открытия и закрытия книги:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим дату-время выхода из файла

If lastrow>1 Then Worksheets("Лог").Cells(lastrow, 3) = Now

'сохраняемся перед выходом

ActiveWorkbook.Save

End Sub

Private Sub Workbook_Open()

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим имя пользователя и дату-время входа в файл

Worksheets("Лог").Cells(lastrow + 1, 1) = Environ("USERNAME")

Worksheets("Лог").Cells(lastrow + 1, 2) = Now

End Sub

Попробуйте открыть-закрыть этот файл пару раз и убедитесь, что на лист Лог попадает ваше имя пользователя (логин входа в Windows) и дата-время:

Отслеживание входа пользователей в книгу Excel Microsoft Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Этап 3. Улучшаем надежность

Можно было бы скрыть лист Лог и на этом остановиться, но есть одно "но": если у пользователя, который открывает нашу книгу, макросы разрешены по умолчанию либо он сам их разрешает, нажав в окне предупреждения на кнопку Включить содержимое, то все в порядке:

Отслеживание входа пользователей в книгу Excel Microsoft Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Но что если пользователь не разрешит выполнение макросов или они отключены у него по умолчанию? Тогда наши макросы отслеживания выполняться не будут и фиксации имени и даты не произойдет :( Как же заставить пользователя разрешить использование макросов?

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

Отслеживание входа пользователей в книгу Excel Microsoft Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Суть в том, чтобы по умолчанию скрыть в книге все листы кроме этого, а рабочие листы с данными отображать с помощью специального макроса. Если пользователь не разрешил выполнение макросов, то он увидит в книге только один лист с предупреждением. Если же макросы разрешены, то наш макрос обработки события открытия книги скроет лист с предупреждением и отобразит листы с данными. Чтобы пользователь сам не отобразил их - используем суперскрытие вместо обычного скрытия листов (параметр xlSheetVeryHidden вместо обычного False).

Чтобы реализовать все описанное, слегка изменим наши процедуры в модуле ЭтаКнига (ThisWorkbook)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим дату-время выхода из файла

If lastrow > 1 Then Worksheets("Лог").Cells(lastrow, 3) = Now

'скрываем все листы, кроме листа ПРЕДУПРЕЖДЕНИЕ

Worksheets("Предупреждение").Visible = True

For Each sh In ActiveWorkbook.Worksheets

If sh.Name = "Предупреждение" Then

sh.Visible = True

Else

sh.Visible = xlSheetVeryHidden

End If

Next sh

'сохраняемся перед выходом

ActiveWorkbook.Save

End Sub


Private Sub Workbook_Open()

'ищем последнюю занятую строчку в логах

lastrow = Worksheets("Лог").Range("A60000").End(xlUp).Row

'заносим имя пользователя и дату-время входа в файл

Worksheets("Лог").Cells(lastrow + 1, 1) = Environ("USERNAME")

Worksheets("Лог").Cells(lastrow + 1, 2) = Now

'отображаем все листы

For Each sh In ActiveWorkbook.Worksheets

sh.Visible = True

Next sh

'скрываем листы ПРЕДУПРЕЖДЕНИЕ и ЛОГ

Worksheets("Предупреждение").Visible = xlSheetVeryHidden

Worksheets("Лог").Visible = xlSheetVeryHidden

End Sub

Чтобы просмотреть скрытый Лог откройте редактор VisualBasic (Alt+F11), выделите лист на панели Project и измените его видимость на панели Properties, используя свойство Visible:

Отслеживание входа пользователей в книгу Excel Microsoft Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Если пользователи настолько продвинутые, что знают про суперскрытые листы и могут их отобразить через редактор Visual Basic или нарушить работу наших макросов, то можно дополнительно поставить пароль на просмотр и изменение макросов. Для этого щелкните правой кнопкой мыши по имени файла в панели Project (строка VBAProject (blackbox.xls)), выберите команду VBA Project Properties и включите флажок Lock project for viewing и задайте пароль на вкладке Protection:

Отслеживание входа пользователей в книгу Excel Microsoft Excel, Vba, Макрос, Полезное, Контроль, На заметку, Длиннопост, Системное администрирование

Теперь точно никто не уйдет безнаказанным.


Интересные поправки в макрос из комментария источника:

Вместо поиска последней занятой строки в таких случаях я обычно вставляю новую строку сразу после "шапки". Все предыдущие строки сдвигаются вниз, конечно. В результате немного упрощается код, сортировка получается обратная, "новые сверху", что позволяет быстрее просматривать последние записи, не прокручивая лист. При открытии - вставляется строка, её номер всегда будет 2. При закрытии файла в строку 2 добавляется время. Переполнение этого стека маловероятно, но и проверку такого переполнения проводить не нужно, просто отрезать строки ниже запланированного количества записей, без проверки.

Private Sub Workbook_Open()

Worksheets("Реестр изменений").Rows("2:2").Insert Shift:=xlDown 'вставляем между строками 1 и 2 новую строку

Worksheets("Реестр изменений").Rows("501:501").Delete Shift:=xlUp 'удаляем строку 501 (реестр на 500 строк)

Worksheets("Реестр изменений").Cells(2, 1) = Environ("USERNAME") 'запись в первую ячейку второй строки

Worksheets("Реестр изменений").Cells(2, 2) = Now 'запись во вторую ячейку второй строки

MS, Libreoffice & Google docs

715 постов15K подписчиков

Добавить пост

Правила сообщества

1. Не нарушать правила Пикабу

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях


Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.

Вы смотрите срез комментариев. Показать все
11
Автор поста оценил этот комментарий
На планете Excel много полезных статей, не плохо бы указать ссылку на автора оригинальной статьи по заметнее, чем в середине текста.
раскрыть ветку (1)
9
Автор поста оценил этот комментарий

Там есть скриншот с именем автора, а ссылка указана в конце, я ни в коем случае не претендую на авторство статьи

Вы смотрите срез комментариев. Чтобы написать комментарий, перейдите к общему списку