Tracking changes in a workbook - Error with code

[crookesa]

I found the following code posted by @Logit and have been using it and it's working well.

The only problem is that when a user selects all cells on the sheet (the button above row 1 & left of column A) i get an error below

The code I am using is below and I have it saved in the ThisWorkbook code. The code line that is highlight in the debug is vOldVal = Target is the last sub of the code. Can anyone help me work out how to still use the code if someone selects the entire worksheet?

Option Explicit

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim bBold As Boolean

If Target.Cells.Count > 1 Then Exit Sub
If ActiveSheet.Name = "AUDIT" Then Exit Sub

'On Error Resume Next

    With Application
         .ScreenUpdating = False
         .EnableEvents = False

    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
        With Sheets("AUDIT")
            '.Unprotect Password:="Secret"
                If .Range("A1") = vbNullString Then
                    .Range("A1:F1") = Array("Cell Changed", "Old Value", _
                        "New Value", "TIME", "DATE", "USER")
                End If

            With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = ActiveSheet.Name & "!" & Target.Address
                  .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
              If bBold = True Then
                .ClearComments
                .AddComment.Text Text:= _
                     "NOTE :" & Chr(10) & "" & Chr(10) & _
                        "Bold values are the results of formulas"

              End If
                .Value = Target
                .Font.Bold = bBold

            End With
                .Offset(0, 3) = Time
                .Offset(0, 4) = Date
                .Offset(0, 5) = Application.UserName
            End With
            .Cells.Columns.AutoFit
            '.Protect Password:="Secret"
        End With

    vOldVal = vbNullString

    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With
On Error GoTo 0

                'MsgBox "There was a change to this sheet !"
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    vOldVal = Target
End Sub

 

[mumps]

Try:

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

 

[Fluff]

You need to change this

If Target.Cells.Count > 1 Then Exit Sub

to

If Target.CountLarge > 1 Then Exit Sub

 

[crookesa]

Try:

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

This gave me an Error 6 instead of 7 when I tried it. This following line of did though did fix it, which i tried from the suggestion from @Fluff

If Target.CountLarge > 1 Then Exit Sub

 

[crookesa]

You need to change this

If Target.Cells.Count > 1 Then Exit Sub

to

If Target.CountLarge > 1 Then Exit Sub

This didn't work as the problem appear in the Workbook_SheetSelectionChange sub. I did however use the code in the sub mentioned and it did fix it.
So it appears a combination of both solutions has fixed it, unless I'm missing something else?

 

[Fluff]

You will need to use that line in both procedures.

 

[crookesa]

You will need to use that line in both procedures.

Ok thanks I'll give it a go. is there much different between the cells.count or countlarge?

 

[Fluff]

Yes using Count will cause an error is you select the entire sheet.