MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Menus in VBA Excel

  Asked By: Techguy    Date: Mar 13    Category: MS Office    Views: 3947

Anyone have the code that allow creat a menu like as File, Edit, etc in VBA for
Excel ?



6 Answers Found

Answer #1    Answered By: Dot net Sachin     Answered On: Mar 13

There is no such ability built in to Excel VBA, but it can be done using calls
to the Windows API. The following link has instructions and a downloadable


Answer #2    Answered By: Renee Lane     Answered On: Mar 13

Back (a couple of years ago) when I was creating my custom commandbars,
I ran across the VBA scripting to customize the menu  bar.
My routine saved the beginning menu bar, then created a new menu, then
removed it as part of a BeforeClose event.
I tested it, and decided to use the command bars instead (I liked the icons)
I'll try to look for it again.

Answer #3    Answered By: Volney Fischer     Answered On: Mar 13

You can certainly make changes to a commandbar or create a new commandbar. I
thought the question was how to add menus  to a userform. Reading the origianl
post again, I don't know where I got that idea. Here is code  to add an option to
the bottom of the Tools menu  while that workbook is open. Paste this code into a
VBA module in the workbook.

Option Explicit
Global Const VbaMenuItem As String = "MyMacroMenu"

Private Sub Auto_Open()
Dim newItem As CommandBarControl
On Error GoTo AOerr1
'Add a new item to the bottom of the Tools menu while the document is open.
, _
Len(VbaMenuItem$)) <> VbaMenuItem$ Then
Set newItem = CommandBars("Tools").Controls.Add(Type:=msoControlButton)
With newItem
.BeginGroup = True
.Caption = VbaMenuItem$
.FaceId = 0
.OnAction = "ShowForm"
End With
End If
'Assign macro shortcut = {Ctrl}{Shift}a
Application.OnKey "+^a", "ShowForm"
Exit Sub
MsgBox Err.Description
Resume Next
End Sub

Private Sub Auto_Close()
Dim x As Integer
On Error GoTo AcERR1
'Check the name of the active workbook. If it's not ThisWorkbook, don't do
If LCase(ActiveWorkbook.Name) = LCase(ThisWorkbook.Name) Then
'Remove VbaMenuItem$ from the Tools menu
For x% = 1 To CommandBars("Tools").Controls.Count
If CommandBars("Tools").Controls(x%).Caption = VbaMenuItem$ Then
End If
Next x%
End If
'Clear macro shortcut = {Ctrl}{Shift}a
Application.OnKey "+^a", ""
Exit Sub
MsgBox Err.Description
Resume Next
End Sub

You could use the Workbook_Open and Workbook_BeforeClose events instead of
Auto_Open and Auto_Close. In that case, the code would go in the ThisWorkbook

Answer #4    Answered By: Sophie Campbell     Answered On: Mar 13

You can also do menus  more easily in forms, by just adding labels and
using popups. I have found that API method severely flakey and would
never deploy it.

Answer #5    Answered By: Adalwine Fischer     Answered On: Mar 13

Sub XPTO()

Dim mymenu

' The following line of code  adds "Perso-Menu" as a new menu  on

' the worksheet menu bar.

Application.MenuBars(xlWorksheet).Menus.Add "Perso-Menu", Before:=8

' Set mymenu to be the menu items.

Set mymenu =

With mymenu

.Add Caption:="&Unfilter", OnAction:="File_unfilter" 'Adds Item1

.Add Caption:="&2 Teste", OnAction:="mymacro2" 'Adds Item2

.Add Caption:="&3 Teste", OnAction:="mymacro3" 'Adds Item3

.Add Caption:="&4 Teste", OnAction:="mymacro4" 'Adds Item4

.Add Caption:="&5 Teste", OnAction:="mymacro5" 'Adds Item5

.Add Caption:="&6 Teste", OnAction:="mymacro6" 'Adds Item6

.Add Caption:="&Caculation", OnAction:="Open_Cotation" 'Adds Item7

End With

CommandBars("Worksheet menu bar").Controls("Perso-Menu").Controls("2
Teste").BeginGroup = True

CommandBars("Worksheet menu bar").Controls("Perso-Menu").Controls("4
Teste").BeginGroup = True

CommandBars("Worksheet menu bar").Controls("Perso-Menu").Controls("6
Teste").BeginGroup = True

End Sub

Answer #6    Answered By: Kristin Johnston     Answered On: Mar 13

I recommend that you use MSDN online as a reference:


There are frequently examples of the selected
Object/Method/Property. They may take some modifications to work in
your situation.

You can certainly add items to the standard menus  and add menus to
the standard menu  bar. I may be wrong, but I'm pretty sure you can
even disable the standard menu bar and add your own.

I hope some examples help. Here are some examples (from my
working code) for adding (and, upon exit, removing) menus. They may
not be pretty, but they work:

The first Sub adds an item to a standard Edit menu and adds two
additional menus to the menu bar. The firstadded menu is a blank menu
to make the second added menu name stands out.

The second Sub removes any and all menus with the specific name(s). I
had a problem that caused the menu to be added more than once and
needed that algorithm.

The third Sub only removes the added menus.

Sub AddKenwoodMenu(Optional h As Byte) ' From MSDN then adapted
Debug.Print " ThisWorkbk 5 Add Menu"
' Adds UnDoSorts and TH-F6A menus
Dim MB As Object
Dim MN As Object
Application.Cursor = xlWait

Set MB = MenuBars("Worksheet")

MB.Menus("Edit").MenuItems.Add Caption:="U&nDoAllSorts",
before:="-", OnAction:="UnDoSorts"

MB.Menus.Add Caption:=" " ' Adds a top level menu called
" ".
MB.Menus.Add Caption:="TH-F6&A" ' Adds a top level menu called
MB.Menus("&TH-F6A").MenuItems.Add Caption:="&New...",
MB.Menus("&TH-F6A").MenuItems.Add Caption:="&Open .Fx...",
OnAction:="FileRead.ReadFxFile" ' Adds an item called "&Open .FX"
under the menu "Kenwood"
MB.Menus("&TH-F6A").MenuItems.Add Caption:="Save&As .Fx ...",
OnAction:="FileRead.WriteFxFile" ' Adds an item called "&Save .FX"
under the menu "Kenwood"
MB.Menus("&TH-F6A").MenuItems.Add Caption:="-" ' Adds a divider
MB.Menus("&TH-F6A").MenuItems.Add Caption:="Transfer &Memories...",
MB.Menus("&TH-F6A").MenuItems.Add Caption:="&UnDoAllSorts",
End Sub

Public Sub RemoveKenwoodMenus(Optional h As Byte)
Debug.Print " ThisWorkbk 8 Remove Menus"
Dim MB As Object
Dim MN As Object
Application.Cursor = xlWait

' Delete all instances that may exist of my menus.
For Each MB In MenuBars
For Each MN In MB.Menus

If MN.Caption = "TH-F6&A" Then
ElseIf MN.Caption = " " Then
MB.Menus(" ").Delete
End If

Next MN
Next MB
On Error Resume Next
' Don't test, just delete & swallow the error if not present.
On Error GoTo 0
End Sub

Public Sub RemoveKenwoodMenu(Optional h As Byte)
Debug.Print " ThisWorkbk 9 Remove Menu"
Dim MB As Object

Set MB = MenuBars("Worksheet")
MB.Menus(" ").Delete ' Removes the top level menu called
" ".
MB.Menus("TH-F6&A").Delete ' Removes the top level menu called

End Sub

Didn't find what you were looking for? Find more on Menus in VBA Excel Or get search suggestion and latest updates.