Results 1 to 1 of 1

to handle onclick event of menu item

Threaded View

  1. #1
    Registered User
    Join Date
    02-16-2009
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    78

    to handle onclick event of menu item

    Hi,

    I have a written a code to create a menu during workbook open event.
    The AddMenus is called during Workbook Open event
    Sub AddMenus()
    
    Dim cMenu1 As CommandBarControl
    Dim cbMainMenuBar As CommandBar
    Dim iHelpMenu As Integer
    Dim cbcCutomMenu As CommandBarControl
    
    '(1)Delete any existing one. We must use On Error Resume next _ in case it does not exist.
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("&Menu").Delete
    On Error GoTo 0
    '(2)Set a CommandBar variable to Worksheet menu bar
     Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
    '(3)Return the Index number of the Help menu. We can then use  this to place a custom menu before.
     iHelpMenu = cbMainMenuBar.Controls("Help").Index
     '(4)Add a Control to the "Worksheet Menu Bar" before Help.
     'Set a CommandBarControl variable to it
     Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)
     '(5)Give the control a caption
     cbcCutomMenu.Caption = "&Menu"
    '(6)Working with our new Control, add a sub control and  give it a Caption and tell it which macro to run (OnAction).
     With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
            .Caption = "Get Well Information"
           .OnAction = Module1.CreateWorkBook
           
    End With
     '(6a)Add another sub control give it a Caption and tell it which macro to run (OnAction)
     With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                    .Caption = "Menu 2"
                   ' .OnAction = "MyMacro2"
     End With
     
    End Sub
    Now what happens is when the menu is created Module1.CreateWorkbook is called (.OnAction = Module1.CreateWorkBook), but ideally it should be called after the menu created and then when i click the menu item "Get Well Information"..

    Do u i have add any special event for onclick of menu item "Get Well Information" ?
    Last edited by Ranjani; 02-20-2009 at 08:20 PM. Reason: Solved

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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