make macros (sub) work for add-in?

  • Thread starter Thread starter meow
  • Start date Start date
M

meow

Hello,

I've followed this tutorial (http://www.fontstuff.com/vba/vbatut03.htm)
to build an add-in file for my excel 2003. It lists functions when the
add-in is loaded in the function menu. However the main portions of my
code is subrountines. I run them from the Macro menu. After I saved the
file as an add-in file and loaded it into my excel, I no longer see the
subs in the macro menu. Is there a way to make them show whenever excel
is opened?

Thanks
 
No, not in an add-in, it just works that way. You can still run them though

VBA

Application.Run "myaddin.xla!mymacro"

or Excel

Tools>Macro>Macros ... (or Alt-F8) and in the Macro Name editbox type the
addin name and macro name as above.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
You could also have the add-in add items to a menu, add a new menu or add a
temporary toolbar.
A temp toolbar is probably the easiest to implement. Search XL help.
Google for "excel commandbar vba" will get you started on adding menu items.

Below is snip from an add-in that adds one item to the "Tools" menu. It
adds the item when the addin is installed and removes it when the addin is
uninstalled. I use the 'tag' property to be able to identify my item to
make sure I don't add twice or accidently delete others.

if you add items to a menu and are adding multiple macro controls, its best
to add a new dropdown menu rather than append to an existing one.

rgds - voodooJoe

Private Sub Workbook_AddinInstall()
Dim i%, crtlSKIP As Boolean, crtlUPPER As CommandBarButton

With Application.CommandBars("Tools").Controls
'check to make sure it isn't already there
For i = 1 To .Count
If .Item(i).Tag = "addin.UPPERCASE" Then Exit Sub
Next i

'add control to the end of the list
i = .Count
Set ctrlUPPER = .Add(Type:=msoControlButton, ID:=2949, before:=i +
1)
End With
'set parameters - make SURE .Tag is a *unique* identifier
With ctrlUPPER
.Caption = "UPPER case"
.OnAction = "UPPERCASE"
.Tag = "addin.UPPERCASE"
End With

Set crtlUPPER = Nothing
End Sub

'this removes the item when the add-in is uninstalled
Private Sub Workbook_AddinUninstall()
Dim i%
With Application.CommandBars("Tools").Controls
For i = 1 To .Count
If .Item(i).Tag = "addin.UPPERCASE" Then
.Item(i).Delete
Exit Sub
End If
Next i
End With
End Sub
 
Back
Top