Link to home
Start Free TrialLog in
Avatar of bfreescott
bfreescottFlag for United States of America

asked on

VBA Excel Target & Intersect

Hi All,

I have a little proc that runs on a change in the worksheet in front of it, but it's behaving badly.
The proc allows the user to place a checkmark in Column A when clicking a cell in that column or moving the cursor there via the keyboard.  There are a couple of problems with it:

1) If the user selects the entire row, the contents are deleted.
2) It does not play well with Filters.

If you see a glaring issue or have a better way to provide this ability, let me know.
Thank you!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
    Dim p As Integer
    Dim r As Range

    p = Cells(Rows.count, "B").End(xlUp).Row
    Set r = Range("A4:A" & p)
   
    If Not Intersect(Target, r) Is Nothing Then
        If Target.Text = "a" Then
            Target.Value = ""
        Else
            Target.Value = "a"
        End If
    End If
'
End Sub
ASKER CERTIFIED SOLUTION
Avatar of folderol
folderol

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfreescott

ASKER

like a charm!