Excel Query - Selecting a specific tab from a drop down box?

Question

Hi all you Excel wizards,

I have a spreadsheet that contains, say 26 tabs, each being named a different letter of the alphabet.
In the 27th tab I have a scroll down box, or a data validation list, that contains the names of all the other 26 tabs (A-Z).

Is there a way of selecting, for example, the letter 'T' from my drop down box and from this selection Excel will automtaically go to that tab? I have tried so many ways but to no avail. surely this can be done??
If anyone can be kind enough to send in the vb code, so that all I have to do is copy and paste it onto my spreadsheet that be would great!

Thank you in advance if you can indeed solve this one for me.

Answer 1

I solved your issue here
http://answers.yahoo.com/question/index?qid=20070312095519AAVQXhW

Enjoy it, and Trust me
I am the VBAXLMan

Answer 2

OK, here's how you do this. The code is pretty simple but you have to put it in the right place.

1 - Go to Tools, Macro, Visual Basic Editor
2 - You should see the project explorer on the left of the new screen (it has a tree with all the spreadsheets listed). If you do no see it go to View, Project Explorer
3 - Double click on the worksheet that has the validation. A new code module will open up.
4 - At the top of this code module, there are two drop down boxes. One says, (General) and the other says (Declarations). Change (General) to Worksheet.
5 - Change the right drop down to the option Change.
6 - Make your code look like this.
NOTE: My test sheet used A6 for the data validation. Change it to fit your sheet.

Private Sub Worksheet_Change (ByVal Target As Range)

Sheets (Range("A6").Value). Activate

End Sub

Answer 3

I can think of no formula method to go from a drop-down validation directly to another sheet. However, you can use your drop-down validation to cause a display to change based upon the selected value.

Then, based upon selected value, you can display a hyperlink formula like this one:

=HYPERLINK ("[Book1]Sheet4!E56", "Jump To Sheet Four!")

You really should read details on HYPERLINK function in Excel help.

Anyway, as I was explaining, if your validation listing is sheet names and user selects one from a list, the HYPERLINK formula can display the selection and function as a link to the sheet.

For example, selection validation in Sheet1!A1. The user selects sheet named "Apples".

Formula in Sheet1!B1:

=HYPERLINK("[Book1]"& A1 & "!E56", "Jump To Sheet " & A1 &"!")

What the user would see in Sheet1!B1

"Jump To Sheet Apples!"

Click on it and you jump to that sheet, "Apples" an cursor position on cell E56. Very quick!

I realize this isn't exactly what you asked for. Sometimes a solution is almost there but not quite! In this situation, I think this is best you can hope for using formulas. Sure, you could probably do a macro that displays the sheet names and then you choose it, but I don't know macros well enough to give that kind of advice.

Also, for speedier navication between sheets, try using keyboard combination + or

I hope this helps.
Kind Regards,
QwertyKPH @ Yahoo

Answer 4

Validations only work in the same sheet you are working in, At least in my experance. I tried to use a macro list box but you can only link one macro to it. You can create a button per letter and record a macro for it. VB macros are easy to "record" or write excel VB record every move you make and writes a macro for it. Or add a hyperlink per letter. A hyperlink to it , then B hyperlink to it.

Update:
Got a list to work. Here are the steps
1) Type A-Z in individual Cells ( Down in a column)
2)Add Hyperlinks for each Letter to correct sheet
-Highlight cell and select hyperlink
-select Place in this document and select correct sheet
3) Highlight A-Z Cells
4) Right Click and Select Create List

Answer 5

You mean a combo box ? In Design mode, right click on the combo box & then click on View Code. It will default to the Change event & you can enter code in here that will perform an action.