Tom’s Tutorials for Excel: Sheet Selector Drop-Down List.
Although you can right-click the sheet navigation buttons at the left of the sheet tabs to display a list of sheets, where you can click a sheet name to select it, we have had requests from many developers to make it easier for their users to select worksheets. Instead of placing a button or embedded combobox on every sheet to produce a drop down list, here is a combobox that is at the top of the sheets, so it is always available, and only visible when the workbook is active.
A message box greets the users when the workbook opens, knowing if it is morning, afternoon, or evening, telling users about the combobox and how to easily switch to any visible worksheet. This is useful to avoid scrolling the sheet tabs when the workbook has dozens of sheets. The code only lists sheets that are visible, because a hidden worksheet cannot be activated.
In the above picture, the drop-down combobox is on the menu bar in Excel versions up to and including 2003, and on the Add-Ins tab of the Ribbon in versions 2007 and 2010. Here is the code and the steps to implement it. You can click the link at the top of this blog entry to download a zipped example workbook.
From any worksheet, go to the Visual Basic Editor (VBE) by pressing Alt+F11 (careful, that’s ALT+F11), then press Ctrl+R.
In the vertical pane on the left, find your workbook project and expand the folder named Microsoft Excel Objects.
Right-click on ThisWorkbook, left click to select View Code, and paste this code into the large white area that is the Workbook module:
Private Sub Workbook_Open() Run "ResetMenu" Run "MakeCBO" Dim TymeOfDay$ If Time < 0.5 Then TymeOfDay = "Good Morning !!" & vbCrLf & vbCrLf ElseIf Time >= 0.5 And Time < 0.75 Then TymeOfDay = "Good Afternoon !!" & vbCrLf & vbCrLf Else TymeOfDay = "Good Evening !!" & vbCrLf & vbCrLf End If MsgBox _ TymeOfDay & _ "To quickly and easily activate a sheet, select its name" & vbCrLf & _ "from the drop-down list on the menu bar in version 2003," & vbCrLf & _ "or on the Add-In tab in version 2007 or 2010.", 64, "Sheet navigation tip:" End Sub Private Sub Workbook_Activate() Run "ResetMenu" Run "MakeCBO" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Run "ResetMenu" ThisWorkbook.Save End Sub Private Sub Workbook_Deactivate() Run "ResetMenu" End Sub
From the menu at the top of the VBE, click Insert > Module and paste the following code into that new module:
Private Sub ResetMenu() On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Sheet selector").Delete Err.Clear End Sub Private Sub MakeCBO() With Application .ScreenUpdating = False Run "ResetMenu" Dim cboSheetz As CommandBarComboBox, ws As Worksheet With .CommandBars("Worksheet Menu Bar") Set cboSheetz = .Controls.Add(Type:=msoControlComboBox, before:=.Controls.Count) End With With cboSheetz .Caption = "Sheet selector" .OnAction = "mySheet" .Width = 100 End With For Each ws In Worksheets If ws.Visible = xlSheetVisible Then cboSheetz.AddItem ws.Name Next ws cboSheetz.ListIndex = 1 .ScreenUpdating = True End With End Sub Private Sub mySheet() With CommandBars("Worksheet Menu Bar").Controls("Sheet selector") Worksheets(.List(.ListIndex)).Activate End With End Sub
Press Alt+Q to return to the worksheets.
Close the file (it will automatically save itself) and anytime thereafter that you reopen the file or you activate it from another workbook, you will see the sheet selector combobox as shown in the picture. When you click a sheet name from that drop-down list, it will take you to that sheet.