Navigate Via Drop Down List.

[VinceF]

Hello Experts,

Hopefully this is an easy one for the experts.
I created a drop down list and I would like to be able to navigate the sheets contained in that list which are in the workbook.
I suppose the cleanest way would be to you VBA...doing so is over my pay grade...help please.

VinceF
Excel 2016
Win10

 

[VinceF]

My apologies for the mess...I'm doing the best that I can with my limited skills. Something that is most likely easy for advanced Excell users is complex for novices like me.

When you post code I recommend that you select the code and then click the VBA button in the editor...sorry for the copy and paste again but I wasn't able to do as you recommended.



When I put this code in I get a debug error message "Compile error, invalid outside procedure.


VBA Code:
Option Explicit

' !!! Note how indentation is preserved when using code tags!!!
Private Sub Worksheet_Change(ByVal Target As Range)

Const VALIDATION_CELL = "B2" '<== change validation cell as needed.

If Target.Address = Range(VALIDATION_CELL).Address Then
Sheets(Target.Value).Select
End If

End Sub




Private Sub CommandButton1_Click()

Dim warning
warning = MsgBox(Range("A1").Value & " WARNING !!!! WARNING !!! WARNING !!! This will reset the entire sheet. Select OK to continue or select CANCEL to continue without resetting", vbOKCancel, "Warning")
If warning = vbCancel Then Exit Sub

On Error Resume Next
Sheets("main").Range("b2").Value = "NAVIGATION"
Sheets("main").Range("d2").Value = "TEAM GEN"
Sheets("main").Range("E2").Value = "HISTORY"
Sheets("main").Range("F2").Value = "TWO PLUS"

 

[6StringJazzer]

Do not add the following line in your code. It is a label when code is displayed in a post; it is not part of the code. That is causing the error.

VBA Code:
_________________________________
To post code with code tags:

Paste your code into a post
Select the code
Click the edit button that says VBA

See below:

 

[VinceF]

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

Const VALIDATION_CELL = "B2"

If Target.Address = Range(VALIDATION_CELL).Address Then
Sheets(Target.Value).Select
End If

End Sub






Private Sub CommandButton1_Click()

Dim warning
warning = MsgBox(Range("A1").Value & "    WARNING !!!! WARNING !!! WARNING !!! This will reset the entire sheet.  Select OK to continue or select CANCEL to continue without resetting", vbOKCancel, "Warning")
If warning = vbCancel Then Exit Sub

On Error Resume Next
Sheets("main").Range("b2").Value = "NAVIGATION"

Do not add the following line in your code. It is a label when code is displayed in a post; it is not part of the code. That is causing the error.

VBA Code:
_________________________________
To post code with code tags:

Paste your code into a post
Select the code
Click the edit button that says VBA

See below:

View attachment 61931

Do not add the following line in your code. It is a label when code is displayed in a post; it is not part of the code. That is causing the error.

VBA Code:
_________________________________
To post code with code tags:

Paste your code into a post
Select the code
Click the edit button that says VBA

See below:

Do not add the following line in your code. It is a label when code is displayed in a post; it is not part of the code. That is causing the error.

VBA Code:
_________________________________
To post code with code tags:

Paste your code into a post
Select the code
Click the edit button that says VBA

See below:

View attachment 61931

Jeff,
THANK YOU for your patience. Thanks to your advice it is working as I had hoped. However when I run the code to reset the sheet I now get a "runtime error 9" highlighting in yellow "Sheets(Target,Value).Select"
I hope that I posted the code correctly this time... any suggestions on how to get rid of this error message?
Thanks again...

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

Const VALIDATION_CELL = "B2"

If Target.Address = Range(VALIDATION_CELL).Address Then
Sheets(Target.Value).Select
End If

End Sub

 

[6StringJazzer]

Hopefully that's a typo and your real code uses a period . and not a comma , there.

This is a "subscript out of range" error and means that there is no sheet named Target.Value. What is the value of Target.Value at the time the error occurs, and is there a sheet with that name? When an error occurs, and you hit Debug, and you see a highlighted yellow line of code, you can hover your cursor over a variable name to see its value. If that does not work, hit CTRL+g to open the immediate window and type

?Target.Value

in that window to query the value.

If you think there is a sheet with that name, check that the value in the cell doesn't have extra spaces at the beginning or end, and same for the sheet name on its tab.

 

[VinceF]

Jeff...I thank you again.
I had misspelled a name in the list. I'm happy to report everything is working fine and thanks for the tip and explanation, with the help of you and the others I learn something new about this program every day.

VinceF