Error when creating menu item on command bar

  • Thread starter Thread starter Michael Malinsky
  • Start date Start date
M

Michael Malinsky

Below is my code. It worked fine until today and I didn't change
anything. I currently have this code in an .xla add-in, but the menu
that should be appearing on the Worksheet Menu Bar does not work. When
I try to run the code manually, I get the error

Method 'OnAction' of object '_CommandBarButton' failed

on the .Controls(MenuName).OnAction = "Conversion" line

Thanks for any help.

Sub Auto_Open()

Dim cWmb As String, cMm As String
Dim MenuName As String

cWmb = "Worksheet Menu Bar"
cMm = "Utilities"

Application.ScreenUpdating = False

'To delete any existing Utilities menus
On Error Resume Next
CommandBars(cWmb).Controls(cMm).Delete
On Error GoTo 0

'Add menu
CommandBars(cWmb).Controls.Add(Type:=msoControlPopup,
Before:=CommandBars(cWmb).Controls.Count - 1).Caption = cMm

'Use the menu to create the menu item(s)
With CommandBars(cWmb).Controls(cMm)
MenuName = "&Conversion"
.Controls.Add(Type:=msoControlButton).Caption = MenuName
.Controls(MenuName).OnAction = "Conversion"
End With
Application.ScreenUpdating = True

End Sub
 
When run from and xla file, I get a 'Invalid Procedure Call or
Function' error on line

CommandBars(cWmb).Controls.Add(Type:=msoControlPopup, _
Before:=CommandBars(cWmb).Controls.Count - 1).Caption = cMm

It runs fine when run from an .xls file. If you need this routine on
every new workbook, can you run it from an xlt file in your xlstart
directory?
 
Back
Top