Add list as a subchoice to a custom menu

[stakar]

Hi there
I have create a custom menu and want to add a choice and a subchoice
that will guide the user to insert a number in an inputbox or under a
list. Then the user's selected number it will be used as parameter for
a vba code.
Eg.:
The choice is: How many rows will be marked
The subchoice : 10 (default value)

What i want is the following:
If the user choose the above subchoice then it has to be appeared an
inputbox or something like that in which he has to insert a number, eg.
45. After that the choice must shows

The choice: How many rows will be marked
The subchoice: 45

This parameter must be active all the time that the user uses the
workbook. At the end, if the user saves the workbook, the next time it
will be opened, the subchoice must have the value 45.
Look at the attachment image to see exactly what i want

Here's the code. Just copy it under a module


Code:
--------------------


Option Explicit

Dim cbMenu As CommandBarControl
Dim cbSubMenu As CommandBarControl
Public MenuSeries

Sub CreateMenu()

' creates a new menu.
' can also be used to create commandbarbuttons
' may be automatically executed from an Autpen macro or a Workbook_Open eventmacro
'Dim cbMenu As CommandBarControl
'Dim cbSubMenu As CommandBarControl
RemoveMenu ' delete the menu if it already exists
' create a new menu on an existing commandbar (the next 6 lines)
Set cbMenu = Application.CommandBars(1).Controls.Add(msoControlPopup, , , , True)
With cbMenu
.Caption = "&Joker"
.Tag = "JokerTag"
.BeginGroup = False
End With
' or add to an existing menu (use the next line instead of the previous 6 lines)
'Set cbMenu = Application.CommandBars.FindControl(, 30007) ' Tools-menu
If cbMenu Is Nothing Then Exit Sub ' didn't find the menu...


' add menuitem to menu
With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Menu Item1"
.OnAction = ThisWorkbook.Name & "!Macroname"
.Delete
End With

' add menuitem to menu
With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Menu Item2"
.OnAction = ThisWorkbook.Name & "!Macroname"
.Delete
End With

' add menuitem to submenu (or buttons to a commandbar)

' add a submenu to the submenu
Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = "&xxx"
.Tag = "SubMenu1"
.BeginGroup = True
End With

' add menuitem to submenu submenu
With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "xx1"
.OnAction = "Series_Between_Draws"
.State = msoButtonDown '--checked
MenuSeries = 1 '--parameter
End With

' add menuitem to submenu submenu
With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "xx2"
.OnAction = "Series_From_Last_Draw"
End With

Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = "Choice"
.Tag = "SubMenu2"
'.BeginGroup = True
End With

'--------Note that ---------------------------------------------

'At this point i want to put another submenu which
'it will the be the inputbox or list or something like that

'------------------------------------------------------------------

' add menuitem to menu
With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "About..."
.BeginGroup = True
.OnAction = "About"
End With

' add menuitem to menu
With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Remove this menu"
.OnAction = ThisWorkbook.Name & "!RemoveMenu"
.Style = msoButtonIconAndCaption
.FaceId = 463
.BeginGroup = True
.Delete
End With
Set cbSubMenu = Nothing
Set cbMenu = Nothing

End Sub

Sub RemoveMenu()
' may be automatically executed from an Auto_Close macro or a Workbook_BeforeClose eventmacro
On Error Resume Next
Application.CommandBars(1).Controls("&Joker").Delete
End Sub

--------------------



That's all
Thanks in advance


+-------------------------------------------------------------------+
|Filename: submenu9sz.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4832 |
+-------------------------------------------------------------------+