CODEWITHSUNDEEP
excelvba
Simon.626
Simon.626

Reputation: 83

Macro triggered by cell selection

I have a macro 'Edit_Row' that brings the values from the active row into a UserForm for editing. This macro is called by another macro, which is triggered by selecting the cell in column B of the table for the appropriate row:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
    If Not Intersect(Target, Range("B3:B9999")) Is Nothing Then
        Call Edit_Row
    End If
End If
End Sub

This works as intended, except for the fact that this macro is also activated any time a sort is done on the table. Is there something else that I can do to achieve the same result (without these false positives)?

Upvotes: 1

Views: 452

Answers (1)

David Zemens
David Zemens

Reputation: 53663

Assuming your table's Range includes the headers in Row 3, try something like this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tbl As ListObject
Set tbl = Me.ListObjects("Table1")  'Modify using your table's Name

If Target.Cells.Count <> 1 Then Exit Sub

If Not Intersect(Target, tbl.DataBodyRange.Columns(2)) Is Nothing Then
    Call Edit_Row
End If

End Sub

Note: If your table doesn't begin in column A, then modify .Columns(2) to .Columns(1).

The way this should work is that it's checking whether Selection is part of the table's DataBodyRange, which excludes the header row.

Upvotes: 1

Related Questions