Logging Users Opening Spreadsheet 1

[metalboy]

Hi guys,

Is it possible to log all users who open a spreadsheet and what time they opened it?

Thankyou all who look at this..

Regards

Alex

“It is a mistake to think you can solve any major problems just with potatoes.”
Douglas Adams

 

[Phaed]

pick up username - environ("username") assuming username is a valid environment variable.

pick up time - Now.

write username and time to a log file

 

[metalboy]

thanks for that. its my first time using VBA for excel though where would i place the code and what is the specific code?

Regards

“It is a mistake to think you can solve any major problems just with potatoes.”
Douglas Adams

 

[boggg1]

Start a new spreadsheet
Select Tools, Macro, Visual Basic Editor (or press Alt-F11)
Double Click "This Workbook" in the upper left pane
In the WIndow select
Change "(General)" to "Workbook" in the left hand drop down box in the main pane
Make sure the right hand drop down box says "Open"
Change the text to read...

Private Sub Workbook_Open()

MsgBox "Do something"

End Sub

Now save the workbook, shut it down and restart it from its icon ("enable macros" if you get asked). The message box should pop up. You have just used your first "event driven" program where the action of opening the workbook has started a program (which did something trivial).

More later when I have time but at least you are started and Phaed's post is the next thing to look at.

Boggg1

 

[boggg1]

Replace the code with...

Private Sub Workbook_Open()

Open "C:\Spreadsheet Openers.txt" For Append As #1
Print #1, Environ("username"), Date & " " & Time
Close #1

MsgBox "Your use of this file has been recorded"

End Sub

At present this can be circumvented by disabling macros at start-up, so every user needs to know to allow the macro to run - I can't think how to get around this limitation at this moment.

As a newcomer I should say that this macro has been placed in the workbook, because the event is applicable to the workbook opening. It is also possible to insert code into
a module (although it will not respond to events there). If your code grows a lot (more than a hundred lines ?) then you should consider organising much of it into subroutines and functions in modules, which are called from events as above. But that is for the future.

Boggg1