Thread: Linking DropDown box options to worksheets

Hi all,

Sorry if this has been asked before, I've done some research online and I couldn't find a solution, so I thought I'd ask myself!

Basically I have an intro page for a spreadsheet, with a DropDown box (couldbe Combo is thats easier) I need to have a series of options within the DropDown box, that when you click on it, links you to sheet within the workbook.

So far I have:

Code:

Private Sub DropDown14_Change()
  
Select Case Range("AR86:AR105").Text

Case "Option1t"
Range("H:\Docs\CV Tracking.xlsx#sheet3!A1").Select

Case "Option2"
Range("H:\Docs\CV Tracking.xlsx#sheet4!A1").Select


End Select
End Sub

but I've also tried:

Code:

Private Sub ComboBox1_Click()
    ComboBox1.DropDown
End Sub

Private Sub Userform_Initialize()
    ComboBox1.AddItem "Option1"
    ComboBox1.AddItem "Option2"
    ComboBox1.AddItem "Option3"
    ComboBox1.AddItem "Option4"
    ComboBox1.AddItem "Option5"
End Sub


Private Sub ComboBox1_Change()
  Select Case ComboBox1.Value

Case "Option1"
Range("H:\Docs\CV Tracking.xlsx#sheet6!A1").Select

Case "Option2"
Range("H:\Docs\CV Tracking.xlsx#sheet21!A1").Select


End Select
End Sub

but I've had no luck with either! Can anyone help?

Thanks,

Chris

Welcome to the Forum chris2886.

However, your post does not comply with the Forum Rules you agreed to follow. Per Rule #3, all VBA code must be wrapped in code tags. Please edit your post to add the code tags, after which solutions to your question will be suggested.

Rule #3

Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button. For more information about these and other tags, click here.

You may find this link helpful: Creating a Index Worksheet

Try adapting the code and use it in a validation drop down instead of a control from Forms or Control Tool Box.

Code:

Private Sub Worksheet_Activate()

Dim wSheet As Worksheet

Dim l As Long

l = 1



    With Me

        .Columns(1).ClearContents

        .Cells(1, 1) = "INDEX"

        .Cells(1, 1).Name = "Index"

    End With

    

    For Each wSheet In Worksheets

        If wSheet.Name <> Me.Name Then

            l = l + 1

                With wSheet

                    .Range("A1").Name = "Start_" & wSheet.Index

                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _

                    SubAddress:="Index", TextToDisplay:="Back to Index"

                End With

                

                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _

                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name

        End If

    Next wSheet

End Sub