Отслеживание входа пользователей в книгу 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
'сохраняемся перед выходом
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
'сохраняемся перед выходом
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 'запись во вторую ячейку второй строки
MS, Libreoffice & Google docs
715 постов15K подписчиков
Правила сообщества
1. Не нарушать правила Пикабу
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.