Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 78
Default logging user name after viewing protected workbook

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!!
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,276
Default logging user name after viewing protected workbook

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

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 78
Default logging user name after viewing protected workbook

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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,276
Default logging user name after viewing protected workbook

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

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 78
Default logging user name after viewing protected workbook

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



  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default logging user name after viewing protected workbook

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


  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default logging user name after viewing protected workbook

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


  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 78
Default logging user name after viewing protected workbook

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



  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default logging user name after viewing protected workbook

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shared Workbook - User Not Logging Out John Excel Discussion (Misc queries) 0 February 6th 09 03:36 PM
Viewing Comments on a Protected Spreadsheet Pendelfin Excel Discussion (Misc queries) 2 August 6th 07 09:06 AM
Users stuck, not logging out of shared workbook Peter Excel Discussion (Misc queries) 1 January 4th 07 02:01 PM
Password-Protected Workbook not showing "in use by another user" Jim Jackson Excel Discussion (Misc queries) 1 July 13th 06 03:11 PM
Viewing protected cells AC_VID Excel Discussion (Misc queries) 1 April 10th 05 01:21 AM


All times are GMT +1. The time now is 08:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"