11 thoughts on “Sheet Names in a DropDown on a Toolbar”

1. Dick,
   Why re-invent the wheel? Right-clicking on the arrows at the bottom left of the screen, just over the status bar, does the same thing.
   Gary Brown

2. I modified the macro so that I can use my own “name” for the sheet rather than the actual name. I know that I could just change the name of the worksheet by rightclicking on “rename”, but my workbook is so big it crashes when I try it. I added the following code in square brackets:

   With cbSheets
   ‘Add a combobox control
   Set ctDropDown = .Controls.Add(msoControlDropdown)

   ‘Populate with sheet names
   For Each oSheet In ThisWorkbook.Sheets
   ctDropDown.AddItem oSheet.Name

   [If oSheet.Name = “Data” Then
   ctDropDown.AddItem “Collections”
   End If]
   Next oSheet

   and then in Sub ActivateSheet()

   With Application.CommandBars(sCbName).Controls(1)
   [If .Text = “Collections” Then
   Sheets(“Data”).Activate
   Else]
   ThisWorkbook.Sheets(.Text).Activate
   End If
   End With

   End Sub

   Now I can simulate a “new” name for my Worksheet.

   Thanks

3. Why did you choose to use ‘ThisWorkBook’ rather ‘ActiveWorkBook’?

4. Christopher: Good question. The example is for a one-shot type workbook. That is, it’s the only workbook the user will have open. If you made it the ActiveWorkbook, you would need some event handling to update the control for the sheets in the active book whenever the user switched workbooks. That’s doable, it’s just a lot more work.

5. Why re-invent the wheel?
   One will learn a lot from it.
   And one can put this wheel onto something cooler than a monowheel (http://www.dself.dsl.pipex.com/MUSEUM/TRANSPORT/motorwhl/motorwhl.htm#1)

6. As a relative newcomer to Excel programming, I am struggling with where to paste the code for this example.

   I pasted it all into This Workbook and got an error saying that Constants, fixed length arrays ….. are not allowed as public members of object modules.

   The offending line was the Public Const…line. Where should this line have been placed.

   Thanks,

7. Dale: All this would go in a standard module.

8. I took Dick’s code and made it list the different workbooks in a drop-down when you right-click in Excel. Automatically updates for new workbooks by changing the OnAction of the new workbook icon in Excel.

   Public Const sCbName As String = “SheetList”

   Sub CreateCB()

   Dim cbSheets As CommandBarControl
   Dim oSheet As Object

   ‘ Delete commandbar if it exists
   On Error Resume Next
   Application.CommandBars(“Cell”).FindControl(, , sCbName).Delete
   On Error GoTo 0

   ‘ Create commandbar
   Set cbSheets = Application.CommandBars(“Cell”).Controls.Add(msoControlDropdown)

   With cbSheets
   .OnAction = “ActivateSheet”
   .BeginGroup = True
   .Tag = sCbName
   End With

   PopNames (sCbName)

   Application.CommandBars(“Standard”).FindControl(, 2520).OnAction = “NewBook”

   End Sub

   Sub ActivateSheet()

   Dim objVB As VBComponent

   With Application.CommandBars(“Cell”).FindControl(, , sCbName)
   strText = .Text
   PopNames (sCbName)
   Workbooks(strText).Activate
   End With

   End Sub

   Sub PopNames(strCBName As String)

   ‘ Populate with sheet names
   Application.CommandBars(“Cell”).FindControl(, , strCBName).Clear
   For Each oSheet In Workbooks
   Application.CommandBars(“Cell”).FindControl(, , strCBName).AddItem oSheet.Name
   Next oSheet

   End Sub

   Sub NewBook()

   Workbooks.Add
   PopNames (“SheetList”)

   End Sub

9. Hi, I’m just a begginer plz help – lol I need to know what is standard module b’coz i tried to create a new module and put the code in there.and run the code it gives me an error like this ” compile error” constant Required.

   It would be highly apprecited if someone can give me solution to run this module.

   By the way the Toolbar looks excellent but i want to use it on my sheet

   Thankx

   Faizal

10. Hi
    problem are the apostrophes. They’re not correctly represented in this code extract. Try the following code (hope I didn’t miss one of the apostrophes):

    Public Const sCbName As String = “SheetList”

    Sub CreateCB()

    Dim cbSheets As CommandBarControl
    Dim oSheet As Object
    ‘ Delete commandbar if it exists
    On Error Resume Next
    Application.CommandBars(“Cell”).FindControl(, , sCbName).Delete
    On Error GoTo 0

    ‘ Create commandbar
    Set cbSheets = Application.CommandBars(“Cell”).Controls.Add(msoControlDropdown)

    With cbSheets
    .OnAction = “ActivateSheet”
    .BeginGroup = True
    .Tag = sCbName
    End With

    PopNames (sCbName)

    Application.CommandBars(“Standard”).FindControl(, 2520).OnAction = “NewBook”

    End Sub

    Sub ActivateSheet()

    Dim objVB As VBComponent

    With Application.CommandBars(“Cell”).FindControl(, , sCbName)
    strText = .Text
    PopNames (sCbName)
    Workbooks(strText).Activate
    End With

    End Sub

    Sub PopNames(strCBName As String)

    ‘ Populate with sheet names
    Application.CommandBars(“Cell”).FindControl(, , strCBName).Clear
    For Each oSheet In Workbooks
    Application.CommandBars(“Cell”).FindControl(, , strCBName).AddItem oSheet.Name
    Next oSheet

    End Sub

    Sub NewBook()

    Workbooks.Add
    PopNames (“SheetList”)

    End Sub

11. If someone wants to check out an integrated workbook/sheet navigator, check
    Workbook Window Navigator
    http://www.tushar-mehta.com/excel/software/utilities/wb_nav.html

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.