macro track changes return error #6 overflow / #13 type mismatch

[kelvin_9]

hello, i'm a macro beginner and i've got an error #6/#13 on this 2 code, what's wrong with this and how can i amend it?
thank you so much

error #13 type mismatch (select more than 1 cell)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)Dim sSheetName As String
sSheetName = ActiveSheet.Name
If ActiveSheet.Name <> "TRACK" Then
Application.EnableEvents = False
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " – " & Target.Address(0, 0)
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
'Sheets("TRACK").Hyperlinks.Add Anchor:=Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="‘" & sSheetName & "‘!" & oldAddress, TextToDisplay:=oldAddress


'Sheets("TRACK").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValue = Target.Value
oldAddress = Target.Address
End Sub

error #6 overflow (slecet the whole sheet)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)Dim sSheetName As String
sSheetName = ActiveSheet.Name
If ActiveSheet.Name <> "TRACK" Then
Application.EnableEvents = False
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " – " & Target.Address(0, 0)
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
'Sheets("TRACK").Hyperlinks.Add Anchor:=Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="‘" & sSheetName & "‘!" & oldAddress, TextToDisplay:=oldAddress


'Sheets("TRACK").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Count = 1 Then
oldValue = Target.Value
End If
End Sub

 

[mole999]

which lines do you get to before it fails

 

[Fluff]

Try

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Oldvalue = Target.Value
End Sub

 

[kelvin_9]

which lines do you get to before it fails

the second path: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

 

[kelvin_9]

Try

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Oldvalue = Target.Value
End Sub

this is fantastic Fluff!!
but i 've got another error #7 out of memory, if i paste into the whole sheet from another workbook?

 

[Fluff]

Where do you get the error?

 

[kelvin_9]

Where do you get the error?

sheet 1(also tried in a new sheet)
i open and copy the worksheet from another workbook, and paste on it, error pop up

 

[Fluff]

What line of code gives the error?

 

[kelvin_9]

What line of code gives the error?

sorry Fluff for the late reply

[COLOR=#333333]Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value[/COLOR]

in addition, i found this returned in sheet_track

sheet & cell	before	after	user	date & time
GEMS – 1:1048576

<tbody>
</tbody>

finally,
if i want more record in sheet_track, i need to save the workbook first, reopen the workbook again
is it possible to record anything changed by any user after my macro loaded? instead of i have to save and reopen?

 

[Fluff]

You're code is designed for changing/pasting individual cells, not entire sheets.
To prevent the error when pasting an entire sheet use

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
[COLOR=#ff0000]If Target.CountLarge > 1 Then Exit Sub[/COLOR]
sSheetName = ActiveSheet.Name
If ActiveSheet.Name <> "TRACK" Then
Application.EnableEvents = False
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " – " & Target.Address(0, 0)
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Oldvalue
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
'Sheets("TRACK").Hyperlinks.Add Anchor:=Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="‘" & sSheetName & "‘!" & oldAddress, TextToDisplay:=oldAddress


'Sheets("TRACK").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub