Tom’s Tutorials for Excel: Sheet Selector Drop-Down List.
Download example workbook: ComboBox_SheetSelector
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.
Step 1
From any worksheet, go to the Visual Basic Editor (VBE) by pressing Alt+F11 (careful, that’s ALT+F11), then press Ctrl+R.
Step 2
In the vertical pane on the left, find your workbook project and expand the folder named Microsoft Excel Objects.
Step 3
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
Step 4
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
Step 5
Press Alt+Q to return to the worksheets.
Step 6
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.
Hi, I’ve tried your code in Excel 2010, and ran into a major problem (for me). Quitting the workbook (either by upper right close button or file menu/close) saves all changes done in the workbook, without any kind of prompt for save changes.
In the Workbook_BeforeClose event, comment out (that is, enter an apostrophe immediately before), or simply delete, this code line:
ThisWorkbook.Save
thanks, Tom – it was that simple 🙂
I have been trying this code and it is exactly what i need. However, i am getting the error “Inavalid procedure call or argument.” When i click debug, it tells me the problem is in the line “Application.CommandBars(“Worksheet Menu Bar”).Controls(“Sheet selector”).Delete”
Any help is appreciated.
I am using excel 2013. Is this the issue? Is there anyway i can change the VBA to correct for this?
Not sure how to answer that, as I cannot duplicate the behavior you are seeing. Make sure you are putting the appropriate code in their respective modules as I wrote them.
One idea is to see what happens if you put this line…
On error resume next
…immediately before this line…
Application.CommandBars(“Worksheet Menu Bar”).Controls(“Sheet selector”).Delete
and then this line…
Err.clear
..immediately after that.
Hi Tom,
This is great! Is it possible to place the combobox in a custom tab I’ve created on the Ribbon, rather than the ‘Add-Ins’ tab?
Thanks,
Harry
Amazing. Perfect thank you. as harry mentioned though it would be nice to be able to place that module anywhere or even in the tabs menu
Thanks to you and Harry for your comments. I have to believe the code can be housed in a custom tab, not just as an add-in. Maybe one of those things I can get around to on a rainy day this coming winter.
Either Way it’s a great script. so useful on my workbook with 50+ sheets
Good Day Sir!!
It is really a helpful code and matched my exact requirement with 90+ worksheet.
Just have a small query. Can I show the sheet name alphabetically in drop down list? I hope it can be done by manually rearranging worksheets. But it is really a time consuming for 90+ worksheet in 3 different workbook.
Kindly suggest any way to achieve the same please.
Substitute the original MakeCBO macro with the one below, and see if it does what you want.
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
For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then cboSheetz.AddItem ws.Name
Next ws
Dim unsorted As Boolean, i As Integer, temp As Variant
unsorted = True
Do
unsorted = False
For i = 1 To .ListCount - 1
If .List(i) > .List(i + 1) Then
temp = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = temp
unsorted = True
Exit For
End If
Next i
Loop While unsorted = True
.ListIndex = 1
End With
.ScreenUpdating = True
End With
End Sub
Hello Tom,
Add in works awesome!! Wondering if you have found a way to insert a drop box list into the worksheet itself? Also wondering if there is a way for the list to automatically update itself when a new sheet is created without having to close the file? If possible to add a drop down list to the worksheet itself, how could I have the drop down list only show new worksheets with the same tab name as the drop list is placed in?
This didn’t work with Excel Home 2016. Any suggestions?
Yes it does work with Excel 2016, I just tested it again. Make sure your workbook is of the .xlam extension and look for the Addins tab on the Ribbon.
Is there a way to open a userform, place a combobox there and have the list created there and make it selectable? My problem is that I do not own the source file and cannot save it. I want to be able to open a worksheet (name unknown until review of sheet names) copy some data into another workbook and do something with that data. I’ve already got the code to select and open the source workbook, just need a way to select a worksheet from this workbook after opening it.
Thanks
You want to create a userform on the fly? It can be done, but maybe you can utilize other media approaches, such as a custom list of selectable items from your right-click menu. It’s not clear what you want to populate the combobox with. Is it sheet names, or workbook names, or cell values from a particular range, or something else. More info is needed to understand what you want to do and what your expected result is, based on some event you have in mind.