macro to capture who accessed the Excel file

C

cedmunds

New Member
Joined
Aug 25, 2008
Messages
9
Anyone know of a way to capture who (user name, machine name etc.) had the excel file open when a certain cell is selected? a Macro?

Trying to use an Excel spreadsheet for a peer review where each reviewer accesses the Excel peer reivew file and enters the minutes they spent in the peer review but would like to also capture the user that had the file open when the minutes spent on the peer review were entered. This would provide proof that the actual peer reviewer opened the file and entered their minutes.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Try this. Right click the sheet tab, select View Code and paste in

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
    Application.EnableEvents = False
    Range("B1").Value = Now
    Range("C1").Value = Environ("username")
    Application.EnableEvents = True
End If
End Sub

then close the code window using the X.

If an entry is made in A1 the date & time will be placed in B1 and the username in C1.
 
Upvote 0
Thank you so much this is going to be great.

I modified your code just to go to different cells...see below.

What I really need is to do the same thing for any cell in the "F15" to "F25" range i.e. if the person editing makes a change to F15 then G15 and H15 will get the data and if the person editing makes a change to F16 then G16 and H16 will get the date/time and user data. Can you show me how to do that?


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "F15" Then
Application.EnableEvents = False
Range("G15").Value = Now
Range("H15").Value = Environ("username")
Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Try this

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F15:F25")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now
Target.Offset(0, 2).Value = Environ("username")
Application.EnableEvents = True
End Sub

However, Kenneth Hobson makes a good point and it might be prudent to write the information to a hidden sheet as there is nothing to stop a reviewer from changing what is in columns G and H. Let us know if you would like code to do that.
 
Upvote 0
Yes having a hidden sheet sounds like a great idea although I would need to know how to "unhide" the sheet to view/print it.

In that case then the entries on the hidden sheet should be as below with the entry for "G15" starting At hidden sheet "A2", "B2", "C2" then "A3", "B3"," C3" etc. leaving A1,B1,C1 open for history headings:

1. A2 = the identity of the cell modified e.g "sheet1" G15
2. B2 = the date/time
3. C2 = the user

Thank you so much for responding so quickly. This is solving a long standing problem.
 
Upvote 0
We'll will deal with the hidden bit after we've tested this. Add a new sheet, rename it to Log and add in A1 to C1 Cell, When, Who (or whatever headings you prefer). Then on the sheet where the reviewers comments are added change the code to

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NR As Long
If Intersect(Target, Range("F15:F25")) Is Nothing Then Exit Sub
With Sheets("Log")
    NR = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & NR).Value = Target.Address(False, False)
    .Range("B" & NR).Value = Now
    .Range("C" & NR).Value = Environ("username")
End With
End Sub
 
Upvote 0
The code you sent to log edits on a Log sheet worked perfectly capturing all edits to cells in the target range on the Log sheet. Thank You.

I tried out the hide worksheet under the tools menu to hide the Log sheet and that worked. I also tried out "protect sheet" on the tools menu and password protected the Log sheet but that causes an error when logging data. What would be handy is to be able to password protect viewing the log sheet rather than protecting it which causes an error on the logging. Guess you'll have to guide this newbee on what best to do on this as well.
 
Upvote 0
This unprotects then re-protects the Log sheet - you need to change xyz to a more sensible password.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NR As Long
If Intersect(Target, Range("F15:F25")) Is Nothing Then Exit Sub
With Sheets("Log")
    .Unprotect password:="xyz"
    NR = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & NR).Value = Target.Address(False, False)
    .Range("B" & NR).Value = Now
    .Range("C" & NR).Value = Environ("username")
    .Protect password:="xyz"
End With
End Sub
 
Upvote 0
Once again your code did the trick. I now have a protected log sheet for peer reviews. Exactly what I needed. I cannot thank you enough for your excellent help on this. I am just realizing how powerful Excel can be.:) * 100
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Excel Table - How to Append to Access Database Using VBA
Replies
1
Views
196
Micron
M
A
  • Question Question
Creating a workbook from a Tracker spreadsheet that links back to cells within the Tracker spreadsheet
Replies
2
Views
425
AndyEd
A
T
  • Question Question
Save As Dialog Box with File Name Predetermined for Ppt file through Excel
2
Replies
10
Views
289
tbakbradley
T
J
  • Solved
Using Index/Match (or Something Else) to Return Row(s) of Data
Replies
4
Views
257
jbennett01
J
S
  • Question Question
Auto protect certain columns after 5 minutes inactivity
Replies
1
Views
128
SKuser
S

Forum statistics

Threads
1,225,155
Messages
6,183,208
Members
453,151
Latest member
Lizamaison

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top