Select previous/next cell in a named range using buttons

    Hello Oz, I am looking for a VB script that will select/activate the cell below the last (most recently) selected cell in an excel table column.


    My excel table is named "offices_tbl" and the column I want to work with is called "office address".


    I.e. Range("offices_tbl[office address]")


    I am already using the worksheet change event to perform an action when a cell is selected within the column, as shown below, but I would like to add buttons to the page so users can increment the selection up/down.


    [VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Not Intersect(Target, Range("offices_tbl[office address]")) Is Nothing Then 'for cells not selected:
    Range("offices_tbl[office address]").Interior.ColorIndex = xlColorIndexNone ' set color to default/nothing

    With Range("offices_tbl[office address]") 'when a cell is selected do this:
    Range("F4").Value = ActiveCell.Value 'copy cell value to F4
    With Selection.Interior 'change interior cell colour to
    .Color = 6750207 'light yellow
    End With
    End With
    Application.Run "StartJob1" 'and run Job1
    End If
    End Sub[/VBA]


    Note: the user may have selected another cell in another column, so the current ActiveCell may not be within the target range when they press the button(s)!


    As always, any help/tips to accomplish this greatly appreciated!!!


    PS I am not looking for a spin button/toggle type solution at the moment, I have to use two buttons, one for up, one for down.

    Hello,


    Since you have two dedicated buttons to go up or down ... your event macro will remain totally independent ... :wink:


    Below are the two codes to be executed for your two buttons :


    Code
    Sub rwUP()
Dim rw As Long
ActiveCell.Offset(-1, 0).Select
End Sub


Sub rwDOWN()
Dim rw As Long
ActiveCell.Offset(1, 0).Select
End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi Carim, than you for replying. Unfortunately, your solution will move the active cell no matter where it is. I am trying to find a solution that will move the last selected cell in the target range Range("offices_tbl[office address]"). If there is no other method for identifying it, it could be identified by the fact that the last cell selected in the target range will be in italics and have an interior colour value of ".Color = 6750207" due to the Worksheet_SelectionChange event firing but I am hoping there is a simpler solution.

    Hello again,


    The two macros rwUP and rwDOWN can have their target area restricted to whatever range you select ...


    For example, you could add as the first line in each macro :


    Code
    If Intersect(ActiveCell, Range("offices_tbl[office address]")) Is Nothing Then Exit Sub


    Again, your Event macro Worksheet_SelectionChange() has no relationship with your objective : two buttons to move Up or Down ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re,


    Do not hesitate to share your comments ... :wink:


    Let me know if your problem is fixed ... or not ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi Carim, that's not quite what I'm looking for. My objective is to select the cell above, or below, the previously selected cell within the target range. It occurred to me that I might be able to do this by adding a line like [VBA]LastOffice = Target[/VBA] after [VBA]With Range("offices_tbl[office address]")[/VBA] in the Worksheet_SelectionChange event and I could then modify your code to be [VBA]LastOffice.Offset(1, 0).Select[/VBA] but it's not working. I tried making LastOffice global by adding [VBA]Global LastStation As String[/VBA] at the top of the module but that didn't help. Do you see what I'm getting at? Thanks again for your assistance! :)

    Hello,


    It is important to differentiate a user typing data in a sheet ... this action can trigger an event macro ... which runs only because the user is manually selecting another cell ...


    versus


    Two Buttons which launch macros .... they only get executed with the action of clicking on each button ...


    Hope this clarifies


    :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi Carim, you are correct, however, both processes will be occurring. Sometimes users will use the buttons to review offices sequentially, but at other times they may want to select offices at opposite ends of the list so they will skip the buttons and just select them manually.

    Hello,


    It seems to me you should be revisiting the title you have picked for your thread ... :wink:


    Why don't you attach your file with your next message ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hello,


    Have you eventually fixed your problem ...??? :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login