How to use VBA to add a menu to the worksheet menu bar?

I'll give the Access crosstab a try to see if it's any easier/quicker than the option I'm using at the moment.

I currently use a program called Seagate Info Report Designer to produce a crosstab and then export that data into Excel - with any luck Access will be a bit quicker!!

I was hoping there would be a way completely within Excel, but if not, I've at least got another option to explore now.

Thanks for replies guys...

There are several ways you could do this. One way is to open the VB editor and double click the ThisWorkbook icon in the project explorer.

There are events associated with a user adding or removing an addin and you can use these to carry out the procedures to add the menu items or you can alternately use the standard Workbook_Open and Workbook_Close events. Here's some sample code which adds just one button to the main menu bar. To add a popup menu like the other ones (File, Edit, etc) change the control type constant msoControlButton to msoControlPopup. You can then put in buttons underneath this. If you want some sample code for this then let me know.

Private Sub Workbook_AddinInstall()
Dim ComBut As CommandBarButton

'Just add a simple button to the commandbar and have run macro
Set ComBut = Application.CommandBars("Worksheet Menu Bar").Controls.Add(msoControlButton)
ComBut.Caption = "&Your add-in"
ComBut.Style = msoButtonCaption
ComBut.OnAction = "AnySubYouWant"
End Sub

Private Sub Workbook_AddinUninstall()
Dim ctl As CommandBarControl

'Now delete the button with the name "&Your add-in"
For Each ctl In Application.CommandBars("Worksheet Menu Bar").Controls
If ctl.Caption = "&Your add-in" Then
ctl.Delete
End If
Next
End Sub

Regards,
Dax.

VB code to add Item to existing menu bar

This will add, lable and link your macro to a Excel menue item list. Like add an item to "Data" or "Tools" on the menu bar, it will add your item to the first group and let you lable your new item. Your macro needs to be created first so you have something to link.

Sub addMyMenuItem()
'
Dim myItem

Set myItem = CommandBars("Which menu group to use").Controls.Add(Type:=msoControlButton)
With myItem
.BeginGroup = True
.Caption = "Item title to add"
.FaceId = 0
.OnAction = "Macro to run"
End With

End Sub

I have put instructions where your items, lable and macro go. The same code below will add a menu item to the Excel menue "Data" in the first-main grouping of items a new item named "MyCode" and attach the macro "Macro1" to that new item.

Sub addMyMenuItem()
'
Dim myItem

Set myItem = CommandBars("Data").Controls.Add(Type:=msoControlButton)
With myItem
.BeginGroup = True
.Caption = "MyCode"
.FaceId = 0
.OnAction = "Macro1"
End With

End Sub

Hope this helps. JSW