How do you save a macro as an add-in?

Hi Matt

In the file housing the Macro(s) go to File>Save as and select Add-in (*.xla)

Then close out of Excel Open again and go to Tools>add-ins and click the Browse button, locate your add-in and click Open.

Dave
OzGrid Business Applications

Thanks Dave, but I don't have a save as command in my file menu. It will only allow me to save it as the current name???
I tried to export it to my add-ins folder as an .xla file which worked. But when I closed out of excel and went back in to Tools>add-ins and browsed to the file I got on error message that said the "file is not a valid add-in.

Any suggestions???

Thanks again

Matt, why don't you have "Save as" on your File menu ?

Go to View>Toolbar>Customize
With "Worksheet Menu Bar" checked and highlighted, click Reset.

Is it there now ?

Dave

OzGrid Business Applications

Dave, yes I do have it on my excel screen. But on my visual basic screen I don't. So I think I'm little confused on this. It seems like I have to save the worksheet which has the macro in it as an .xla file. Is that right???

But what I really what to do is have a macro that is not linked to any spreadsheet and have it be able to run on any spreadsheet that I have open. Becuase eventually I want to email just the macro to a coworker and have it work on their computer.

Thanks again for your help!

Matt

Matt, When you create an Add-in you delete all un-needed Worksheets and then save the file as a *.xla while in Excel, not the VBE.

This means when you load the add-in (as decribed before) it will automatically open each time you open Excel, as a hidden wokbook. Then to access the macros within the Add-in, you would normaly have the Add-in create a additional menu item or/and toolbar.

It sound like you may be better off saving the code within a module with you "Personal macro workbook". This too will be open as a hidden wokbook, but you can unhide it via Windows>Unhide.

Dave

OzGrid Business Applications

Great! I saved it as an add-in and it works fine. But how do I access it, I have it checked in the add-ins available screen but I still can't get at the macro.

You wrote above that "This means when you load the add-in (as decribed before) it will automatically open each time you open Excel, as a hidden wokbook". But when I then hit the "play" to run a macro the macro isn't there? If the add-in is running in the background and the add-in contains the macro shouldn't I be able to run it?

Also, when I hit the "play" button to run the macro it asks me "Macros in:" and I chose All open workbooks and this work book but niether of them bring up the macro that I'm looking for.

Thank you, Thank you, Thank you

Ok, your nearly there then!

Open the VBE (Alt+F11) and in the "Project Explorer" (Ctrl+R) double click your Add-in file name. This should expand it.

Now double click "ThisWorkbook" and paste in this code:

Private Sub Workbook_AddinInstall()
Dim MyMacro As CommandBarButton
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Push ME!").Delete
Set MyMacro = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With MyMacro
.Caption = "Push ME!"
.Style = msoButtonCaption
.OnAction = "YourMacroNameHere"
End With
Set MyMacro = Nothing
End Sub

Private Sub Workbook_AddinUninstall()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Push ME!").Delete
End Sub

Change "Push ME!" to a caption of your choice.
Change "YourMacroNameHere" to the name of the macro within your Add-in.

Push Alt+Q and then go to Tools>Add-Ins and uncheck your Add-in. Now go back and install it.

Dave

OzGrid Business Applications