Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    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

    Hi All,

    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.

    For example:

    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

    <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>

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Thanked 129 Times in 113 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.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,608 Times in 1,452 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
    In the Sheet Module:
    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
    Hope this helps.
    Attached Files Attached Files
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts