I am using the following code to add a menu to an Excel Workbook. This is called from the AutoOpen Sub :
Public Sub AddPFMUpdateMenu()
Dim myMenuBar, msoControlPopup, newMenu, msoControlButton, ctrl1 As Variant
Dim msoButtonCaption As Variant
Set myMenuBar = CommandBars.ActiveMenuBar
Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
newMenu.Caption = "&PFM Update"
Set ctrl1 = newMenu.Controls.Add(Type:=msoControlButton, Id:=1)
ctrl1.Caption = "&Update Worksheet with PFM Information"
ctrl1.TooltipText = "PFM Update"
ctrl1.Style = msoButtonCaption
I keep getting an Runtime error 5 "Invalid procedure call or argument" when it hits the second line of code. I can't figure out why I keep getting this error. I use the exact code on other workbooks without a problem. This particular workbook has multiple worksheets,
but I don't think that should make a difference.