how to create sub menu items in Excel?
 

Hi all,
I created a custom menu in Excel. I would like to create sub menu items for
one of the menu items. I get an error 'Object not supported by this method'.

I have copied the code below: any suggestions why the error pops up?

Thanks in advance for any pointers.

the code:

Private Sub Workbook_Open()

Dim obj As Object
Dim helpmenu As Object
Dim btnobj As Object
Dim SubMenuItem As Object

For Each obj In Application.CommandBars(1).Controls
If obj.Caption = "De&mo Tool" Then
obj.Delete
Exit For
End If
Next
Set obj = Application.CommandBars(1).Controls.Add(msoControl Popup)
obj.Caption = "De&mo Tool"

Set btnobj = obj.Controls.Add(msoControlButton)
btnobj.Caption = "&Save data to file"
btnobj.OnAction = ThisWorkbook.Name & "!store"
btnobj.FaceId = 600
Set btnobj = obj.Controls.Add(msoControlButton)
btnobj.Caption = "F&ilter"
btnobj.FaceId = 601
Set SubMenuItem = btnobj.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = "&Asia"
SubMenuItem.OnAction = "Macrofilterasia"
End Sub

With regards,
Josh

how to create sub menu items in Excel?
 

You can't add a button to a button which is what you are trying to do.

Set btnobj = obj.Controls.Add(msoControlButton)
should probably be msoControlPopup

--
Regards,
Tom Ogilvy


"Joshua" wrote in message
...
Hi all,
I created a custom menu in Excel. I would like to create sub menu items
for
one of the menu items. I get an error 'Object not supported by this
method'.

I have copied the code below: any suggestions why the error pops up?

Thanks in advance for any pointers.

the code:

Private Sub Workbook_Open()

Dim obj As Object
Dim helpmenu As Object
Dim btnobj As Object
Dim SubMenuItem As Object

For Each obj In Application.CommandBars(1).Controls
If obj.Caption = "De&mo Tool" Then
obj.Delete
Exit For
End If
Next
Set obj = Application.CommandBars(1).Controls.Add(msoControl Popup)
obj.Caption = "De&mo Tool"

Set btnobj = obj.Controls.Add(msoControlButton)
btnobj.Caption = "&Save data to file"
btnobj.OnAction = ThisWorkbook.Name & "!store"
btnobj.FaceId = 600
Set btnobj = obj.Controls.Add(msoControlButton)
btnobj.Caption = "F&ilter"
btnobj.FaceId = 601
Set SubMenuItem = btnobj.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = "&Asia"
SubMenuItem.OnAction = "Macrofilterasia"
End Sub

With regards,
Josh