Excel VBA XLA Add In - Custom Menu - Error91

[hafod]

Hi
I have created a User Form as an xla Add In. I want the form and some features to execute via a custom menu (show User GUI etc). The code below returns ' Error 91: Object Variable or With Block Variable Not Set'when xla is loaded at line '***'. The subroutine is launched
by the 'Workbook_Open() event. Syntactically this seems fine to me. Can anyone please help?. (I have only included relevant code)
Regards Mike.

Public Sub AddCustomMenu() - 'Code in 'General' under 'ThisWorkbook'
Dim MenuBar As CommandBar
Dim iHelpIndex As Integer
Dim muCustom As CommandBarControl

(***) Set MenuBar = CommandBars("Worksheet Menu Bar")
iHelpIndex = MenuBar.Controls("Help").Index
Set muCustom = MenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpIndex, Temporary:=True)
With muCustom
.Caption = "&Custom"
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Show SIMS Data EntryForm"
.OnAction = "ShowDataForm"
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "&Print Data List"
.OnAction = "PrintDataList"
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "&Ascending"
.BeginGroup = True
.OnAction = "SortList"
.Parameter = "Asc"
End With
End With
End Sub

 

[shetlandbob]

See FAQ

Robert Cumming

http://www.consultantengineer.co.uk