Using drop down menu to "go to" tab

K

keeper

Is there a way to use a drop down menu to select the tab and then go to it?
I'm trying to make this worksheet as user friendly as possible and there are
too many tabs to search through and being able to select it from a drop down
menu would be easier.
Possible?
 
A

Ardus Petus

I have home-made XLA that should help: http://cjoint.com/?fwqZQdNgus
It is not protected: you can view & edit the code and the userform

To have tabs alpha ordered, right click on arrows left of tabs list, and
select "Sort tabs"

To select a sheet:
Right-click on a cell and select "Select sheet"
You can type the beginning of the name: it will select corresponding entry
of the list.
To validate: press Enter or double-click on a name
To cancel: press Escape

HTH
 
P

Pete_UK

Another way to do this is to have a "Contents" sheet which lists all
the sheets in the workbook and against them have a hyperlink to jump to
the appropriate sheet - in each of the other sheets you can have a
hyperlink which will jump the user back to the Contents sheet.

Hope this helps.

Pete
 
B

Bob Phillips

If you right-click on the navigation arrows next to the sheet tabs, it
display a list of sheets.

Another way is to add a commandbar dropdown like this


In the ThisWorkbook code module, add this code

Dim AppClass As New clsAppEvents

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call removeCommandBars

End Sub

Private Sub Workbook_Open()
Dim sh As Object

With Application.CommandBars("Formatting")
With .Controls.Add(Type:=msoControlDropdown, temporary:=True)
.BeginGroup = True
.Caption = "SheetList"
.OnAction = "SheetGoto"
For Each sh In ActiveWorkbook.Sheets
.AddItem sh.Name
Next sh
End With
End With

Set AppClass.App = Application

End Sub

Then, add a class module and call it clsAppEvents, and add this code

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
Dim sh As Object
With Application.CommandBars("Custom Toolbar").Controls("Goto Sheet")
.Clear
For Each sh In Wb.Sheets
.AddItem sh.Name
Next sh
.ListIndex = 1
End With
End Sub

And finally, in a standard code module, add

Private Sub SheetGoto()
With Application.CommandBars.ActionControl
ActiveWorkbook.Sheets(.Text).Select
End With
End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Top