Tracking username, time stamping , product code on different worksheet

CT Chua · Feb 23, 2018

Hi,
I have somewhat of the same problem as Spraggs.
Have a spreadsheet on Sheet1 with Product Code in Column A and Column G is the Unit Price.
How to track the username, Product code, date and time whenever the respective Unit price is change and store them on Sheet 2 in 3 column?

Example
Sheet 1

Product Code	Column B	Column C	Column D	Column E	Column F	Unit Price
AAA-0001	Data	Data	Data	Data	Data	$300.00
AAA-0212	Data	Data	Data	Data	Data	$150.00
Etc, etc	Data	Data	Data	Data	Data	$75.00

New Sheet 2 (Tracking Sheet)

Username	Product code	Date n Time Stamping
Apple	AAA-0335	22/02/2018 11:05 am
James	AAA-1145	24/02/2018 02:13 pm

Would appreciate if you could help.
Thanks.

Coding4Fun · Feb 23, 2018

Right Click Sheet1 - View Code and insert the following

Code:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Column = 7 Then
Changedby = Application.UserName
DateChanged = Now()
cpyrng = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row + 1


Sheets("Sheet2").Range("B" & cpyrng).Value = Changedby
Sheets("Sheet2").Range("D" & cpyrng).Value = DateChanged
Sheets("Sheet2").Range("C" & cpyrng).Value = Cells(Target.Row, "A").Value


End If


End Sub

See this thread for more info

https://www.mrexcel.com/forum/excel-questions/1041909-user-open-close-save-changes-log-sheet.html

CT Chua · Feb 24, 2018

Thank you very much for the super fast reply.

CT Chua · Feb 25, 2018

Hi,

With the code from Coding is Fun (see above). My spreadsheet is working perfectly as needed (Superb Work!!!, Thanks). I have .come to realise that I would also need to keep track of the old unit price for checking. Very sorry I didn't thought of this earlier. Can this be added into the same tracking sheet2 on column E?
Thanks.

Coding4Fun · Mar 3, 2018

Coding4Fun*

Try this - This will capture the old value as well as what it was changed to.

Code:

Public Oval As String


Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Column = 7 Then
Changedby = Application.UserName
DateChanged = Now()
cpyrng = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row + 1




Sheets("Sheet2").Range("B" & cpyrng).Value = Changedby
Sheets("Sheet2").Range("D" & cpyrng).Value = DateChanged
Sheets("Sheet2").Range("C" & cpyrng).Value = Cells(Target.Row, "A").Value
Sheets("Sheet2").Range("E" & cpyrng).Value = Oval
Sheets("Sheet2").Range("F" & cpyrng).Value = Cells(Target.Row, "G").Value


End If


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Oval = Target.Value
End Sub

CT Chua · Mar 6, 2018

Thanks you "Coding4Fun". Code working very well .

Now when I stack this code with one that I found that force a column or two into propercase and upper case.
I kept getting this error messages (Run-time error #9 –Subscript out of range ).

When I don’t stack the two code together they both work fine separately.

I am just a Excel user who has very little knowledge of VBA. I am trying to solve some problem which need constant checking.

Which I find can’t be done without VBA, I was told. If its possible without VBA please advise too, Thanks.

I am very sure it’s my ignorance with VBA that is causing the run-time error. I would greatly appreciate if someone could help me out.

Herewith the 2 code I stack, all code below is added into Sheet1.

Public Oval As String

Private Sub Worksheet_Change(ByVal Target As Range)
'Forces text to Proper case for the range I3:I16475 &M1:M16475

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("I3:I16475", "M1:M16475")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If

'Forces text to Upper case for the range D1:D16475" & "E1:E16475

If Not Intersect(Target, Range("D1:D16475", "E1:E16475")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0

'Tracking Changes

If Target.Column = 14 Then
Changedby = Application.UserName
DateChanged = Now()
cpyrng = Sheets("Sheet4").Cells(Rows.Count, 2).End(xlUp).Row + 1 'error msg at this line

Sheets("Sheet4").Range("B" & cpyrng).Value = Changedby
Sheets("Sheet4").Range("D" & cpyrng).Value = DateChanged
Sheets("Sheet4").Range("C" & cpyrng).Value = Cells(Target.Row, "A").Value
Sheets("Sheet4").Range("E" & cpyrng).Value = Oval
Sheets("Sheet4").Range("F" & cpyrng).Value = Cells(Target.Row, "N").Value
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Oval = Target.Value
End Sub

Thank You Very Much.

Coding4Fun · Mar 6, 2018

I was able to run through the code without any issues, my guess is that whatever workbook you have this macro in does not actually have a sheet named "Sheet4" which your code is trying to pull from?

Code:

[COLOR=#333333]cpyrng = Sheets("[/COLOR][COLOR=#ff0000]Sheet4[/COLOR][COLOR=#333333]").Cells(Rows.Count, 2).End(xlUp).Row + 1 'error msg at this line[/COLOR]

This line is saying look at "Sheet4" and get the next available row, either you do not have a "Sheet4" or you are at the bottom of the spreadsheet and there are no more rows available to add a new row.

That is the only time I see run time 9 with your code, if I do not create the required Sheet4 and I run the code I will get the run time error 9.

So either create a Sheet4 or rename the code to reflect the actual sheet name that you are trying to put this change log data into.

CT Chua · Mar 8, 2018

Omg..... I am so stupid.........just like you said I name the Sheets(wrongly)........hahaha, very sorry. It is now working like you mentioned.
Greatly appreciate you patience and help............Thanks you very very much.

Regards,

Coding4Fun · Mar 8, 2018

It happens

glad it is working now.

CT Chua · Mar 9, 2018

Coding4Fun said:
It happens glad it is working now.

Hi Coding4Fun

Once again Thanks, Until next time. Bye

Regards

Tracking username, time stamping , product code on different worksheet

CT Chua

New Member

Excel Facts

Coding4Fun

Active Member

CT Chua

New Member

CT Chua

New Member

Coding4Fun

Active Member

CT Chua

New Member

Coding4Fun

Active Member

CT Chua

New Member

Coding4Fun

Active Member

CT Chua

New Member

Similar threads

Forum statistics

Share this page

Tracking username, time stamping , product code on different worksheet

New Member

Excel Facts

Active Member

New Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock