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(msoControlPopup)
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

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" <[email protected]> wrote in message
news:[email protected]...
> 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(msoControlPopup)
> 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