Excel Dropdown : List Worksheets ?!?

jibbajabbajibbajabba Member Posts: 4,317 ■■■■■■■■□□
I have 100+ Worksheets in a document and apparently it is a pain in the neck to browse through them :D

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 ?
My own knowledge base made public: http://open902.com :p

Comments

  • Node ManNode Man Member Posts: 668 ■■■□□□□□□□
    jibbajabba wrote: »
    I have 100+ Worksheets in a document and apparently it is a pain in the neck to browse through them :D

    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.
  • DevilWAHDevilWAH Member Posts: 2,997 ■■■■■■■■□□
    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 :)
    • If you can't explain it simply, you don't understand it well enough. Albert Einstein
    • An arrow can only be shot by pulling it backward. So when life is dragging you back with difficulties. It means that its going to launch you into something great. So just focus and keep aiming.
  • DevilWAHDevilWAH Member Posts: 2,997 ■■■■■■■■□□
    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 ;)
    • If you can't explain it simply, you don't understand it well enough. Albert Einstein
    • An arrow can only be shot by pulling it backward. So when life is dragging you back with difficulties. It means that its going to launch you into something great. So just focus and keep aiming.
Sign In or Register to comment.