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

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

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

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

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

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

Двойным щелчком откройте модуль ЭтаКнига (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) и дата-время:

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

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

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

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

Суть в том, чтобы по умолчанию скрыть в книге все листы кроме этого, а рабочие листы с данными отображать с помощью специального макроса. Если пользователь не разрешил выполнение макросов, то он увидит в книге только один лист с предупреждением. Если же макросы разрешены, то наш макрос обработки события открытия книги скроет лист с предупреждением и отобразит листы с данными. Чтобы пользователь сам не отобразил их - используем суперскрытие вместо обычного скрытия листов (параметр 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:

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

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

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

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