Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shared Workbook - User Not Logging Out | Excel Discussion (Misc queries) | |||
Viewing Comments on a Protected Spreadsheet | Excel Discussion (Misc queries) | |||
Users stuck, not logging out of shared workbook | Excel Discussion (Misc queries) | |||
Password-Protected Workbook not showing "in use by another user" | Excel Discussion (Misc queries) | |||
Viewing protected cells | Excel Discussion (Misc queries) |