Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add command to Tools Menu (EXCEL 97/2000)

    I normally add a commandbar as new menu for my programs like this:

    Set MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoC ontrolPopup, _
    Before:=10, Temporary:=True)
    MenuObject.Caption = "&DLMIL"

    This time, i just want to add my entry at the bottom of the (existing) Tools menu...(with optional separations bar).... I'm ashamed <img src=/S/blush.gif border=0 alt=blush width=15 height=15> but cannot find how anymore... Anyone?

    Thanks,

    Erik Jan

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add command to Tools Menu (EXCEL 97/2000)

    Don't blush, I had to look it up in some previously written code. The FindControl method and Id:=30007 make it language-independent.

    <pre>Sub DeleteMenuItem()
    On Error Resume Next
    Application.CommandBars(1).FindControl(ID:=30007). Controls("&NewItem").Delete
    End Sub


    Sub AddMenuItem()
    Dim ToolsMenu As CommandBarPopup
    Dim NewMenuItem As CommandBarButton
    Call DeleteMenuItem
    Set ToolsMenu = Application.CommandBars(1).FindControl(ID:=30007)
    If ToolsMenu Is Nothing Then
    MsgBox "Can't find Tools on the Menubar"
    Exit Sub
    Else
    Set NewMenuItem = ToolsMenu.Controls.Add(Type:=msoControlButton)
    NewMenuItem.Caption = "&NewItem"
    NewMenuItem.OnAction = "FireTheSub"
    NewMenuItem.BeginGroup = True
    End If
    End Sub
    </pre>


  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add command to Tools Menu (EXCEL 97/2000)

    Thanks Hans,

    I understand the 30007 refers to the TOOLS-menu. Where/how did you look that one up?

    Erik Jan

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add command to Tools Menu (EXCEL 97/2000)

    I can't remember where you can find this, but here you have a few others:
    id=30002 >> File
    id=30003 >> Edit
    id=30004 >> View
    id=30005 >> Insert
    id=30006 >> Format
    id=30007 >> Tools
    id=30011 >> Data
    id=30022 >> Chart
    id=30009 >> Window
    id=30010 >> Help

    There must be some mysterious logic behind it. You should ask MS about this, I am sorry, but I get lost if I try to understand it.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add command to Tools Menu (EXCEL 97/2000)

    I found my code to list the IDs. Here it is:

    <pre>Sub ListMenuIDs()
    Dim CdbCtrl As CommandBarControl
    Dim Cdb As CommandBar
    Dim i As Integer
    Application.ScreenUpdating = False
    Cells(1, 1).Value = "CommandBar"
    Cells(1, 2).Value = "Control"
    Cells(1, 3).Value = "ID"
    i = 2
    For Each Cdb In CommandBars
    Cells(i, 1).Value = Cdb.Name
    i = i + 1
    For Each CdbCtrl In Cdb.Controls
    Cells(i, 2).Value = CdbCtrl.Caption
    Cells(i, 3).Value = CdbCtrl.ID
    i = i + 1
    Next
    Next
    Application.ScreenUpdating = True
    End Sub
    </pre>


Posting Permissions

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