Create New Tab in Excel

Question

I would like to create a New Tab with Custom Buttons when user opens a Macro Enabled Worksheet (.xlsm). Below is code I’ve used previously in the ThisWorkbook module of my macro; however, the buttons display under the “Add-in” tab. My goal is to add the buttons under a custom tab with custom sections if possible. Thank you.

'Stored in ThisWorkbook module of my macro
Private Sub Workbook_Open()

            Dim CmdBar As CommandBar
            Dim CmdBarMenu As CommandBarControl
            Dim CmdBarMenuItem As CommandBarControl

            Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
            Set CmdBarMenu = CmdBar.Controls("Tools")

            On Error Resume Next
                Application.DisplayAlerts = False
                CmdBarMenu.Controls(Button).Delete
                Application.DisplayAlerts = True
            On Error GoTo 0

            Set CmdBarMenuItem = CmdBarMenu.Controls.Add(Type:=msoControlButton)
            With CmdBarMenuItem
                .Caption = Button
                .OnAction = “NameOfMyMacro"
                .FaceId = 5872
                .Style = msoButtonIconAndCaptionBelow
            End With

    End Sub

This type of code always creates the controls in the Add-ins tab: you can't place them anywhere else since Excel 2007 — Tim Williams, May 29 '18 at 15:04

score 0 · Answer 1 · answered May 29 '18 at 16:20

0

You can manually change the .XML on the user interface, or use a software which does that for you.

See the links below:

How to add a custom Ribbon tab using VBA? http://www.contextures.com/excelribbonaddcustomtab.html

answered May 29 '18 at 16:20

César Rodriguez

296
5
16

Create New Tab in Excel

1 Answers1