Add ins and Creating Custom Menus when installed

[Jimbo]

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

[dk]

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:-

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:-

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 ]

[Jimbo]

Works PERFECTLY thank you so much.

jim