Add ins and Creating Custom Menus when installed
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Add ins and Creating Custom Menus when installed

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I need to create an Addin which, when installed on a PC, creates a new menu (or ideally a Menu within Tools called References (starting a new group and with an Alt-T-R shortcut).

    I have seen this done in third party applications. Could somebody please tell me how to do this.

    Many thanks,

    Jim

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to create an Addin which, when installed on a PC, creates a new menu (or ideally a Menu within Tools called References (starting a new group and with an Alt-T-R shortcut).

    I have seen this done in third party applications. Could somebody please tell me how to do this.

    Many thanks,

    Jim
    Hi Jim,

    How about this? It needs to go in the workbook code module:-

    Code:
    Private Sub Workbook_Open()
    Dim Con As CommandBarControl, ConPop As CommandBarPopup
    Dim ComBut As CommandBarButton
    
    'Attempt to delete any menu just in case Excel was
    'closed down improperly or crashed.
    For Each Con In Application.CommandBars("Tools").Controls
        If Con.Caption = "&References" Then
            Con.Delete
        End If
    Next
    
    'Add a new popup control called References
    Set ConPop = Application.CommandBars("Tools").Controls.Add(msoControlPopup)
    ConPop.BeginGroup = True
    ConPop.Caption = "&References"
    
    'Now add a button to References
    Set ComBut = ConPop.Controls.Add(msoControlButton)
    ComBut.Caption = "&Sub item 1"
    ComBut.OnAction = "ProcedureName1"
    ComBut.FaceId = 2152    'You can have choose from one of the hundreds of FaceIDs available
    
    'Now add a second one, you can do this for as many items as you want
    Set ComBut = ConPop.Controls.Add(msoControlButton)
    ComBut.Caption = "&Sub item 2"
    ComBut.OnAction = "ProcedureName2"
    ComBut.FaceId = 33    'You can have choose from one of the hundreds of FaceIDs available
    
    End Sub
    You'll also need to put something in the Workbook_Close event so that the menu is removed if the user uninstalls the add-in:-

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Con As CommandBarControl
    'Remove References menu item from Tools
    For Each Con In Application.CommandBars("Tools").Controls
        If Con.Caption = "&References" Then
            Con.Delete
        End If
    Next
    End Sub
    Any problems please let me know,

    D


    [ This Message was edited by: dk on 2002-03-19 05:22 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Works PERFECTLY thank you so much.

    jim

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com