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

`Target.Offset(,-1).Value = 0` will fail if `Target` is a multi-cell range, so nest that `If` instead of using `And`. — BigBen, Apr 19 '20 at 00:53
Also, safer to use `CountLarge` instead of `Count`, and you can just drop the `Cells`: `Target.CountLarge = 1`. — BigBen, Apr 19 '20 at 01:21
Good to know, thanks https://learn.microsoft.com/en-us/office/vba/api/excel.range.countlarge — The shadow, Apr 19 '20 at 01:28

VBasic2008 · Accepted Answer · 2020-04-19T06:33:06.193

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

Worksheet_SelectionChange Returns an error if the condition is false

1 Answers1

Selection Change Disaster