CODEWITHSUNDEEP
excelvba
Leedo
Leedo

Reputation: 611

Stop firing event SelectionChange (Intersect already used) if it intersects with another range

I am using the code below to run a macro Calendar_Advanced if any cell is selected in column M.

Problem: If I select any cell in the same time with Range M:M , the event also fires, like if I selected all row.

Private Sub worksheet_SelectionChange(ByVal Target As Excel.Range)

    Dim LastRow As Long: LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
      
    If Not Intersect(Target, Range("M3:M" & LastRow)) Is Nothing Then
        Call Calendar_Advanced
    End If
End Sub

I tried to add to the If condition And Selection.Cells.Count = 1.

It works, but prevents multi selection (Calendar_Advanced) to run on column M.

Upvotes: 0

Views: 111

Answers (1)

Warcupine
Warcupine

Reputation: 4640

Count the columns to make sure it is just M.

Private Sub worksheet_SelectionChange(ByVal Target As Excel.Range)

    Dim LastRow As Long: LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    If Not Intersect(Target, Range("M3:M" & LastRow)) Is Nothing Then
        If Target.Columns.Count = 1 Then
            Calendar_Advanced
        End If
    End If
End Sub

Upvotes: 3

Related Questions