Logo 
Search:

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: 2308
  

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

Share: 

 

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
example:

www.xcelfiles.com/API_Userform_MenuMaker.html


 
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.
If
Right(CommandBars("Tools").Controls(CommandBars("Tools").Controls.Count).Caption\
, _
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
AOerr1:
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
anything.
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
CommandBars("Tools").Controls(x%).Delete
End If
Next x%
End If
'Clear macro shortcut = {Ctrl}{Shift}a
Application.OnKey "+^a", ""
Exit Sub
AcERR1:
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
module.

 
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 =
Application.MenuBars(xlWorksheet).Menus("Perso-Menu").MenuItems



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:

msdn.microsoft.com/.../\
ofcmdbar.asp

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
"TH-F6&A".
MB.Menus("&TH-F6A").MenuItems.Add Caption:="&New...",
OnAction:="Utilities.ClearData"
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...",
OnAction:="Sheet3.MemoryTransfer_Click"
MB.Menus("&TH-F6A").MenuItems.Add Caption:="&UnDoAllSorts",
OnAction:="UnDoSorts"
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
MB.Menus("TH-F6&A").Delete
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.
MenuBars("Worksheet").Menus("Edit").MenuItems("UnDoAllSorts").Delete
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
"Kenwood".
MB.Menus("Edit").MenuItems("UnDoAllSorts").Delete

End Sub

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




Tagged: