Option Explicit

'If you put code in the appropriate workbook open event, and
'delete it in the close it will exist only for that workbook.

'Here is an example of a building a commandbar on the fly
'when you open a workbook. It adds a sub-menu to the Tools menu.

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

On Error Resume Next
Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myButton").Delete
On Error GoTo 0

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls.Add( _
Type:=msoControlPopup, _
temporary:=True)
oCtl.Caption = "myButton"
With oCtl

With .Controls.Add(Type:=msoControlPopup)
.Caption = "mySubMenu"
With .Controls.Add(Type:=msoControlButton)
.Caption = "mySubMacroButton1"
.FaceId = 161
.OnAction = "mySubMacro1"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "mySubMacroButton2"
.FaceId = 161
.OnAction = "mySubMacro2"
End With
End With

Set oCtlBtn = .Controls.Add(Type:=msoControlButton)
oCtlBtn.Caption = "myMacroButton2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro2"
End With
'etc.
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("Tools").Controls("myButton").Delete
End Sub

'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Alan M" wrote in message
...

Hi I am using this example code to create a menu in my workbook. I would
like to amend it so that submenu levels appear. ie.

Wizards-
Wizard1
Wizard2
Wizard3-
Subwizard1
Subwizard2

etc

Can anyone provide a clue for this please?