Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Selection.Count overflow when selecting all cells

In Excel 2007 I want to prompt a message when a cell (L2) is clicked on. I have a piece of code that works but the problem is that when I select all the cells in the sheet with Ctrl+A I get error number 06: overflow on the line If Selection.Count = 1 Then

Mandatory VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("L2")) Is Nothing Then
            MsgBox "ACTION!"
        End If
    End If
End Sub
like image 264
Leo Avatar asked Oct 14 '14 12:10

Leo


2 Answers

Easily fixed:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge = 1 Then
        If Not Intersect(Target, Range("L2")) Is Nothing Then
            MsgBox "ACTION!"
        End If
    End If
End Sub
like image 197
Gary's Student Avatar answered Oct 02 '22 02:10

Gary's Student


Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        If InStr(1, CStr(Target.Address), ":") < 1 Then
            If Selection.Count = 1 Then
                If Not Intersect(Target, Range("L2")) Is Nothing Then
                    MsgBox "ACTION!"
                End If
            End If
        End If

End Sub
like image 38
Jur Pertin Avatar answered Oct 02 '22 01:10

Jur Pertin