How to add a submenu to a submenu?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tan - 03 Oct 2006 06:05 GMT

Hi all,

I have already wrote a vba routine for custom menu. It reads the level of
menu ranking level: 1, 2, 3 from my worksheet called MenuSheet. My MenuSheet
has 5 columns, namely Level, Caption, Macro, Divider and FaceID. I m trying
to add a submenu to a submenu and not sure the walkaround. Can someone throw
me some light. Thanks.

Code as follows:

Sub CreateMenu()

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
'Dim MenuItem As CommandBarButton
Dim SubMenuItem As CommandBarButton
'Dim NextSubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
''''''''''''''''''''''''''''''''''''''''''''''''''''

' Make sure the menus aren't duplicated
Call DeleteMenu

' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True

Case 3 ' A SubMenu Item
Set SubMenuItem =
MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True

End Select
Row = Row + 1
Loop
End Sub

Reply to this Message

Bob Phillips - 03 Oct 2006 09:38 GMT

The code already handles a third level.

All you need to do is to add another row in the worksheet immediately below
its parent with a level of 3. On the parent (level 2 item) make sure that
there is no faceid otherwise the code will fail.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi all,
>
[quoted text clipped - 76 lines]
> Loop
> End Sub

Reply to this Message

Tan - 03 Oct 2006 10:33 GMT

Hi Phillips,

I needs the code to handle a fourth level. Submenu is my third level.

Rgds,

> The code already handles a third level.
>
[quoted text clipped - 85 lines]
> > Loop
> > End Sub

Reply to this Message

Bob Phillips - 03 Oct 2006 13:22 GMT

Sub CreateMenu()

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
'Dim MenuItem As CommandBarButton
Dim SubMenuItem As Object
Dim SubSubMenuItem As CommandBarButton
'Dim NextSubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
''''''''''''''''''''''''''''''''''''''''''''''''''''

' Make sure the menus aren't duplicated
'Call DeleteMenu

' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlButton)
If FaceId <> "" Then MenuItem.FaceId = FaceId
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If Divider Then MenuItem.BeginGroup = True

Case 3 ' A SubMenu Item
If NextLevel = 4 Then
Set SubMenuItem =
MenuItem.Controls.Add(Type:=msoControlPopup)
Else
Set SubMenuItem =
MenuItem.Controls.Add(Type:=msoControlButton)
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
SubMenuItem.OnAction = PositionOrMacro
End If
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If Divider Then SubMenuItem.BeginGroup = True

Case 4 ' A SubSubMenu Item
Set SubSubMenuItem =
SubMenuItem.Controls.Add(Type:=msoControlButton)
SubSubMenuItem.Caption = Caption
SubSubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubSubMenuItem.FaceId = FaceId
If Divider Then SubSubMenuItem.BeginGroup = True

End Select
Row = Row + 1
Loop
End Sub

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi Phillips,
>
[quoted text clipped - 91 lines]
> > > Loop
> > > End Sub

Reply to this Message

Tan - 04 Oct 2006 04:59 GMT

Hi Phillips,

Thanks for helping me. I greatly appreciate. Can we exchange any sharing in
future between us? My email is ringo.tan@hotmail.com from Singapore. Whats
your email?

Best Regards,
Tan

> Sub CreateMenu()
>
[quoted text clipped - 181 lines]
> > > > Loop
> > > > End Sub

Reply to this Message

Bob Phillips - 04 Oct 2006 09:17 GMT

I frequent the newsgroups regularly, that is where I answer questions, so
that all may share in the responses.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi Phillips,
>
[quoted text clipped - 190 lines]
> > > > > Loop
> > > > > End Sub

Reply to this Message

Andy Wiggins - 03 Oct 2006 22:38 GMT

This page might help:
http://www.bygsoftware.com/Excel/InterFace/menu_maker.htm

The Excel download file is here:
http://www.bygsoftware.com/examples/zipfiles/BygMenuMaker.zip

It can create menus to over 200 levels.

Signature

Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

> Hi all,
>
[quoted text clipped - 79 lines]
> Loop
> End Sub

Reply to this Message

Rate this thread: