logging user name after viewing protected workbook

  • Thread starter Thread starter Melanie
  • Start date Start date
M

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!!
 
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.
 
I'm a newbie. Where is the open event? I can't find it in the Modules of
the VBA. Is it somewhere else?
 
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
 
doesn't work...

Eduardo said:
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
Click to expand...
 
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
 
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
 
Thanks so much!!! It works!!!!

Gord Dibben said:
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
Click to expand...
 
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
 
Back
Top