ADD-INS showing in Tools Menu

[Guest]

I've made some custom add-ins and have also followed cook book examples. My
Excel does not show these add-ins in the Tools Menu- although the cookbooks
imply it should. I can get the add-ins that come with MS Excel to show in
the Tools menu. I need a way to execute my custom macros using any MS Excel
file opened. Is there a trick in being able to run Macros either from a
toolbar or other menu source for any openned Excel file when the open Excel
file does not contain the VB code? How can I see and execute these macro
commands?

 

[davesexcel]

Did you save it as an add-in

 

[Guest]

Yes. I saved it as an .xla following the standard cook book procedures. It
seems that creating a custom toolbar may be an answer and saving that as an
xla. Where the commands in the toolbar execute the macros. I've read
several discussions and will experiement. Don't know if that works. Thanks.

 

[Ron de Bruin]

Hi VB Office Bruce

Do you use Excel 2003 ?

 

[Ron de Bruin]

Try to run this code first to see if it is working

Sub MenuBar_Item_Item()
Dim MenuItem As CommandBarControl
MenuBar_Item_Item_Delete

Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu
If MenuItem Is Nothing Then Exit Sub
With MenuItem.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Ron de Bruin"
.OnAction = ThisWorkbook.Name & "!TestMacro"
.BeginGroup = True
.Tag = "MenuItemTag"
End With
Set MenuItem = Nothing
End Sub

Sub MenuBar_Item_Item_Delete()
Dim MenuItem As CommandBarControl
Set MenuItem = Application.CommandBars.FindControl(Tag:="MenuItemTag")
If Not MenuItem Is Nothing Then
MenuItem.Delete
End If
Set MenuItem = Nothing
End Sub

 

[Guest]

Hi Ron de Bruin,

I am using Excel 2000 on one computer and Excel 2003 on another. Code
worked fine on 2003- will it work on 2000 version. Understood that direction
and modified it to run the Macro I created. Again, the code worked
PERFECTLY!!!!!

If I turn it into an Add-In, do I put the code in ThisWorkbook and then
follow the procedure or can I leave it as a module?

Thanks a lot!

 

[Ron de Bruin]

You can run the macro in the open and close event in the thisworkbook module of the add-in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MenuBar_Item_Item_Delete
End Sub

Private Sub Workbook_Open()
MenuBar_Item_Item
End Sub

 

[Guest]

Is there a difference between saving it as a xla file and saving it as an
Add-In? I followed the save as Add-In protocol, yet it really came down to
saving it as an xla file with some descriptions added and security? Is there
something I'm missing?

 

[Guest]

Ron de Bruin, YOU ARE AN MVP!!!!! Does this mean that before any xls file
can use this Maco, once the excel application is loaded with this ADD-IN? In
other words, it will always show up in the menu? Thanks again!!!!

 

[Tom Ogilvy]

If you want your addin to appear in the selections for Tools=>Addins so the
user doesn't have to browse for it, then you need to save it or copy it to
the Addin Directory. You can find this by running this code

Sub ShowPath()
msgbox Application.LibraryPath
End Sub

for me (demo'd from the immediate window)

? Application.LibraryPath
C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\LIBRARY

as an example.

 

[Ron de Bruin]

You can install your add-in with Tools>Add-ins
Browse to the file
OK
You see it now in the list
Check it
OK

Every time you open Excel your add-in will load and you can use the menu
in Tools in every workbook.

 

[Guest]

Hi Ron de Bruin

It works againa GREAT!! Thank you. I checked out your website. It's also
very helpful.

Thanks again, Bruce