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.



"Melanie" wrote:

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!!

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" wrote:

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" wrote:

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,
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" wrote:

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" wrote:

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" wrote:

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!!

doesn't work...

"Eduardo" wrote:

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" wrote:

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" wrote:

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" wrote:

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!!

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

On Mon, 17 Aug 2009 11:28:02 -0700, Melanie
wrote:

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" wrote:

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" wrote:

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!!

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

On Mon, 17 Aug 2009 11:31:01 -0700, Eduardo
wrote:

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" wrote:

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" wrote:

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" wrote:

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!!

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

"Gord Dibben" wrote:

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

On Mon, 17 Aug 2009 11:28:02 -0700, Melanie
wrote:

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" wrote:

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" wrote:

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!!

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

On Mon, 17 Aug 2009 12:22:01 -0700, Melanie
wrote:

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

"Gord Dibben" wrote:

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

On Mon, 17 Aug 2009 11:28:02 -0700, Melanie
wrote:

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" wrote:

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" wrote:

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!!