Отслеживание входа пользователей в книгу Excel
Представьте себе книгу 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:
Теперь точно никто не уйдет безнаказанным. Большой Брат следит за тобой! ;)
Ссылки по теме
- Суперскрытый лист
- Выборочное отображение листов отдельным пользователям
- 4 способа защиты данных в Microsoft Excel
В результате немного упрощается код, сортировка получается обратная, "новые сверху", что позволяет быстрее просматривать последние записи, не прокручивая лист.
При открытии - вставляется строка, её номер всегда будет 2. При закрытии файла в строку 2 добавляется время.
Переполнение этого стека маловероятно, но и проверку такого переполнения проводить не нужно, просто отрезать строки ниже запланированного количества записей, без проверки.
Единственный вариант, который вижу с ходу - вынести ЛОГ в другой файл.
Я то же сталкивался с проблемой мониторинга изменений файла несколькмими пользователями.
Для себя решил эту проблему через организацию совместного достапа к файлу (Рецензирование --> Достук к книге).
"Плюсы":
- проще в настройке;
- не требует написания макросов;
- отслеживает изменение данных (кто и что поменял).
"Минусы":
- файл очень быстро увеличивает свой размер;
- "не сильно умным" все равно ничего не докажешь.
у меня вопрос, можна делать лист Лог "visible" но запретит изменения для ползователей. Я хочу ползаватели знали что идет запис[IMG]
Спасибо за ране [IMG]
Тем не менее, как вынести лог в другую книгу?
Мне не нужно сохранять общий файл как "лист XL с поддержкой макросов", как сделать так, чтобы все эти записи велись в другом файле?
Заранее большое спасибо!
Заранее большое спасибо!
А про защиту структуры книги - это вы очень правильно уточнили. Лучше и здесь пароль поставить, а то особо одаренные залезут, как пить дать.
если единственная цель это:
Из минусов здесь - не будет видно кто просто просматривал книгу, но цель-то у нас см. выше
И ещё, полезно в конце Workbook_Open использовать
Что бы не было лишнего приглашения сохранить книгу при выходе из неё.
У меня установлен Excel 2003. Я пошагово выполнила все необходимые действия. Скопировала макрос с экрана и вставила в "ЭтаКнига" одной строкой. В результате при закрытии файла система выдает ошибку "expected end sub". Что я сделала не правильно?
Спасибо.
Подскажите, пожалуйста, как можно сделать так, чтобы при открытии книги не скрывались рабочие листы и появлялся лист "Предупреждение", а невозможно было внести изменения, пока макросы не включены, а при попытке внесения изменений в ячейки книги выводилось окно с сообщением "Для корректной работы файла нажмите "Включить содержимое" и здесь, желательно, изображение панели с кнопкой, которую нужно нажать" и так пока пользователь не включит макросы.
Спасибо!
Зацените идеи, которую вы подняли в этой статье.
Где исходники?
я бы не стал запускать Это на своем компьютере, не известно что еще делает этот макрос
Сейчас качну посмотреть как устроено.
С Вашего адреса проверка нормально идет на мой...(если всё, кроме адреса "Кому", оставляю без изменений)
С уважением М. Ю. В.
Пароль "1945-2015"
Прошу подсказать как вместо Workbook_BeforeClose использовать Workbook_BeforeSave
Пишет следующее "Procedure declaration does not match description of event or procedure having the same name"
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 'запись во вторую ячейку второй строки
Вопрос к мастерам о развитии темы
Если совместить предложенные варианты в одном файле возникает ошибка.
This command will stop the debbugg и Compile error: Variable not defined
Суть идеи сохранить не только реестр изменений ячейки, а параллельно вести журнал в котором при выходе и сохранении будет записываться:
- сначала срока со старыми значениями ячеек,
- строка с новыми значениями ячеек
Пример прилагаю
Помогите! у меня вопрос, можна делать лист Лог "visible" но запретит изменения для ползователей. Я хочу ползаватели знали что идет запис:D
Спасибо за ране
1. отсутствие окна при выходе
2. сохранение файла при выходе (иногда надо выйти без сохранения)
только я новичок VBA и прошу помощи в написании небольшой части кода)
И тот же вопрос, что и выше: можно лог сделать видимым, но нередактируемый для пользователей, но макрос туда вносит даты?
попутался соединить это макрос с другим, который автоматически закрывает книгу через определенное время, но получаю ошибку.
вот тот макрос, который просто вписал после макроса, описанного в данной теме:
On Error Resume Next
сам код
On Error GoTo 0
так как если пользователь что то изменит в книге но нажмет "не сохранять" появится окно ошибки макроса, а так не появится
Все отлично работало ровно до того момента, как я решил "Защитить книгу", установив пароль.
После защиты VB выдает ошибку в 8-й строке кода:
Worksheets("Предупреждение").Visible = True
Подскажите пожалуйста, в чём проблема и как её устранить.
Заранее благодарен.