Drop down list linked to Worksheets

[shakeregg]

Hey all,

If someone could help me with this I would really appreciate it.

I have a workbook (named Database) which contains 4 worksheets named Main, Leeds, Bradford, York. Ideally I would like to create a drop down list in Main which lists the other worksheets which when selected will automatically take me to that worksheet.

i.e.

Select Cluster
- - - - - - - - -
Leeds
Bradford
York


Would it also be possible to hide the Leeds,Bradford& York worksheets so thy don't appear as tabs?

Cheeeeeeeeers in advance

 

[My Aswer Is This]

I'm a little confused about your question.
You said:

I have a workbook (named Database) which contains 4 worksheets

But then you said:

Ideally I would like to create a drop down list in Main which lists the other worksheets

What other worksheets?
You said you only had 4 and gave the names of 4 worksheets
​

_{^{<strike>
</strike>}}​

Then you said:

Would it also be possible to hide the Leeds,Bradford& York worksheets so thy don't appear as tabs?

Well if you only have 4 sheets if you want to hide 3 why would you need a drop down list​

Or all you saying you have many sheets and do not want four of them in the Drop down list.


And when you say Drop Down List

I assume you mean Data Validation list

Excel has nothing named Drop Down List

And where do you want this Data Validation List

Would it be sheet named Main

Starting in range A1

 

[shakeregg]

Cheers for responding. Hopefully below will answer your questions.

* I have a workbook (named Database) which contains 4 worksheets

But then you said:
Ideally I would like to create a drop down list in Main which lists the other worksheets

What other worksheets? ***** THE THREE OTHERWORK SHEETS LEEDS, BRADFORD & YORK ****

You said you only had 4 and gave the names of 4 worksheets - ****** YES THAT IS CORRECT *****

Then you said:
Would it also be possible to hide the Leeds,Bradford& York worksheets so thy don't appear as tabs?

Well if you only have 4 sheets if you want to hide 3 why would you need a drop down list - ***** BECAUSE I WOULD LIKE THEM TO APPEAR IN A DROP DOWN LIST RATHER THAN A TAB *****


Or all you saying you have many sheets and do not want four of them in the Drop down list - ***** NO THERE ARE FOUR SHEETS NAMED ABOVE *****


And when you say Drop Down List

I assume you mean Data Validation list

Excel has nothing named Drop Down List - ***** YES DATA VALIDATION OR COMBOLIST...... WHATEVER BRINGS A LIST IN DROP DOWN FORMAT *****

And where do you want this Data Validation List

Would it be sheet named Main - **** YES *****

Starting in range A1 - ***** B6 PLEASE *****

Cheers again

 

[My Aswer Is This]

I'm not able to build you a Data Validation List and then assign that list to B6

See if this would work.

Put your sheet names in Range("A1") Range("A2") Range("A3") and range("A4")

Then install this script.

Now when you click on the sheet name you will be taken to that sheet.

If this work for you then let me know what four cells you want to put the sheet names in that would work better for you.

Or if you do not like this plan maybe someone else here on the forum will be able to help you.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  11/3/2018  11:55:13 PM  EDT
If Not Intersect(Target, Range("A1:A4")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Sheets(Target.Value).Activate
End If
End Sub

 

[shakeregg]

Thank you again for responding I really appreciate it. Unfortunately I am after it in a data validation/ combo box format so hopefully someone else will be able to assist.

Cheers again though!

 

[My Aswer Is This]

So if you want to use a Activex combobox tell me what sheet names you do not want loaded into The Combobox.

So we can write a script to load all the sheet names into the combobox. Except for those you want excluded.
Then when you choose a value from the combobox you would be taken to that sheet.

 

[shakeregg]

Perfect..... All the worksheets previously mentioned which are Leeds, Bradford & York. The combobox will be in the worksheet “main” so this won’t be included.

The next step would be to make the three worksheets (Leeds, Bradford and York) not visible where the tabs are. I only want them to be visible in the combobox.

 

[My Aswer Is This]

Try this:

Private Sub CommandButton1_Click()
'Modified  11/4/2018  3:13:44 AM  EST
    With Sheets("Main").ComboBox1
        .Clear
        .AddItem "Leeds"
        .AddItem "Bradford"
        .AddItem "York"
    End With
Sheets("Leeds").Visible = False
Sheets("Bradford").Visible = False
Sheets("York").Visible = False

End Sub

 

[My Aswer Is This]

Now you never said what you want to happen after the sheets are hidden.
So if you want to click on a sheet name in the Combobox to unhide the sheet
Try this script in the combobox:

Private Sub ComboBox1_Click()
'Modified  11/4/2018  3:27:01 AM  EST
Sheets(ComboBox1.Value).Visible = True
End Sub

 

[shakeregg]

Thank you................ nearly there I think!

Seems to be working apart from when I select the worksheets on the ComboBox it reveals them on the tabs but doesn't automatically direct you to it. Also is there a way to make the tabs be hidden again once after you've selected that worksheet but then go back to the "Main" worksheet? For example, at the moment if I select Leeds from the ComboBox it appears but then remains visible in the tabs............. ideally it would be good if this could be hidden again.

Any further help would be appreciated.