Reputation: 27

How to jump to a specific cell based on the list selection

I have created a drop-down list in cell R5 containing names, lets call them Name1 Name2 Name3. I'd like when the user selects a certain name the sheet will scroll down to a specific row. For instance, if Name 1 is selected I'd like it to go to row 2, if Name2 is selected row 10, and Name3 row 18. The list is on the same worksheet as the data I'm wanting to scroll to. Is there some code I can use to do this?

Upvotes: 0

Answers (4)

Vempati Satya Suryanarayana

Reputation: 337

Right click on the cell. At bottom of the popped up window you find "Hyperlink". Click it. Another window opens. There select the sheet and enter the cell number where you want to go. That's all. In this cell the address given under hyperlink appears. If you give a name to it that name appears. Thereafter whenever you click on this cell/name in this sheet you go to the cell in the sheet specified under "Hyperlink". You can enter data in the new place. But you won't be able to come back to this cell on pressing enter. When you press "Enter" in the new place you go to the next cell in that sheet as usual. I used another hyperlink to come back. It is working for me. I Hope this is a facility provided by excel for jumping easily to a new location based on the hyperlink. Hope there won't be any cascading effect. I hope this is exactly what you wanted.

Upvotes: 0

ArcherBird

Reputation: 2134

You would need to use Sheet Events to handle this. Something like this:

In your Worksheet Module of the worksheet that has your input range, put this code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim InputRange As Excel.Range
    Set InputRange = Me.Range("R5")

    '// Check if the change is happening in your dropdown cell
    If Not Intersect(Target, InputRange) Is Nothing Then
        Select Case InputRange.Value
            Case "Name1"
                Application.ActiveWindow.ScrollRow = 2
            Case "Name2"
                Application.ActiveWindow.ScrollRow = 10
            Case "Name3"
                Application.ActiveWindow.ScrollRow = 18
            Case Else
                '//...
        End Select
    End If
End Sub

Edit:

If you're having trouble getting this to work. Try adding a breakpoint by clicking in the area to the left of the code. A breakpoint will halt execution when the flow of code reaches that point. This is one way to figure out if Excel is even TRYING to run this block of code.

Debugging Excel Code

Upvotes: 1

Gary's Student

Reputation: 96753

Say we put a little jump table in columns S and T like:

The row numbers are in column T. We put the drop-down in R5 and the following code in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R5 As Range, v As String, r As Range
    Set R5 = Range("R5")
    If Intersect(Target, R5) Is Nothing Then Exit Sub
    v = R5.Value
    Set r = Range("S:S").Find(what:=v, After:=Range("S1"))
    Application.Goto Range("A" & r.Offset(0, 1).Value)
End Sub

Whenever the user picks a new name in cell R5, the code will jump to the row listed in column T.

Because it is worksheet code, it is very easy to install and automatic to use:

right-click the tab name near the bottom of the Excel window
select View Code - this brings up a VBE window
paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro: