Create menus and sub menus with VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create menus with sub menus in VBA for a Ppt Addin. I can create
the menus, but the sub menus have me stumped (I'm quite new to all this). I
have the following code, which creates a sub menu, but the second sub menu
attaches itself to the first sub menu rather than to the main menu!! Can
someone help me please?
Thanks
Aehan

Dim myMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim myCustomMenu As CommandBarControl

On Error Resume Next
Application.CommandBars.ActiveMenuBar.Controls("&Logos").Delete
On Error GoTo 0

Set myMainMenuBar = Application.CommandBars.ActiveMenuBar

iHelpMenu = myMainMenuBar.Controls("Help").Index

Set myCustomMenu = myMainMenuBar.Controls.Add(Type:=msoControlPopup, _
before:=iHelpMenu)

myCustomMenu.Caption = "&Logos"

Set myCustomMenu = myCustomMenu.Controls.Add(Type:=msoControlPopup)
myCustomMenu.Caption = "&Insert Logos"

With myCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Red on Black"
.OnAction = "TestMacro"
End With
With myCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Red on White"
.OnAction = "TestMacro"
End With

Set myCustomMenu = myCustomMenu.Controls.Add(Type:=msoControlPopup)
myCustomMenu.Caption = "&Size Logos"

With myCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "40"
.OnAction = "TestMacro"
End With
 
You're almost there. See this version of what you've written:

Sub Menus()

Dim myMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim myCustomMenu As CommandBarControl
Dim myTempMenu As CommandBarControl

On Error Resume Next
Application.CommandBars.ActiveMenuBar.Controls("&Logos").Delete
On Error GoTo 0

Set myMainMenuBar = Application.CommandBars.ActiveMenuBar

iHelpMenu = myMainMenuBar.Controls("Help").Index

' Add a new control to the main menu bar and set a reference to it:
Set myCustomMenu = myMainMenuBar.Controls.Add(Type:=msoControlPopup, _
before:=iHelpMenu)
' Set properties:
myCustomMenu.Caption = "&Logos"

' This may be the problem ... you're using the same variable for both the
' reference to the new control and for the control you're adding the new
control
' to.
'Set myCustomMenu = myCustomMenu.Controls.Add(Type:=msoControlPopup)

' Instead, use a new variable

Set myTempMenu = myCustomMenu.Controls.Add(Type:=msoControlPopup)
myTempMenu.Caption = "&Insert Logos"

' and add buttons to it:
With myTempMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Red on Black"
.OnAction = "TestMacro"
End With
With myTempMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Red on White"
.OnAction = "TestMacro"
End With

Set myTempMenu = myCustomMenu.Controls.Add(Type:=msoControlPopup)
myTempMenu.Caption = "&Size Logos"

With myTempMenu.Controls.Add(Type:=msoControlButton)
.Caption = "40"
.OnAction = "TestMacro"
End With

End Sub

================================================

Make sense? It seems to do what you're after
 
Thank you so much. I did try adding a new variable myself, but I added it
for the "Size Logos" control and of course it didn't work and I got an error
message!!! Some day I'll get my head round this properly, meantime I'm very
grateful to you for pointing out what I should do in a way that I can
understand.

Rgds
Aehan
 
Thank you so much. I did try adding a new variable myself, but I added it
for the "Size Logos" control and of course it didn't work and I got an error
message!!! Some day I'll get my head round this properly, meantime I'm very
grateful to you for pointing out what I should do in a way that I can
understand.
Click to expand...

My pleasure. This one in particular is a toughie to wrap the head around until
you've done it a few times.
 
Hello

this looks promising. i am currently working on a solution to send an email
from excel through outlook without any user intervention. i am nearly there.
the problem is adding a menu item in the email window from excel.
Click to expand...

This would seem to be a question for one of the Outlook groups, no?
 
Hello Steve

ah, no.

i am working in excel, trying to make an item appear on a menu in an outlook
window.
Click to expand...

Therefore ah yes.

By and large, it doesn't much matter what app is hosting your automation code, it's the app
you're *automating* that governs what the code looks like.

You can probably automate Outlook from Excel then copy/paste the same code into PPT or even
VB and have it work w/ only minor changes.



yes outlook is involved, but the initial starting point is in excel.
 
Back
Top