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:=msoControlPop up,
Befo=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:=msoControlPop up, _
Befo=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?


Michael Malinsky wrote:
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:=msoControlPop up,
Befo=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

I'm not sure what happened, but it works now.

Thanks.

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

CommandBars(cWmb).Controls.Add(Type:=msoControlPop up, _
Befo=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?


Michael Malinsky wrote:
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:=msoControlPop up,
Befo=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