Making an excel macro available to all workbooks. 4

[ludvik]

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

 

[ivymike]

put it in a "personal macro workbook," which you should then place in your excel startup directory (tools|options|general|alternate startup directory). Then use window|hide to hide that workbook, so that it won't be visible when you start excel.

 

[ludvik]

thanks ivymike, that worked well.

 

[mgp]

You can create an add-in macro
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

 

I have seen some Excel Addins (*.xla) where the code is locked. If I double-click on the file in the VBA Macro editor, I get the message "Project is Unviewable". How can I do this to my code so that only me (owner) can access the code?

 

[ivymike]

I've had some trouble doing that with Excel 97, but here's an article I found on the subject for Excel2k:

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q213903

 

[ivymike]

Okay, I think I've got it. In Excel 97, VB editor, go to file|properties and set the "lock" information. Then go back to Excel and save your worksheet as a .vba file. Put it in your startup directory, and you should be rolling.

 

Thanks for the tips. I am not sure it'll work with XL2000, but I'll try.

 

[FaneDuru]

You may make an AddInn (.xla), which will have in the Autpen sequence some code to put the peculiar button on a peculiar bar.
In the Autpen sequence the button will be delete:

Private Sub Workbook_Open()
For Each ctl In Application.CommandBars("Worksheet Menu Bar&quot.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&quot.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&quot.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 !?!?