VBA Code for User log history

[Kevo]

Hello
Can you please assist with correcting the below VBA code as i am getting an error on the text in yellow-"Run-time error '13': Type mismatch." .Really appreciate any help here. Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C6:X999")) Is Nothing And Range("C" & Target.Row).Value <> Empty Then
If Changes.Range("H2").Value) = Target.Value Then Exit Sub
Dim ChngRow As Long
With Changes
ChngRow = .Range("A99999").End(xlUp).Row + 1
.Range("A" & ChngRow).Value = ThisWorkbook.BuiltinDocumentProperties("Last Author")
.Range("B" & ChngRow).Value = ActiveSheet.Name
.Range("C" & ChngRow).Value = Target.Address
.Range("D" & ChngRow).Value = .Range("H2").Value
.Range("E" & ChngRow).Value = Target.Value

End With
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C6:X999")) Is Nothing And Range("C" & Target.Row).Value <> Empty Then
Changes.Range("H2").Value = Target.Value

End If
End Sub

 

[Joe4]

You are trying to use an undefined object of "Changes".
What is that supposed to be?
Are you trying to work across sheets?
If so, what are the sheet names, and what is the relationship between them and what you are trying to do?

 

[Kevo]

Changes is the Sheet name

Im creating a User log history (Got a data input sheet where the above code is used) and then whenever a user makes changes to to the data input sheet, these changes are showed in the Changes sheet name

 

[Kevo]

Apologies...Please see exact full code (Tried using chat Gpt and it moved some fields around)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C6:X999")) Is Nothing And Range("C" & Target.Row).Value <> Empty Then
If Changes.Range("H2").Value) = Target.Value Then Exit Sub
Dim ChngRow As Long
With Changes
ChngRow = .Range("A99999").End(xlUp).Row + 1
.Range("A" & ChngRow).Value = Now
.Range("B" & ChngRow).Value = ThisWorkbook.BuiltinDocumentProperties("Last Author")
.Range("C" & ChngRow).Value = ActiveSheet.Name
.Range("D" & ChngRow).Value = Target Address
.Range("E" & ChngRow).Value = .Range("H2").value
.Range("F" & ChngRow).Value = Target.Value

End With
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C6:X999")) Is Nothing And Range("C" & Target.Row).Value <> Empty Then
Changes.Range("H2").Value = Target.Value
End If
End Sub





Basically what im doing is creating a User log history (Got a data input sheet where the above code is used) and then whenever a user makes changes to any cells in the data input sheet, these changes are showed in the Changes sheet name under the below columns

.Range("A" & ChngRow).Value = Now 'Changes the date and Time
.Range("B" & ChngRow).Value = ThisWorkbook.BuiltinDocumentProperties("Last Author") 'User
.Range("C" & ChngRow).Value = ActiveSheet.Name 'Sheet
.Range("D" & ChngRow).Value = Target Address 'Cell
.Range("E" & ChngRow).Value = .Range("H2").value 'Old Value
.Range("F" & ChngRow).Value = Target.Value 'New Value


H2 in Changes Sheet is the selected range

 

[Joe4]

If "Changes" is supposed to be a sheet object, you should first declare that object, i.e.

Dim Changes as Worksheet

and then you need to actually set the object to something, i.e.

Set Changes = Sheets("Changes")

Then you can use it like you show in your code after you do those two things.
You will need to do that in EACH procedure, unless you create and set global variables.

 

[Kevo]

Thanks so much for your prompt reply, however would it be possible to show me what the full code will like as i am a novice when it comes to excel VBA

 

[Kevo]

Hi...Just following up if you can share what the full code will look like.

 

[Joe4]

Hi...Just following up if you can share what the full code will look like.

I don't like trying to figure out what you were intending from bad code.
If you would like me to provide code for you, please describe (in plain English), exactly what you want to happen, and when.
Be sure to include all conditions/criteria/limitations, etc.

 

[Kevo]

Please see below link to a 1 minute video of the coding i used....(Which works perfectly except when i try to copy/drag down a field to the next row) it gives me a run-time error "13" mistmatch on line 3 highlighted in yellow above.

How To Track Changes In Excel #SHORTS - Google Search

 

[Joe4]

Please see below link to a 1 minute video of the coding i used....(Which works perfectly except when i try to copy/drag down a field to the next row) it gives me a run-time error "13" mistmatch on line 3 highlighted in yellow above.

How To Track Changes In Excel #SHORTS - Google Search

That does not tell me anything about your situation/data.

If that truly is the only issue, the answer I already provided should answer that.
Just add those two lines of code I showed you at the top of each procedure.