Tracking changes in a workbook - Error with code

crookesa

Board Regular
Joined
Apr 11, 2013
Messages
78
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

Capture.JPG


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?

VBA Code:
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,181
Try:
VBA Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    vOldVal = Target
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
You need to change this
VBA Code:
If Target.Cells.Count > 1 Then Exit Sub
to
VBA Code:
If Target.CountLarge > 1 Then Exit Sub
 
Solution

crookesa

Board Regular
Joined
Apr 11, 2013
Messages
78
Try:
VBA Code:
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

Board Regular
Joined
Apr 11, 2013
Messages
78

ADVERTISEMENT

You need to change this
VBA Code:
If Target.Cells.Count > 1 Then Exit Sub
to
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
You will need to use that line in both procedures.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Yes using Count will cause an error is you select the entire sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top