Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Thanked 0 Times in 0 Posts

    Custom Menu in Workbook (Excel 2000)

    First, this is a great place! I actually get answers!!!

    I have a custom menu which I would like to have show up ONLY in a certain workbook. Right now it shows up for me only and for all my excel workbooks. How can I attach it to a workbook, so that others can see it too?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Custom Menu in Workbook (Excel 2000)

    Here is an example change as appropriate.
    This will add a menu button right before the Help button on the main menu it has 3 items and a sub menu which leads to a different button. You will have to add the macros for the buttons. They are now linke to generic macros named "MyMacroA", "MyMacroB" and "MyMacroC"

    Add this to the code in thisWorkbook object in VB:
    <pre>Option Explicit
    Private Sub Workbook_Open()
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    End Sub</pre>

    The first adds the menu when the file is opened. The second deletes the menu when the file is closed.
    These routines are added into a normal module:

    <pre>Option Explicit
    Sub AddMenusToMenubar()
    Dim cMenu1 As CommandBarControl
    Dim cbMainMenuBar As CommandBar
    Dim iHelpMenu As Integer
    Dim cbcCustomMenu As CommandBarControl


    Set cbMainMenuBar = _
    Application.CommandBars("Worksheet Menu Bar")

    iHelpMenu = _

    Set cbcCustomMenu = _
    cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _

    cbcCustomMenu.Caption = "&Custom Menu"

    With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
    .Caption = "Menu A"
    .OnAction = "MyMacroA"
    .FaceId = 59
    End With
    With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
    .Caption = "Menu B"
    .OnAction = "MyMacroB"
    .FaceId = 29
    End With

    Set cbcCustomMenu = cbcCustomMenu.Controls.Add(Type:=msoControlPopup)
    cbcCustomMenu.Caption = "Ne&xt Menu"

    With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
    .Caption = "EightBall"
    .FaceId = 1845
    .OnAction = "MyMacroC"
    End With

    End Sub

    Sub KillMenuPopUp()
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar"). _
    Controls("&Custom Menu").Delete
    On Error GoTo 0
    End Sub</pre>

    Hope this helps to get you started,

Posting Permissions

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