Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: macro track changes return error #6 overflow / #13 type mismatch
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2015
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,680
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

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

    which lines do you get to before it fails
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,906
    Post Thanks / Like
    Mentioned
    385 Post(s)
    Tagged
    41 Thread(s)

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

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by mole999 View Post
    which lines do you get to before it fails
    the second path: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Fluff View Post
    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?

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,906
    Post Thanks / Like
    Mentioned
    385 Post(s)
    Tagged
    41 Thread(s)

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

    Where do you get the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Fluff View Post
    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 by kelvin_9; May 20th, 2019 at 01:05 PM.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,906
    Post Thanks / Like
    Mentioned
    385 Post(s)
    Tagged
    41 Thread(s)

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

    What line of code gives the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Fluff View Post
    What line of code gives the error?
    sorry Fluff for the late reply
    Code:
    Sheets("TRACK").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
    in addition, i found this returned in sheet_track
    sheet & cell before after user date & time
    GEMS 1:1048576

    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 by kelvin_9; May 22nd, 2019 at 10:11 AM.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,906
    Post Thanks / Like
    Mentioned
    385 Post(s)
    Tagged
    41 Thread(s)

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

    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
    If Target.CountLarge > 1 Then Exit Sub
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •