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

Представьте себе книгу Excel с очень важными для вас данными, сохраненную на общем сетевом диске компании, куда имеет доступ куча народу. Одним совсем не прекрасным утром вы открываете этот файл и обнаруживаете, что внутри кто-то поиграл в аль-каиду: формулы поломаны, данные стерты, дизайн нарушен. Поскольку вы опытный пользователь, то у вас, конечно же, была резервная копия этого важного документа (была же, правда?) и данные вы восстановите, но чисто из спортивного интереса хотелось бы все же узнать - КТО ЭТО СДЕЛАЛ?!

Давайте попробуем решить эту задачу. Итак, нам нужно:

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

Поехали...

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

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

blackbox1.png

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

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

blackbox2.png

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

blackbox3.png

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

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

blackbox4.png

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

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

blackbox5.png

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

blackbox7.png

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

blackbox8.png

Теперь точно никто не уйдет безнаказанным. Большой Брат следит за тобой! ;)

Ссылки по теме



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

Переполнение этого стека маловероятно, но и проверку такого переполнения проводить не нужно, просто отрезать строки ниже запланированного количества записей, без проверки.
07.05.2014 12:38:58
Очень дельное уточнение, спасибо!
Елена, добрый день! можете написать код, пожалуйста?
13.05.2014 17:41:02
Добрый день, Николай, вместе с временем выхода происходит сохранение всей книги , а это не всегда нужно, чтобы поправить чтобы сохранялся только лист "Лог", а  про другие изменения спрашивал как раньше?
14.05.2014 09:43:28
Как сохранить лист, но не сохранять книгу? Невозможно, к сожалению :(
Единственный вариант, который вижу с ходу - вынести ЛОГ в другой файл.
14.05.2014 10:04:12
оставлю только workbook_open и буду фиксировать только тех кто сохраняет книгу
20.05.2014 16:00:48
Добрый день!
Я то же сталкивался с проблемой мониторинга изменений файла несколькмими пользователями.
Для себя решил эту проблему через организацию совместного достапа к файлу (Рецензирование --> Достук к книге).
"Плюсы":
- проще в настройке;
- не требует написания макросов;
- отслеживает изменение данных (кто и что поменял).
"Минусы":
- файл очень быстро увеличивает свой размер;
- "не сильно умным" все равно ничего не докажешь.
04.07.2014 10:01:53
Скажите, а как правильно сохранить этот макрос в VBA?Сделав все это, у меня нет инфы по пользователю и дате входа, появляется разово только дата и время выхода!
03.08.2014 15:24:37
Специально сохранять макросы не нужно, но саму книгу нужно сохранить в формате XLSM, т.е. Файл - Сохранить как - Тип файла = Книга Excel с поддержкой макросов.
21.12.2016 08:49:14
Добрый день, Николай!
у меня вопрос, можна делать лист Лог "visible"  но запретит изменения для ползователей. Я хочу ползаватели знали что идет запис[IMG]
Спасибо за ране [IMG]
09.07.2014 12:44:32
Друзья, я еще никогда не работала с макросами, поэтому для вас мой вопрос прозвучит глупо, наверное.
Тем не менее, как вынести лог в другую книгу?
Мне не нужно сохранять общий файл как "лист XL с поддержкой макросов", как сделать так, чтобы все эти записи велись в другом файле?
Заранее большое спасибо!
(Скрытый комментарий)
03.08.2014 15:27:53
Честное слово, не "крал" - использую этот трюк в своих проектах уже лет пять, наверное :)
А про защиту структуры книги - это вы очень правильно уточнили. Лучше и здесь пароль поставить, а то особо одаренные залезут, как пить дать.
26.09.2014 12:30:21
Добрый день

если единственная цель это:
чисто из спортивного интереса хотелось бы все же узнать - КТО ЭТО СДЕЛАЛ?!
то, можно вместо Workbook_BeforeClose использовать Workbook_BeforeSave. Будет видно не только, кто нагадил, а ещё и сколько раз :)

Из минусов здесь - не будет видно кто просто просматривал книгу, но цель-то у нас см. выше :)

И ещё, полезно в конце Workbook_Open использовать
ActiveWorkbook.Saved = True

 Что бы не было лишнего приглашения сохранить книгу при выходе из неё.
27.09.2014 09:21:04
Согласен :)
Добрый день, Николай! Я только-только начинаю постигать расширенные возможности Excel. Так что заранее простите за возможно элементарные вопросы.

У меня установлен Excel 2003. Я пошагово выполнила все необходимые действия. Скопировала макрос с экрана и вставила в "ЭтаКнига" одной строкой. В результате при закрытии файла система выдает ошибку "expected end sub". Что я сделала не правильно?

Спасибо.
24.02.2015 23:37:35
После любого "Sub", даже "Private", должен быть "End Sub". Об этом система и говорит.
27.07.2015 15:55:18
и что куда добавить?? такая же беда-пробую, ничего не выходит и пишет сообщ. "expected end sub"
31.07.2015 06:11:29
Вы сколько строк-то вставили? (Не одну ли?) А лучше полностью напишите, что добавляли.
08.04.2015 20:23:49
Здравствуйте, Николай,
Подскажите, пожалуйста, как можно сделать так, чтобы при открытии книги не скрывались рабочие листы и появлялся лист "Предупреждение", а невозможно было внести изменения, пока макросы не включены, а при попытке внесения изменений в ячейки книги выводилось окно с сообщением "Для корректной работы файла нажмите "Включить содержимое" и здесь, желательно, изображение панели с кнопкой, которую нужно нажать" и так пока пользователь не включит макросы.
Спасибо!
09.04.2015 09:08:31
Такое окно с сообщением на защищенных листах не вывести никак - оно в Excel со стандартным текстом только. Именно поэтому в статье и предложен такой вариант.
09.04.2015 18:45:30
Жалко очень. А так, все очень удобно вы придумали. Спасибо.
12.04.2015 13:54:58
Николай, добрый день.
Зацените развитие идеи, которую вы подняли в этой статье.
K M
18.04.2015 20:40:53
"Ниачем"
Где исходники?
я бы не стал запускать Это на своем компьютере, не известно что еще делает этот макрос
18.04.2015 22:26:48
Выглядит здорово :)
Сейчас качну посмотреть как устроено.
18.04.2015 22:53:23
Кстати, функции шифрования позаимствовал у вас, правда их трудно узнать, поэтому не стал уж давать ссылки.
23.07.2015 20:57:48
Зарегистрировался, пишут, что регистрация прошла, но требуют еще какие-то данные. Прошу Вашей помощи...
С Вашего адреса проверка нормально идет на мой...(если всё, кроме адреса "Кому", оставляю без изменений)
С уважением М. Ю. В.
23.07.2015 21:19:13
Юрий, напишите мне напрямую denbat75@gmail.com
18.04.2015 21:30:33
Странно читать "ниачем" от 45-летнего мужчины :)
Пароль "1945-2015"
30.04.2015 15:54:25
Очень полезная штука.
28.11.2015 14:10:33
Спасибо за полезный материал.
Прошу подсказать как вместо Workbook_BeforeClose использовать Workbook_BeforeSave

Пишет следующее "Procedure declaration does not match description of event or procedure having the same name"
28.11.2015 15:43:42
Добрый день! Подскажите, как посмотреть реализацию такого варианта: http://www.planetaexcel.ru/techniques/5/196/#3236
30.11.2015 18:09:44
Если вы о комментарии "Елена Кузьмина(24.04.2014 08:42:48)" то возможно такой вариант подойдет:

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 'запись во вторую ячейку второй строки
01.12.2015 16:31:37
Спасибо большое! Все работает, как часы!!!
30.11.2015 18:45:05
Добрый день.
Вопрос к мастерам о развитии темы http://planetaexcel.ru/techniques/5/196/#4947
Если совместить предложенные варианты в одном файле возникает ошибка.
This command will stop the debbugg   и  Compile error: Variable not defined
Суть идеи сохранить не только реестр изменений ячейки, а параллельно вести журнал в котором при выходе и сохранении  будет записываться:
- сначала срока со старыми значениями ячеек,
- строка с новыми значениями ячеек

Пример прилагаю https://drive.google.com/file/d/0B0OCdyjFeVXOem4tRFJGSW1FdGs/view?usp=sharing
07.03.2016 20:36:30
а если файл находится на серваке? один открыл - попал в лог, а второй вынужден открыть "только для чтения" - тоже будет в логе?
10.06.2016 22:35:23
Сделал вариант сессионой записи работы в файлах в отдельный лог файл. Поддерживает много пользователей, много файлов, режими как чтения так и редактирования, обрывает сессии при неактивности и т.п.
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=78636&TITLE_SEO=78636-makros-dlya-uchyeta-vremeni-raboty-v-fayle-excel&MID=658875#message658875
21.12.2016 08:47:57
Добрый день, Друзя!
Помогите! у меня вопрос, можна делать лист Лог "visible"  но запретит изменения для ползователей. Я хочу ползаватели знали что идет запис:D  
Спасибо за ране :)
16.06.2017 23:44:28
На основе этой темы придумал как можно избежать следующих проблем:
1. отсутствие окна при выходе
2. сохранение файла при выходе (иногда надо выйти без сохранения)


http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=77713&TITLE_SEO=77713-kak-vypolnit-otmenuundo-makrosom&MID=766429#message766429

только я новичок VBA и прошу помощи в написании небольшой части кода)


И тот же вопрос, что и выше: можно лог сделать видимым, но нередактируемый для пользователей, но макрос туда вносит даты?
26.09.2017 08:18:36
сам я зеленый в VBA, использую готовые решения, найденные в сети, которые мне подходят.
попутался соединить это макрос с другим, который автоматически закрывает книгу через определенное время, но получаю ошибку.

вот тот макрос, который просто вписал после макроса, описанного в данной теме:

 Option Explicit

Dim DateTime As Date

Private Sub Workbook_Open()
    DateTime = Now + #12:02:00 AM#
    Application.OnTime DateTime, "TimeOut"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnTime DateTime, "TimeOut", , False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Workbook_BeforeClose False
    Workbook_Open
End Sub
Что нужно подправить, чтобы работало?
20.12.2017 07:00:31
но вы ведь сами говорили что есть возможность брать данные из закрытой книги, так ведь можно написать макрос, который бы отслеживал закрытую книгу, и сделать то что вы сделали, и тогда не будут танцы с бубнами с разрешением макросов. и файл можно запрятать куда хотите, и никто не узнает, и т.д. возможно ли это? и можно написать отдельный файл который будет записывать любой файл, который пользователь может указать и он с этого файла будет все отслеживать, а если по сетке, то вообще атас, начальник не вставая со стола находясь в другом этаже, (если онлайн доступ) то хоть на Америке может посмотреть кто заходил, работают ли вообще с файлом и т.д.) возможно ли это? (получается беспалевно)8)
20.12.2017 07:03:37
а есть ли макрос, которые бы отслеживал что вообще сделал пользователь? и выявить главную проблему фирм, скопировал ли лист или файл, что важно для систем безопасности, на этом можно деньжат заработать огого, с помощью какого то макроса)):D
21.12.2018 11:58:54
я еще бы добавил в код "предзакрытия книги" запись
On Error Resume Next
сам код
On Error GoTo 0
так как если пользователь что то изменит в книге но нажмет "не сохранять" появится окно ошибки макроса, а так не появится
Наверх