Making an excel macro available to all workbooks.
Making an excel macro available to all workbooks.
(OP)
Dear Forum,
I have written a macro in excel which has usefulness to my work in a wide range of applications. I would like to have a button on my toolbar to invoke this macro from any workbook.
Currently I have the macro written in its own spreadsheet, and whenever I invoke the macro from my toolbar, the macro's spreadsheet has to open up before it can work.
Is there some way that I can attach my macro to a global template or something so that I can access this macro a bit more cleanly?
Thanks
ml
I have written a macro in excel which has usefulness to my work in a wide range of applications. I would like to have a button on my toolbar to invoke this macro from any workbook.
Currently I have the macro written in its own spreadsheet, and whenever I invoke the macro from my toolbar, the macro's spreadsheet has to open up before it can work.
Is there some way that I can attach my macro to a global template or something so that I can access this macro a bit more cleanly?
Thanks
ml





RE: Making an excel macro available to all workbooks.
RE: Making an excel macro available to all workbooks.
RE: Making an excel macro available to all workbooks.
This works pretty much the same as what Ivymike has described - only the add-in is a compiled version which cannot be opened and does not appear anywhere in the file lists, sheet lists etc, except for any buttons you may have added.
The files are saved as .xla
I've forgotten how it is done but look in the help file for "add-in's"
Regards
Mogens
RE: Making an excel macro available to all workbooks.
RE: Making an excel macro available to all workbooks.
http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q213903
RE: Making an excel macro available to all workbooks.
RE: Making an excel macro available to all workbooks.
RE: Making an excel macro available to all workbooks.
In the Auto_Open sequence the button will be delete:
Private Sub Workbook_Open()
For Each ctl In Application.CommandBars("Worksheet Menu Bar").Controls
If ctl.Caption = "NewButt" Then
k = k + 1
End If
Next ctl
If k = 0 Then Call CreateButton
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteButton
End Sub
Sub CreateButton()
Set Buton = CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlButton)
With Buton
.Visible = True
.Caption = "NewButt"
.State = msoButtonDown
.FaceId = 1715
.Style = msoButtonCaption
.TooltipText = "Make ...."
.OnAction = "YourMacro"
End With
Sub DeleteButton()
For Each ctl In CommandBars("Worksheet Menu Bar").Controls
If ctl.Caption = "NewButt" Then
ctl.Delete
End If
Next ctl
End Sub
Sub YourMacro()
Msgbox "Whatever ......."
End Sub
You have to activate the AddInn - Tools.. Add-Inns... Browse... (find your AddIn), put the tick and click "OK".
When you don't need it any more you will take the tick and the AddInn will disappear and its button too !?!?