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

kelvin_9

Board Regular
Joined
Mar 6, 2015
Messages
246
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)
Code:
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)
Code:
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,311
Office Version
2019, 2016, 2013
Platform
Windows
which lines do you get to before it fails
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
Try
Code:
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

Board Regular
Joined
Mar 6, 2015
Messages
246
Try
Code:
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?:confused:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
Where do you get the error?
 

kelvin_9

Board Regular
Joined
Mar 6, 2015
Messages
246
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
What line of code gives the error?
 

kelvin_9

Board Regular
Joined
Mar 6, 2015
Messages
246
What line of code gives the error?
sorry Fluff for the late reply
Code:
[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 & cellbeforeafteruserdate & 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?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
You're code is designed for changing/pasting individual cells, not entire sheets.
To prevent the error when pasting an entire sheet use
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,444
Messages
5,450,488
Members
405,613
Latest member
Arpit

This Week's Hot Topics

Top