Addin to display under a menu

[alok2007]

I have a excel addin which I want to insert under Data menu. How can I do so?
Thanks in advance

[Bob Phillips]

If you put code in the appropriate workbook open event, and delete it in the close it will exist only for that workbook.

Here is an example of a building a commandbar on the fly when you open a workbook. It adds a sub-menu to the Data menu.

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls("Data").Controls.Add( _
Type:=msoControlPopup, _
temporary:=True)
oCtl.Caption = "myButton"
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"
End With
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro2"
End With
'etc.
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("Tools").Controls("myButton").Delete
End Sub

To add this, go to the VB IDE (ALT-F11 from Excel), and in the explorer pane, select your workbook. Then select the ThisWorkbook object (it's in Microsoft Excel Objects which might need expanding). Double-click the ThisWorkbook and a code window will open up. Copy this code into there, changing the caption and action to suit.

This is part of the workbook, and will only exist with the workbook, but will be available to anyone who opens the workbook.

[alok2007]

Thanks for the code. But I have not yet tested the same. After testing the same I will get back

[alok2007]

I have tried to use the code but could not suceed. As I am not very expert in macros, please elaborate the same in some detail. My ad in nme is Audit and is installed in Tools\Addins.
I want that menu Audit should appear under Data or any other suitable menu
Is it posible to modify the code so that the Audit menu will be available whenever excel will be opened and not a particular workbook?

[Bob Phillips]

Just change MyButton to Audit, and myMacroButton to you first macro etc.

[alok2007]

I have tried but not succeed. Data menu is showing four menus ? Two as my button & two as audit & under these menus mymacrobutton & mymacrobutton2 & audit & audit2 are appearing On clicking, the required action is not performed. Rather it asking for the workbook on which I have tried your code i.e Book7.xls and showing a message Book7.xls could not be found even if I am working in a new workbook

[alok2007]

i have deleted the file book7.xls on which i had tested the code,but the menus are still available (of course not working). Can u plz tell me how can i remove those menus apperaing under data?

[alok2007]

i have deleted the file book7.xls on which i had tested the code,but the menus are still available (of course not working). Can u plz tell me how can i remove those menus apperaing under data (till the code starts functiong)?

[Bob Phillips]

Tools>customize...>Toolbars>delete