Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Move to cell after list selection! (Excel2000>)

    Hi all,
    Could I ask for some guidance regarding this query?

    In the attachment, there is a small list on entries (validation). If the user selects "Other", the selection must move to the Comments column and activate a message informing the user to write a comment in the cell.
    I assume it will be necessary to code this using a selection chage event. Is there any other method that will work more effectively as I don't want the workbook to have to trigger the event after every selection change. Is there a way to suppress selection change unless the cell value = "Other"

    Any advive will be useful and appreciated! Thanx.
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move to cell after list selection! (Excel2000>)

    Try this code for the Worksheet_Change (not SelectionChange) event. The code must go into the Sheet1 module.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("B3:B10")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("B3:B10"))
    If oCell = "Other" Then
    oCell.Offset(0, 5).Select
    Exit For
    End If
    Next oCell
    End If
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Move to cell after list selection! (Excel2000>)

    Hans, its working well. I will modified it to also prompt a msgbox to instruct the user to do something.
    Could you explain why you use the Worksheet change event, and the general dynamical of the code. How does the specific event and the event arguments do all this?
    Thanx
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move to cell after list selection! (Excel2000>)

    The Worksheet_Change event occurs when cells on the worksheet are changed by the user or by an external link. The Worksheet_SelectionChange event occurs when the selection changes on a worksheet, i.e. when the user moves to another cell.

    In this case, you want to react if the user selects "Other" in the dropdown list in a cell. This changes a cell value, so the Worksheet_Change event is the one to use. The Target argument of the event procedure is a range that represents all cells that have changed (although it is not probable in your worksheet, it is possible to change multiple cells to "Other" at the same time). First, we check if the changed cells overlap with B3:B10; if so, we check each changed cell in the overlap. If one of them has been changed to "Other", we jump 5 columns to the right and exit the loop.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Move to cell after list selection! (Excel2000>)

    Hi Hans,
    I copied the code to the genuine example, and it works great there too, except that the selection moves to the cell off the screen. How can we get the screen to scroll to the active cell to?

    If you consider my attachment sample. Say the comment must go into column L. Can we get the macro to move the screen so column L shows. At this moment it simply moves the selection and I have to scroll manually to see the selected cell!

    Any ideas?
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Move to cell after list selection! (Excel2000>)

    Ahh, i get it. Its amazing how simple it is when you just get a decent explanation. Now its a matter of storing this and being able to recall it when needed again!
    Thanx for sharing your knowledge!
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Move to cell after list selection! (Excel2000>)

    I found the solution in the object browser.
    Activewindow.scrollcolumn=12.

    Working great now....Thanx a stack Hans
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move to cell after list selection! (Excel2000>)

    On my system (Excel 2002 SP3), the active cell is automatically scrolled into view if it was offscreen. You could use these instructions to force the active cell to become visible:

    ActiveWindow.ScrollRow = ActiveCell.Row
    ActiveWindow.ScrollColumn = ActiveCell.Column

Posting Permissions

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