How to Create Customized Menus in Your Excel Worksheet via VBA

The Excel ribbon can be modified to suit a particular job. This exercise creates new menus that point users to specific sheets relevant to their task.

In this article, we will introduce how to create customized menus in your Excel worksheet, like this:Create Customized Menus In Your Excel Worksheet

This article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

Workbook

The workbook to be used has a number of sheets. We suggest you use the sample one found here.  It looks like this.The Sample Workbook

The first four sheets are dysfunctional, being used in this exercise for navigation purposes only.

The fifth sheet will hold the customised menu structure particular to this workbook. At present it should be blank except for a Test button.

Add the Menu Structure

Copy the following block of text into cell A1 of the sheet “MenuSheet”.

Level,Caption,Position/Macro,Divider

1,&User Tools,10

2,Show Dashboard,SelectDashboard

2,Add new, ,TRUE

3,Client,SelectClient

3,Location,SelectLocation

3,Manager,SelectManager

2,Close,CloseFile

Format the Menu Structure

This CSV data ends up in column A. To format it into individual Excel cells, select column A and using ‘Text to columns’ in the Data tab. The delimiter would be “comma”.Format It Into Individual Excel Cells

The above, once we have added the appropriate code, will give us the level-based menu structure at left.The Level-based Menu Structure

The first level, in this exercise, is arbitrarily placed as the tenth item on the menu bar, as you will see when the full width of the workbook is displayed.

Creating and Destroying Menus

Since we only want the new menus for this specific workbook, we will create and destroy them on opening and closing the workbook.

The code below is a start. It will be triggered when the workbook is opened or closed. Copy it into a module in your workbook

Assign the Test button on MenuSheet to Auto_Open.

Option Explicit

Sub auto_Open()
    Call DeleteMenu
    Call CreateMenu
 End Sub
 
Sub auto_Close()
    Call DeleteMenu
End Sub

Below, we examine our menu structure more closely.Examine Our Menu Structure More Closely

No macro is triggered by Add New because it has no function other than as parent of sub-menus.

This kind of menu structure is easy to maintain. Simply add new items with their macros, being conscious of the menu Level.

The Code

Append the following VBA code to the module.  This will examine the “MenuSheet” and create the customised menu.

Sub CreateMenu()
'   Called from Auto_Open. 'NOTE: There is no error handling in this subroutine
    Dim MenuSheet As Worksheet
    Dim MenuObject As CommandBarPopup

    Dim MenuItem As Object
    Dim SubMenuItem As CommandBarButton
    Dim sRow As Integer
    Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider

    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
    Call DeleteMenu

    sRow = 2 '   start row
    
'   Add menus using the structure as per the MenuSheet
    Range("A" & sRow).Select
    Do While ActiveCell > "" '****************
        With MenuSheet
            MenuLevel = .Cells(sRow, 1)
            Caption = .Cells(sRow, 2)
            PositionOrMacro = .Cells(sRow, 3)
            Divider = .Cells(sRow, 4)
            NextLevel = .Cells(sRow + 1, 1)
        End With
        
        Select Case MenuLevel
            Case 1 ' Add the top-level menu to the Worksheet CommandBar
                Set MenuObject = Application.CommandBars(1). _
                    Controls.Add(Type:=msoControlPopup, _
                    Before:=PositionOrMacro, _
                    Temporary:=True)
                MenuObject.Caption = Caption
            Case 2 ' A Menu Item
                If NextLevel = 3 Then
                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
                Else
                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
                    MenuItem.OnAction = PositionOrMacro
                End If
                MenuItem.Caption = Caption
                If Divider Then MenuItem.BeginGroup = True
            Case 3 ' A SubMenu Item
                Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = Caption
                SubMenuItem.OnAction = PositionOrMacro
                
                If Divider Then SubMenuItem.BeginGroup = True
        End Select
        sRow = sRow + 1
        Range("A" & sRow).Select '***************************************
    Loop
    Sheets("Dashboard").Activate
End Sub

Sub DeleteMenu()
'   This sub will be executed when the workbook is closing
    Dim MenuSheet As Worksheet
    Dim sRow As Integer
    Dim Caption As String
    
    On Error Resume Next
    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
    sRow = 2
    Range("A" & sRow).Select
    Do While ActiveCell > ""
        If MenuSheet.Cells(sRow, 1) = 1 Then
            Caption = MenuSheet.Cells(sRow, 2)
            Application.CommandBars(1).Controls(Caption).Delete
        End If
        sRow = sRow + 1
        Range("A" & sRow).Select
    Loop
   
    On Error GoTo 0
End Sub

Sub SelectDashboard()
    Sheets("Dashboard").Activate
End Sub
Sub SelectClient()
    Sheets("Client").Activate
End Sub

Sub SelectLocation()
    Sheets("Location").Activate
End Sub

Sub SelectManager()
    Sheets("Manager").Activate
End Sub

Sub CloseFile()
    MsgBox "Close! (write your own code in the module)"
End Sub

Test the code with the Test button. The newly created user menu will be found at menu position 10, under Add-ins.

Excel Recovery

Excel is unstable at times, and crashes while open, damaging the source file. Where it fails to recover the file, it is useful to have a tool to repair Excel damage otherwise all unbacked- up work will be lost.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar and sql recovery software products. For more information visit www.datanumen.com