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
                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
                        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
                        If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With