×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Making an excel macro available to all workbooks.
4

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

RE: Making an excel macro available to all workbooks.

2
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.

RE: Making an excel macro available to all workbooks.

(OP)
thanks ivymike, that worked well.

RE: Making an excel macro available to all workbooks.

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

RE: Making an excel macro available to all workbooks.

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?

RE: Making an excel macro available to all workbooks.

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.

RE: Making an excel macro available to all workbooks.

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

RE: Making an excel macro available to all workbooks.

You may make an AddInn (.xla), which will have in the Auto_Open sequence some code to put the peculiar button on a peculiar bar.
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 !?!?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources