Drop Down list & Goto

[ebrehm]

I am struggling!!! I would like to have a drop down form, linked to cell "E7" and based on the value in E7 the page would jump to a certain cell.
This drop down list has 9 options.

Here is the current code I have in the "Sheet" (not in a module) and it is not working. Nothing happens when I change the drop down form choice.
Please help - anyone!
Thank you in advance.
Erica



Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Range("$E$7").Value = Target.Value

If Target.Value = 1 Then
Application.Goto Reference:="A13"
If Target.Value = 2 Then
Application.Goto Reference:="A370"
If Target.Value = 3 Then
Application.Goto Reference:="A596"
If Target.Value = 4 Then
Application.Goto Reference:="A822"
If Target.Value = 5 Then
Application.Goto Reference:="A1048"
If Target.Value = 6 Then
Application.Goto Reference:="A1274"
If Target.Value = 7 Then
Application.Goto Reference:="A1500"
If Target.Value = 8 Then
Application.Goto Reference:="A1726"
If Target.Value = 9 Then
Application.Goto Reference:="A1952"


End If
End Sub

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$7" Then
    Select Case Target.Value
        Case 1: Application.Goto Reference:="A13"
        Case 2: Application.Goto Reference:="A370"
        Case 3: Application.Goto Reference:="A596"
        Case 4: Application.Goto Reference:="A822"
        Case 5: Application.Goto Reference:="A1048"
        Case 6: Application.Goto Reference:="A1274"
        Case 7: Application.Goto Reference:="A1500"
        Case 8: Application.Goto Reference:="A1726"
        Case 9: Application.Goto Reference:="A1952"
    End Select
End If
End Sub

 

[ebrehm]

Thank you for your feedback. I tried your suggestion and NOTHING is happening. Not a thing. It's as if there is no code. Do I have my code in the right place -- not in a module, but in the "sheet" area???

Thanks - Erica

 

[VoG]

Save your work. Exit Excel. Restart Excel and try again.

 

[ebrehm]

I followed your suggestion and still nothing.

Thanks though.

 

[VoG]

Are macros enabled?

 

[ebrehm]

Yes. Other macros are working.

 

[VoG]

Are you changing values or just changing the selection?

 

[ebrehm]

I have a "user form" box. I use the box as a drop down choice. After the choice is make the cell E7 is linked to the choice with a number value 1-9. So the actual cell value for "E7" is changing on every choice.

 

[ebrehm]

Ok .... based on your last comment, I had a thought... maybe this isn't working since the "user" isn't directly changing the cell value. So I manually typed in the value and it works fine.
Hmmm.... now how can i get this to work with the user form???
I don't even care if I have to completely change this. I just want the users to have the ability to choose from a drop down list and then "goto" a certain cell for 9 different options.

Thank you - Erica