How to Track and Count User Edits in Excel

mistylsand

New Member
Joined
Sep 16, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
I have an excel checklist of items that are marked passed or failed by using a drop down box. Once the item has been completed i would like to be able to track the user who marked it and a time stamp of when they marked it.

Date format would be m/d/yyyy h:mm AM/PM.
The checks marked passed or failed are in range I18:I2500
I want to track those changes in columns KO18:KO2500.
I would prefer the username in one column and the date in another if possible.

Is this possible to accomplish?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
What have you tried? Is the dropdown box you mentioned a Data Validation dropdown? Is the checklist just a column of spreadsheet cells? How about a picture of your workbook to help us understand it. First thought is to respond to the worksheet_change event in someway.
 
Upvote 0
What have you tried? Is the dropdown box you mentioned a Data Validation dropdown? Is the checklist just a column of spreadsheet cells? How about a picture of your workbook to help us understand it. First thought is to respond to the worksheet_change event in someway.
Thank you for your help. I figured it out though.
When they enter the the test data here.
1620736435889.png

I used VBA to add the user, date and time for the checks that were marked in the columns below.
1620736498278.png

Used this code to pull the user, date and time for each test marked off:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 7 Then
Application.EnableEvents = False
Cells(Target.Row, 302).Value = Application.UserName
Cells(Target.Row, 303).Value = Date
Cells(Target.Row, 304).Value = Time
Application.EnableEvents = True
End If

If Target.Column = 8 Then
Application.EnableEvents = False
Cells(Target.Row, 306).Value = Application.UserName
Cells(Target.Row, 307).Value = Date
Cells(Target.Row, 308).Value = Time
Application.EnableEvents = True
End If

If Target.Column = 9 Then
Application.EnableEvents = False
Cells(Target.Row, 310).Value = Application.UserName
Cells(Target.Row, 311).Value = Date
Cells(Target.Row, 312).Value = Time
Application.EnableEvents = True
End If

If Target.Column = 10 Then
Application.EnableEvents = False
Cells(Target.Row, 314).Value = Application.UserName
Cells(Target.Row, 315).Value = Date
Cells(Target.Row, 316).Value = Time
Application.EnableEvents = True
End If

If Target.Column = 11 Then
Application.EnableEvents = False
Cells(Target.Row, 318).Value = Application.UserName
Cells(Target.Row, 319).Value = Date
Cells(Target.Row, 320).Value = Time
Application.EnableEvents = True
End If

If Target.Column = 12 Then
Application.EnableEvents = False
Cells(Target.Row, 322).Value = Application.UserName
Cells(Target.Row, 323).Value = Date
Cells(Target.Row, 324).Value = Time
Application.EnableEvents = True
End If
If Target.Column = 13 Then
Application.EnableEvents = False
Cells(Target.Row, 326).Value = Application.UserName
Cells(Target.Row, 327).Value = Date
Cells(Target.Row, 328).Value = Time
Application.EnableEvents = True
End If

If Target.Column = 14 Then
Application.EnableEvents = False
Cells(Target.Row, 330).Value = Application.UserName
Cells(Target.Row, 331).Value = Date
Cells(Target.Row, 332).Value = Time
Application.EnableEvents = True
End If

End Sub
 
Upvote 0
Solution
Glad to hear you solved it. Well done.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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