Hiding error message

Question

I have the following two modules in my workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
' check if target is in Column B or Column G
If Target.Column = 2 Or Target.Column = 7 Then
Select Case Target.Row
    Case 12 To 18, 26 To 32  'check if target row is 12 to 18 (including) or between 26 to 32 (including)
        Hours = Target.Value
        If Hours <> 0 Then
            If Hours > 8 Then
                Target.Value = 8
                Target.Offset(0, 1).Value = Hours - 8
                Exit Sub
            Else
                If Hours < 8 Then
                    Target.Offset(0, 2).Value = 8 - Hours
                End If
                Exit Sub
            End If
        End If
End Select
End If
End Sub

and

Sub Clear()
Range("B12:E18").Select
Selection.ClearContents
Range("G12:J18").Select
Selection.ClearContents
Range("B26:E32").Select
Selection.ClearContents
Range("C9").Select
Selection.ClearContents
MsgBox ("Content cleared")
End Sub

When I call the second one I get a Run time error 13 Type mismatch error at If Hours <> 0 Then The same happens if I manually clear the range. How can I prevent this?

score 0 · Accepted Answer · edited May 23 '17 at 12:15

When multiple cells are changed at the same time (such as when you run your Clear sub), the Target argument to Worksheet_Change is a range consisting of all of the changed cells - the .Value of that range is a 2-D array of values, not a single value, so you cannot compare it directly with a single value (eg to test whether it is zero or not).

The simplest way around this is to exit when Target is >1 cell.

Private Sub Worksheet_Change(ByVal Target As Range)
' check if target is in Column B or Column G

If Target.Cells.CountLarge > 1 Then Exit Sub  '<<<<<<<<<<<<<<

If Target.Column = 2 Or Target.Column = 7 Then
Select Case Target.Row
    Case 12 To 18, 26 To 32  'check if target row is 12 to 18 (including) 
                             ' or between 26 to 32 (including)
        Hours = Target.Value
        If Hours <> 0 Then
            If Hours > 8 Then
                Target.Value = 8
                Target.Offset(0, 1).Value = Hours - 8
                Exit Sub
            Else
                If Hours < 8 Then
                    Target.Offset(0, 2).Value = 8 - Hours
                End If
                Exit Sub
            End If
        End If
End Select
End If
End Sub

Also this is a simpler way to handle clearing your ranges:

Sub Clear()

    Range("B12:E18, G12:J18,B26:E32, C9").ClearContents
    MsgBox ("Content cleared")

End Sub

Obligatory: How to avoid using Select in Excel VBA macros

Hiding error message

1 Answers1