Worksheet_SelectionChange Returns an error if the condition is false

Question

I try to bounce a Form if the selection is in column 4 and only 1 cell is selected and the cell next in column 3 is empty

If the condition is true it works

But if the condition is false, an error comes out:

run time error '13': type mismatch,

On the if line

That's the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 4 And Target.Cells.Count = 1 And Target.Offset(, -1).Value = 0 Then

    C = Target.Offset(, -1).Address
    UserForm1.Show

End If

End Sub

Answer 1

Selection Change Disaster

The Two to Three Issues

You should use CountLarge instead of Count. If you select too many cells, Count will cause Run-time error '6': Overflow. Try it by selecting all cells on the worksheet (CTRL+A).

If you select a range in column A, the impossibility of calculating Target.Offset(, -1) will cause Run-time error '1004': Application-defined or object-defined error.

If multiple cells are selected then Target.Offset(,-1).Value will result in an array which can not be compared to 0 which will cause Run-time error '13': Type mismatch.

A possible solution

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const ColumnNumber As Long = 4
    Const ColumnOffset As Long = -1
    Const OffsetValue As Long = 0

    Dim C As String

    With Target
        If .Column + ColumnOffset < 1 Or .Cells.CountLarge > 1 Then Exit Sub
        If .Column = ColumnNumber And .Offset(, ColumnOffset) = OffsetValue Then
            C = Target.Offset(, ColumnOffset).Address
            UserForm1.Show
        End If
    End With

End Sub