Excel Dropdown : List Worksheets ?!?

I have 100+ Worksheets in a document and apparently it is a pain in the neck to browse through them

Is there a way to simply add a drop down to the first Worksheet simply listing the names of all the other ones so you can quickly jump to the one needed ?

Find more posts tagged with

Free for TechExams community: Cybersecurity salary guide

Compare cert salaries and plan your next career move

Button

Comments

Node Man

jibbajabba wrote: »

I have 100+ Worksheets in a document and apparently it is a pain in the neck to browse through them

Is there a way to simply add a drop down to the first Worksheet simply listing the names of all the other ones so you can quickly jump to the one needed ?

i teach Excel. There may be a way to do your idea, but it wont be easy. Or at least beginner or intermediate level. I am thinking you may need to code some VBA.

DevilWAH

Well the logic is simple and you can find the code examples on the internet.

First add the name of each sheet to an array...

[COLOR=#333333]Sub sheetray()[/COLOR]
    Dim SNarray, i    ReDim SNarray(1 To Sheets.count)    For i = 1 To Sheets.count        SNarray(i) = ThisWorkbook.Sheets(i).NameDebug.Print SNarray(i)    Next [COLOR=#333333]End Sub[/COLOR]

you can inset an active x List box from the menu ribbon and then go in to its proprieties and assign the array created above to populate the list box (or do it via some simple VBA code)

then the final bit of code you assign to the list bock action to open the sheet

[COLOR=#00008B][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] activateSheet[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]([/FONT][/COLOR][COLOR=#000000][FONT=Consolas]sheetname [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]As[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]String[/FONT][/COLOR][COLOR=#000000][FONT=Consolas])[/FONT][/COLOR]
[COLOR=gray]'activates sheet of specific name[/COLOR]    Worksheets(sheetname).Activate [COLOR=#00008B][FONT=Consolas]End[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]Sub[/FONT][/COLOR]

where "sheetname" is thee variable pulled from the list box choice..

so not to hard if you know a bit of VBA, and if not it might be a nice project to try out.

If not and if no one else posts the full code I will look at it later for you

DevilWAH

OK so a bit more.

First create this module in the "This Workbook" section in the VBA editor

Private Sub Workbook_Open()    
    Dim SNarray, i
    ReDim SNarray(1 To Sheets.Count)
    For i = 1 To Sheets.Count
    SNarray(i) = ThisWorkbook.Sheets(i).Name
    Debug.Print SNarray(i)
    Next
    Sheet1.ComboBox1.List = SNarray
    End Sub

Second create and Active X combo box on sheet one (note this has to be the sheet named "Sheet 1" in the VB editor"

Third Under the "Sheet 1" section of the VB editor add the following code

Private Sub ComboBox1_Change()
Worksheets(Sheet1.ComboBox1.Value).Activate
End Sub

Now each time you open the workbook it will populate the combo box with the sheet names.
selecting one will move to that sheet.

could be tidyer and neater, you could create the combo box from the code is you wanted. but I will leave it to you to play with. Oh and please play with a test work box or a copy before you put it in the live one