Tom’s Tutorials for Excel: Sheet Selector Drop-Down List

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
16 comments on “Tom’s Tutorials for Excel: Sheet Selector Drop-Down List
  1. Per Claussen says:

    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.

    • Tom Urtis says:

      In the Workbook_BeforeClose event, comment out (that is, enter an apostrophe immediately before), or simply delete, this code line:

      ThisWorkbook.Save

  2. Per Claussen says:

    thanks, Tom – it was that simple 🙂

  3. michael smith says:

    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?

    • Tom Urtis says:

      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.

  4. Harry Price says:

    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

  5. Matt Walsh says:

    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

    • Tom Urtis says:

      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.

  6. Matt Walsh says:

    Either Way it’s a great script. so useful on my workbook with 50+ sheets

  7. PritishS says:

    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.

    • Tom Urtis says:

      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

  8. Austin says:

    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?

  9. John says:

    This didn’t work with Excel Home 2016. Any suggestions?

    • Tom Urtis says:

      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.

  10. Ralph says:

    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

    • Tom Urtis says:

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*