gabethegrape
06-22-2009, 09:04 PM
Hello,
I need some help inserting menu items for two addins (the attachment has the code for both addins in two separate modules). With each addin, I'm trying to add a sub menu to "My Menu". I can successfully do this if the first addin is activated first, but it gives me an error if the second addin is activated first because "My Menu" does not exist. How can I check if "My Menu" exists and if it does not, create it?
Thanks for your help!
Gabe
First Addin
Private Sub Workbook_Activate()
Run "addMenu"
End Sub
Private Sub Workbook_Deactivate()
Run "removeMenu"
End Sub
Sub addMenu()
Dim cmdbar As CommandBar
Dim toolsMenu As CommandBarControl
Dim myMenu As CommandBarPopup
Dim subMenu As CommandBarControl
' Point to the Worksheet Menu Bar
Set cmdbar = Application.CommandBars("Worksheet Menu Bar")
' Point to the Tools menu on the menu bar
Set toolsMenu = cmdbar.Controls("Tools")
' Create My Menu
Set myMenu = toolsMenu.Controls.Add(Type:=msoControlPopup)
' Create the sub Menu(s)
Set subMenu = myMenu.Controls.Add
With myMenu
.Caption = "My Menu"
.BeginGroup = False
With subMenu
.Caption = "sub Menu"
.BeginGroup = False
.OnAction = "'" & ThisWorkbook.Name & "'!myMacro" ' Assign Macro to Menu Item
End With
End With
End Sub
Private Sub myMacro()
MsgBox ("My Sub Menu Command")
End Sub
' How to remove the menu item
Sub removeMenu()
On Error Resume Next
Dim cmdbar As CommandBar
Dim CmdBarMenu As CommandBarControl
Set cmdbar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = cmdbar.Controls("Tools")
CmdBarMenu.Controls("My Menu").Delete
End Sub
Second Addin
Private Sub Workbook_Activate()
Run "addMenu"
End Sub
Private Sub Workbook_Deactivate()
Run "removeMenu"
End Sub
Sub addMenu()
Dim cmdbar As CommandBar
Dim toolsMenu As CommandBarControl
Dim myMenu As CommandBarPopup
Dim subMenu As CommandBarControl
' Point to the Worksheet Menu Bar
Set cmdbar = Application.CommandBars("Worksheet Menu Bar")
' Point to the Tools menu on the menu bar
Set toolsMenu = cmdbar.Controls("Tools")
Set myMenu = toolsMenu.Controls("My Menu")
If myMenu.Caption <> "My Menu" Then
Set myMenu = toolsMenu.Controls.Add(Type:=msoControlPopup)
With myMenu
.Caption = "My Menu"
.BeginGroup = False
End With
Else
' Point to the My Menu on in the Tools menu
Set myMenu = toolsMenu.Controls("My Menu")
' Create the sub Menu(s)
Set subMenu = myMenu.Controls.Add
With subMenu
.Caption = "sub Menu"
.BeginGroup = False
.OnAction = "'" & ThisWorkbook.Name & "'!myMacro" ' Assign Macro to Menu Item
End With
End If
End Sub
Private Sub myMacro()
MsgBox ("My Sub Menu Command")
End Sub
' How to remove the menu item
Sub removeMenu()
On Error Resume Next
Dim cmdbar As CommandBar
Dim CmdBarMenu As CommandBarControl
Set cmdbar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = cmdbar.Controls("Tools")
CmdBarMenu.Controls("My Menu").Delete
End Sub
Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&MyMenu").Delete
On Error GoTo 0
End Sub
I need some help inserting menu items for two addins (the attachment has the code for both addins in two separate modules). With each addin, I'm trying to add a sub menu to "My Menu". I can successfully do this if the first addin is activated first, but it gives me an error if the second addin is activated first because "My Menu" does not exist. How can I check if "My Menu" exists and if it does not, create it?
Thanks for your help!
Gabe
First Addin
Private Sub Workbook_Activate()
Run "addMenu"
End Sub
Private Sub Workbook_Deactivate()
Run "removeMenu"
End Sub
Sub addMenu()
Dim cmdbar As CommandBar
Dim toolsMenu As CommandBarControl
Dim myMenu As CommandBarPopup
Dim subMenu As CommandBarControl
' Point to the Worksheet Menu Bar
Set cmdbar = Application.CommandBars("Worksheet Menu Bar")
' Point to the Tools menu on the menu bar
Set toolsMenu = cmdbar.Controls("Tools")
' Create My Menu
Set myMenu = toolsMenu.Controls.Add(Type:=msoControlPopup)
' Create the sub Menu(s)
Set subMenu = myMenu.Controls.Add
With myMenu
.Caption = "My Menu"
.BeginGroup = False
With subMenu
.Caption = "sub Menu"
.BeginGroup = False
.OnAction = "'" & ThisWorkbook.Name & "'!myMacro" ' Assign Macro to Menu Item
End With
End With
End Sub
Private Sub myMacro()
MsgBox ("My Sub Menu Command")
End Sub
' How to remove the menu item
Sub removeMenu()
On Error Resume Next
Dim cmdbar As CommandBar
Dim CmdBarMenu As CommandBarControl
Set cmdbar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = cmdbar.Controls("Tools")
CmdBarMenu.Controls("My Menu").Delete
End Sub
Second Addin
Private Sub Workbook_Activate()
Run "addMenu"
End Sub
Private Sub Workbook_Deactivate()
Run "removeMenu"
End Sub
Sub addMenu()
Dim cmdbar As CommandBar
Dim toolsMenu As CommandBarControl
Dim myMenu As CommandBarPopup
Dim subMenu As CommandBarControl
' Point to the Worksheet Menu Bar
Set cmdbar = Application.CommandBars("Worksheet Menu Bar")
' Point to the Tools menu on the menu bar
Set toolsMenu = cmdbar.Controls("Tools")
Set myMenu = toolsMenu.Controls("My Menu")
If myMenu.Caption <> "My Menu" Then
Set myMenu = toolsMenu.Controls.Add(Type:=msoControlPopup)
With myMenu
.Caption = "My Menu"
.BeginGroup = False
End With
Else
' Point to the My Menu on in the Tools menu
Set myMenu = toolsMenu.Controls("My Menu")
' Create the sub Menu(s)
Set subMenu = myMenu.Controls.Add
With subMenu
.Caption = "sub Menu"
.BeginGroup = False
.OnAction = "'" & ThisWorkbook.Name & "'!myMacro" ' Assign Macro to Menu Item
End With
End If
End Sub
Private Sub myMacro()
MsgBox ("My Sub Menu Command")
End Sub
' How to remove the menu item
Sub removeMenu()
On Error Resume Next
Dim cmdbar As CommandBar
Dim CmdBarMenu As CommandBarControl
Set cmdbar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = cmdbar.Controls("Tools")
CmdBarMenu.Controls("My Menu").Delete
End Sub
Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&MyMenu").Delete
On Error GoTo 0
End Sub