Results 1 to 3 of 3
2012-02-12, 22:03 #1
- Join Date
- Jun 2001
- New York, New York, Lebanon
- Thanked 1 Time in 1 Post
Detect if the user has moved horizontally on a worksheet
I am writing an application where I need to filter a list anytime the user picks an item from the list.
But I need to reestablish the original list if the user goes to a new column.
In Column A, from A3 to A15, I have a data validation list that I control via VBA. Each time the user picks an item from the list, that item is removed from the list thereafter. So the user is on cell A5, and the user chooses a value from the data validation list say Tuesday, then from A6 through A15 the user will not have a Tuesday to pick from the list, because my code will have taken out from the validation list.
My problem is that when the user picks B3, I need the whole data validation list reset, including Tuesday so that the user will be able to choose Tuesday because that is OK, but the user can not pick the same entry from the data validation list twice in the same column. I looked into the Selection_Change event, but I am not sure if it will work.
How can I detect that the user has moved from say A15 to B3, that is from column A to column B?
I hope this is clear enough.
Thanks a million
Wassim<img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>
2012-02-13, 04:55 #2
- Join Date
- Jan 2004
- Thanked 113 Times in 98 Posts
You can use the event Private Sub Worksheet_SelectionChange(ByVal Target As Range).
Use a test such as If Target.Column [test] Then . . . to find out which column contains the new active cell, and act accordingly.
You have to be careful not to get into unwanted loops when you are using Worksheet events and it may be a good idea to turn off events with Application.EnableEvents = False whilst the event procedure is running.
2012-02-13, 07:29 #3
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 1,510 Times in 1,371 Posts
Here's some code to get you started:
In a Module:
Option Explicit Public rngLastSelected As Range Sub Auto_Open() Set rngLastSelected = ActiveCell 'Capture starting cell End Sub
Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) '*** Limiting the Worksheet_Change event to a firing when a single cell is changed Dim isect As Range Dim lLastCol As Long Dim lCurCol As Long lLastCol = rngLastSelected.Column lCurCol = Target.Column If lLastCol <> lCurCol Then MsgBox "Selected Column has changed!", _ vbOKOnly + vbInformation, _ "Notification of Change" Set rngLastSelected = Target End If End Sub