Add ins and Creating Custom Menus when installed

Jimbo

New Member
Joined
Mar 18, 2002
Messages
12
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top