SpecialCells causing SheetSelectionChange event in Excel 2010

Question

I have a test Macro

Sub test()
    Dim rSrcMatrix As Range
    Set rSrcMatrix = Sheets("Code Matrix").Range("Xfer_To_Xfer_Matrix").Range("A1")
    Set rSrcMatrix = rSrcMatrix.Resize(rSrcMatrix.SpecialCells(xlCellTypeLastCell).Row, rSrcMatrix.SpecialCells(xlCellTypeLastCell).Column)     
End Sub

I am using this macro to test my COM addin that I have created in VS2010. I have delegated the SheetSelectionChange event in the addin to some function.

Now I notice that whenever I run this macro, Excel fires the SheetSelectionChange event 4 times and my addin calls the associated method for that many times.

Is there anything that I am missing or is this a bug in excel?

Answer 1

I believe and I could be wrong because I couldn't find an MSDN article to prove it but SpecialCells performs a type of selection and triggers the Worksheet_SelectionChange or the Workbook_SheetSelectionChange event and hence you need to switch off events.

Here is a simple way to test it.

Place this code in the Sheet Code Area

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "Damn! The SpecialCells caused me to pop up!!!"
End Sub

Sub test()
    Debug.Print ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
End Sub

Worksheet_SelectionChange and Workbook_SheetSelectionChange do the same job. Worksheet_SelectionChange is used in the sheet code are for a specific sheet. And Workbook_SheetSelectionChange is used when you want the event to fire across all the sheets in that workbook.

YOUR QUESTION FROM THE COMMENT: What if we wanted to associate another event with that line of code. In that case, we cannot suppress the event.

Now, we have two alternatives. Based on your above question we cannot use Alternative One. So you may directly skip to Alternative 2

ALTERNATIVE 1

Switch Off Events

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    '
    '~~> YOUR CODE
    '

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

ALTERNATIVE 2

Instead of using SpecialCells to find the last row or the last column, we will use .Find.

Sub test()
    Dim ws As Worksheet
    Dim rSrcMatrix As Range
    Dim Lrow As Long, LCol As Long

    Set ws = ThisWorkbook.Sheets("Code Matrix")

    With ws
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            Lrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row

            LCol = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Column
        Else
            Lrow = 1
        End If

        Set rSrcMatrix = .Range("Xfer_To_Xfer_Matrix").Range("A1")
        Set rSrcMatrix = rSrcMatrix.Resize(Lrow, LCol)

        Debug.Print rSrcMatrix.Address
    End With
End Sub