logging user name after viewing protected workbook

[Melanie]

I have a protected workbook and I would like to know who has accessed the file.
Is there a way to log the user name of whoever has accessed it in a
different file?

Thanks!!

 

[Eduardo]

Hi,
see if this solution given in the community helps you

A simple text file should be OK, call this in the open event

Sub test()
Dim sFile As String
Dim sText
Dim ff As Long

sFile = Application.DefaultFilePath
' or maybe
'sFile = ThisWorkbook.Path
If Right$(sFile, 1) <> "\" Then sFile = sFile & "\"

sFile = sFile & "logTest.txt"

sText = "ABC" & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")

ff = FreeFile
Open sFile For Append As #ff
Print #ff, sText
Close #ff

End Sub

I don't know what fOSUserName() is but replace the "ABC" with it.

 

[Melanie]

I'm a newbie. Where is the open event? I can't find it in the Modules of
the VBA. Is it somewhere else?

 

[Eduardo]

Hi,
right click in the mouse on the tab name, View code, paste the code there

may a backup of your spreadsheet before running it, I didn';t try it

Hope this helps

 

[Melanie]

doesn't work...

Hi,
right click in the mouse on the tab name, View code, paste the code there

may a backup of your spreadsheet before running it, I didn';t try it

Hope this helps

 

[Gord Dibben]

Alt + F11 to open VBE

CTRL + r to open Project Explorer.

Select your workbook/project and expand it.

Expand Microsoft Excel Objects.

Double-click on Thisworkbook module.

Paste this into that module.

Private Sub Workbook_Open()
Dim sFile As String
Dim sText
Dim ff As Long

sFile = Application.DefaultFilePath
' or maybe
'sFile = ThisWorkbook.Path
If Right$(sFile, 1) <> "\" Then sFile = sFile & "\"

sFile = sFile & "logTest.txt"

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")
'Environ("Username") is the login name of user opening the workbook
ff = FreeFile
Open sFile For Append As #ff
Print #ff, sText
Close #ff
End Sub

Save and close the workbook.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Worksheet module is not the place to store a workbook_open event.

See my post to Melanie.

I have tested the amended code and works for me.


Gord Dibben MS Excel MVP

 

[Melanie]

Thanks so much!!! It works!!!!

Alt + F11 to open VBE

CTRL + r to open Project Explorer.

Select your workbook/project and expand it.

Expand Microsoft Excel Objects.

Double-click on Thisworkbook module.

Paste this into that module.

Private Sub Workbook_Open()
Dim sFile As String
Dim sText
Dim ff As Long

sFile = Application.DefaultFilePath
' or maybe
'sFile = ThisWorkbook.Path
If Right$(sFile, 1) <> "\" Then sFile = sFile & "\"

sFile = sFile & "logTest.txt"

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")
'Environ("Username") is the login name of user opening the workbook
ff = FreeFile
Open sFile For Append As #ff
Print #ff, sText
Close #ff
End Sub

Save and close the workbook.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Good to hear that.

Welcome to VBA and event code.

Thanks to Eduardo for posting the original code for creating the log file
and appending to it.


Gord