View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Michael Malinsky Michael Malinsky is offline
external usenet poster
 
Posts: 24
Default Error when creating menu item on command bar

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