Excel VBA New Menu

[iancook1982]

Hi,

I have setup my workbook so that when it opens it adds a menu alongside the File, Edit, View menus at the top. I did this using the below code in the ThisWorkbook code in VBA. With one problem, I get the menu bar called "FoFCIT Divergence Macro", and I get the submenu "Control Panel".

But on clicking this Control Panel option I would like it to launch a userform i have that is called Control_Panel. Is this possible?

Alternatively I have a module that launches this Control_Panel, the module is called "Load_Control.Load_Control"

Code=
Private Sub Workbook_Open()
On Error Resume Next
Dim cmbBar As CommandBar
Dim cmbControl As CommandBarControl
Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True)
With cmbControl
.Caption = "&FoFCIT Divergence Macro"
With .Controls.Add(Type:=msoControlButton)
.Caption = "Control Panel"
.TooltipText = "Runs the Control Panel"
.OnAction = "Control_Panel"
End With
End With
Resume Next

End Sub

Can you help? Thanks, Ian.

 

[Andrew Poulsom]

It's not a good idea to have a procedure with the same name as the module that contains it. That said you need to change:

.OnAction = "Control_Panel"

to:

.OnAction = "Load_Control"

 

[iancook1982]

That's great Andrew, works a treat.
Thanks for the tip.

Kind Regards.