Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add list as a subchoice to a custom menu
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 Auto_Open 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(msoControl Popup, , , , 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").Dele te End Sub -------------------- That's all Thanks in advance +-------------------------------------------------------------------+ |Filename: submenu9sz.jpg | |Download: http://www.excelforum.com/attachment.php?postid=4832 | +-------------------------------------------------------------------+ -- stakar ------------------------------------------------------------------------ stakar's Profile: http://www.excelforum.com/member.php...fo&userid=6268 View this thread: http://www.excelforum.com/showthread...hreadid=547386 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding custom list and text boxes to the custom tool bar from Excel C API | Excel Discussion (Misc queries) | |||
Adding Sub Menu Item to Current Custom Menu | Excel Programming | |||
VBA - Disappearing custom menu and custom toolbar | Excel Programming | |||
Custom Menu | Excel Programming | |||
Custom Menu return to Excel Menu upon Closing | Excel Programming |