Making A Macro Generally Available

[Sheldon]

I've written a macro (I'll call it Macro1) that multiple people will
need to use. From Macro1, (that exists in my PERSONAL.XLS), I created
an add-in on a common drive location and copied the code to
Macro1.xla, thus creating Macro1.xla as an add-in. As a test, I
created a custom button in my Excel and associated it with the drive
location and name of Macro1.xla but it gets an error when I try to run
it. I guess I'm confused with the overall process of what I need to
do to enable different users to run my macro without needing to have
their own copy. And beyond that question, how would they actually run
the macro which runs from within an Excel spreadsheet that they open.
Thanks for any/all suggestions.
Sheldon Potolsky

 

[Bernie Deitrick]

Sheldon,

The best option is to create the commandbar on the fly, when the workbook is
opened, and delete the commandbar when the workbook is closed. Follow these
instructions and example code.

In the workbook's Thisworkbook object code module, place the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandbar
End Sub

Private Sub Workbook_Open()
CreateCommandbar
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("My Bar").Visible = True
Exit Sub
NotThere:
CreateCommandbar
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("My Bar").Visible = False
End Sub

In a regular code module, place the following:

Dim myBar As CommandBar
Dim myButton As CommandBarButton

Sub CreateCommandbar()

On Error Resume Next
DeleteCommandBar

Set myBar = Application.CommandBars.Add("My Bar")
With myBar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "SayHello"
End With
End With

End Sub

Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub

Sub SayHello()
MsgBox "Hello there"
End Sub

You can add as many buttons or other menu items as you like.

HTH,
Bernie
MS Excel MVP

 

[Sheldon]

Thanks for the suggestion Bernie. One thing I neglected to mention is
that the macro I wrote would be run from any number of spreadsheets so
I'm guessing that could make a difference in using the code you so
graciously provided. I could provide a manual process of opening a
spreadsheet, pressing alt-F11, choosing the project (the .xla file)
and running the macro. Not as nifty as I'd like but the macro is a
good 100x faster than the previous (manual) process that was run so
the 3-4 users would still be very happy anyway.
Sheldon Potolsky

 

[Bernie Deitrick]

Sheldon,

Save the file as an add-in, and have every user add the file trhough Tools / Add-ins... browse for
the file.
Then it will be opened automatically when Excel is started.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Sheldon,

You will need to remove this part:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("My Bar").Visible = True
Exit Sub
NotThere:
CreateCommandbar
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("My Bar").Visible = False
End Sub

Then the commandbar will always be available to any workbook.

HTH,
Bernie
MS Excel MVP

 

[Sheldon]

Bernie,

Sorry for taking awhile to get back to you but I was working on more
of the coding for this project and I've now completed that. I did
what you said below...
** Save the file as an add-in, and have every user add the file
through Tools / Add-ins... browse for
the file. Then it will be opened automatically when Excel is
started.**
...and that worked fine. But if I want to manually add a button to
the Excel for the two current users, I don't have the visibility to
the add-in when I want to associate it with the button I created. Any
suggestions would be appreciated.
Sheldon

 

[Gord Dibben]

Sheldon

Add-in macros are not visible in Tools>Macro>Macros but you can assign a macro
to your button by just typing the name of the macro in the dialog box.

Right-click on button and "Assign Macro".

Type the name like MyMacro and OK


Gord Dibben MS Excel MVP

 

[Sheldon]

When I type in the name of my macro as you suggested, automatically
filled in for me by Excel (which I don't want) is the Excel filename.
Is this the expected "behavior" that one can expect from Excel in this
scenario? Anyway, since the macro I wrote can be run from any number
of Excel files, this doesn't work for me. However, looking elsewhere
in this forum I found where someone suggested prefacing the macro name
with the module name e.g. Module1.MyMacro and that seems to work fine
for me. I will now try it on someone else's workstation.
Thank you,
Sheldon

 

[Gord Dibben]

Excel should not append the macroname with the filename if the add-in is truly
an add-in.

Did you have "All open workbooks" selected at "macros in" dialog?

Was the add-in loaded at the time?


Gord

 

[Sheldon]

Gord,
I did have "All open workbooks" selected at "macros in" dialog.
I *think* the add-in was loaded at the time but maybe that's the
issue. How can I confirm this AND have the add-in loaded if it's
not? I am basing my *think* answer on the Tools/Add-Ins dialog and
seeing my add-in listed with a checkbox next to it under Add-Ins
available. However, if I go into VBE (alt-F11) and then Add-Ins/Add-
In Manager, I don't see my macro listed as an Available Add-In and, if
that is the issue, I'm not sure how to get it added there.
Thanks,
Sheldon

 

[Gord Dibben]

When you go to the VBE hit CTRL + r to open the Project Explorer.

Your add-in will be one of the open projects if it is loaded.

If you assigned xyz to a button and then ran the macro but go no error message
about "macro xyz could not be found" I would assume the add-in was loaded.

You will not see it in the Add-ins Manager.

The Manager only lists and looks after add-ins that supply tools and functions
to the Visual Basic development environment.

Excel workbook add-ins do not fall into this category.


Gord

 

[Sheldon]

Thank you Gord. Well, my add-in was loaded but the button still
required the module name (Module1.MyMacro) for whatever reason. I can
live with that as it works fine. Thank you again.
Sheldon Potolsky

 

[Gord Dibben]

Strange.

I cannot replicate the need to preface with Module1

A simple MyMacro will do.

If not too sensitive could you email me your workbook/add-in?

Change the AT and DOT for my email address.


Gord

 

[Dave Peterson]

Do you have another macro (sub or function) named myMacro in a different module?

How about an excel Name that uses myMacro?

 

[Dave Peterson]

Or even a module named myMacro?
Is the project named myMacro?

 

[Gord Dibben]

These I intend to scope out if I get an email.


Gord