+ Reply to Thread
Results 1 to 1 of 1

Add list as a subchoice to a custom menu

Hybrid View

  1. #1
    Registered User
    Join Date
    02-18-2004
    Location
    Greece
    Posts
    3

    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

    
    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(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
    Attached Images Attached Images

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1