bfreescott
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
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(
'
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER