Sharing and Changing a Macro in Multiple Workbooks

[Guest]

Greetings:

I have a macro that I want to use in multiple workbooks. If possible, I
want all the workbooks that need this macro to use the same module rather
copy the module separately to each workbook. That way, if the macro is
changed, all workbooks will get the change(s) automatically. Is this
possible?

Thanks in advance for your help.

 

[Bob Phillips]

Create an add-in.
http://support.microsoft.com/default.aspx?kbid=211563
How to create an add-in file in Excel 2000


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modc...
Building Application Specific Add-Ins


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modc...
Excel Add-Ins


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modc...
Add-ins, Templates, Wizards, and Libraries


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[AndrewArmstrong]

Barry,
You could create a custom button in the toolbar, then when each
workbook is open click the button to run the macro on that workbook,
but the main workbook will open, or has to be open already.
Or you could write a line in the macro that opens the other workbooks,
runs the macro, then closes the workbooks, like

This can be changed to whatever, this will run on workbook open

Private Sub Workbook_Open()

'open other workbook
Workbooks.Open "file path"

'run macro "test" in this workbook on the other workbook
Application.Run ("Test")

'close the other workbook
ActiveWorkbook.Close

End Sub

Hope this helps!
Andrew Armstrong

 

[Dave Peterson]

You may want to consider creating a dedicated workbook with the code. Save that
workbook as an addin. And give the users an interface to run the macro.

(Write the code to work against the activesheet--so you don't rely on
sheetnames???).

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Guest]

Bob, Andrew & Dave

Thank you for your suggestions. I haven't had a chance to try them out yet.
I will post a response when I have done so.

--
Barry Carroll

(Cleverly disguised as a responsible adult)
---------
PSC Scanning, Inc. assumes no responsibility whatsoever for any statements
made by me. I''m entirely on my own.

You may want to consider creating a dedicated workbook with the code. Save that
workbook as an addin. And give the users an interface to run the macro.

(Write the code to work against the activesheet--so you don't rely on
sheetnames???).

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Guest]

Dave:

I ended up using your toolbar method and attaching the macro module to a
template file. It works well, except for one thing: after the template has
been saved as normal workbook, the macros no longer work, as they are still
associated with the template file, which is no longer open. Closing and
reopening the workbook solves this problem.

Is there a way to change the macro association from the template file to the
workbook file automatically, without closing and reopening the file?

Thank you for your help.
--
Barry Carroll

(Cleverly disguised as a responsible adult)
---------
PSC Scanning, Inc. assumes no responsibility whatsoever for any statements
made by me. I''m entirely on my own.

You may want to consider creating a dedicated workbook with the code. Save that
workbook as an addin. And give the users an interface to run the macro.

(Write the code to work against the activesheet--so you don't rely on
sheetnames???).

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Dave Peterson]

The suggestion I made didn't require that the workbook be saved as a template
file.

There's a line in the code that assigns the macro to the buttons with something
like:

.onaction = "'" & thisworkbook.name & "'!somemacronamehere"

You could do the same kind of thing to reassign the macro to the newly saved
(and named) workbook.

But if these are macros that you're placing in a template, then I'd stop that.
Instead of having 100's of versions of the same code existing in 100's of
workbooks, create an addin that builds the toolbar (and deletes it when the
addin closes).

You can tell the users that when they want to run the supported macros, they
have to open the addin file. If the macros are common enough, you could even
tell the users to install them so that they're always available--tools|addins.

If you keep the macros in lots and lots of workbooks, you're gonna have a heck
of a time when you find out that there's a bug to be fixed. Or if someone just
comes by with an enhancement request.

Dave:

I ended up using your toolbar method and attaching the macro module to a
template file. It works well, except for one thing: after the template has
been saved as normal workbook, the macros no longer work, as they are still
associated with the template file, which is no longer open. Closing and
reopening the workbook solves this problem.

Is there a way to change the macro association from the template file to the
workbook file automatically, without closing and reopening the file?

Thank you for your help.

 

[Guest]

Dave:

I see your point about proliferating copies of my macro. Using an add-in
file would make maintenance much easier.

If possible, however, I still want to associate the macro with a template
file and the workbooks generated from it. Is it possible for the template
file to install the add-in when opening and uninstall it when closing?

Thank you again.

 

[Dave Peterson]

You can have a procedure that looks to see if your addin is already open. If
it's not, then open it. If it is, don't do anything more.

Option Explicit
Sub Auto_Open()
Dim myWkbk As Workbook

Set myWkbk = Nothing
On Error Resume Next
Set myWkbk = Workbooks("book1.xla")
On Error GoTo 0

If myWkbk Is Nothing Then
'open it
Workbooks.Open Filename:="C:\my documents\excel\book1.xla"
End If

End Sub

 

[Guest]

Dave:

Here is the audo_open subroutine as I inplemented it

#######
Sub Auto_Open()
Dim homeShare As String
Dim addInPath As String
Dim addInName As String
Dim AddInFullName As String
Dim myWkbk As Workbook

homeShare = Environ("USERPROFILE")
addInPath = "\Application Data\Microsoft\AddIns\"
addInName = "tsttbltoolbar.xla"
AddInFullName = homeShare + addInPath + addInName
MsgBox (AddInFullName)

Set myWkbk = Nothing
On Error Resume Next
Set myWkbk = Workbooks(addInName)
On Error GoTo 0

If myWkbk Is Nothing Then
MsgBox ("Opening the toolbar")
'open it
Workbooks.Open Filename:=AddInFullName
End If
End Sub
#######

I have verified that the pathname I build is correct for the add-in file,
and that the logic works: the if statement succeeds and the Open logic
executes. However, the toolbar doesn't appear.

I can't see what I have done wrong. Can you.

Again, thank you for all your help.

--
Barry Carroll

(Cleverly disguised as a responsible adult)
---------
PSC Scanning, Inc. assumes no responsibility whatsoever for any statements
made by me. I''m entirely on my own.

You can have a procedure that looks to see if your addin is already open. If
it's not, then open it. If it is, don't do anything more.

Option Explicit
Sub Auto_Open()
Dim myWkbk As Workbook

Set myWkbk = Nothing
On Error Resume Next
Set myWkbk = Workbooks("book1.xla")
On Error GoTo 0

If myWkbk Is Nothing Then
'open it
Workbooks.Open Filename:="C:\my documents\excel\book1.xla"
End If

End Sub

 

[Dave Peterson]

What's the name of the routine that builds the toolbar? Are you using
Auto_Open?

If yes, then you'll have to run that Auto_Open procedure in code. (I should
have mentioned it earlier--but I didn't think of it.)

The last portion of your code could look like:

If myWkbk Is Nothing Then
MsgBox "Opening the toolbar"
'open it
Set myWkbk = Workbooks.Open(Filename:=AddInFullName)
myWkbk.RunAutoMacros which:=xlAutoOpen
End If

====
As an aside: If the code that creates the toolbar were in the workbook_open
event, then you would have been ok.

Dave:

Here is the audo_open subroutine as I inplemented it

#######
Sub Auto_Open()
Dim homeShare As String
Dim addInPath As String
Dim addInName As String
Dim AddInFullName As String
Dim myWkbk As Workbook

homeShare = Environ("USERPROFILE")
addInPath = "\Application Data\Microsoft\AddIns\"
addInName = "tsttbltoolbar.xla"
AddInFullName = homeShare + addInPath + addInName
MsgBox (AddInFullName)

Set myWkbk = Nothing
On Error Resume Next
Set myWkbk = Workbooks(addInName)
On Error GoTo 0

If myWkbk Is Nothing Then
MsgBox ("Opening the toolbar")
'open it
Workbooks.Open Filename:=AddInFullName
End If
End Sub
#######

I have verified that the pathname I build is correct for the add-in file,
and that the logic works: the if statement succeeds and the Open logic
executes. However, the toolbar doesn't appear.

I can't see what I have done wrong. Can you.

Again, thank you for all your help.

--
Barry Carroll

(Cleverly disguised as a responsible adult)
---------
PSC Scanning, Inc. assumes no responsibility whatsoever for any statements
made by me. I''m entirely on my own.

You can have a procedure that looks to see if your addin is already open. If
it's not, then open it. If it is, don't do anything more.

Option Explicit
Sub Auto_Open()
Dim myWkbk As Workbook

Set myWkbk = Nothing
On Error Resume Next
Set myWkbk = Workbooks("book1.xla")
On Error GoTo 0

If myWkbk Is Nothing Then
'open it
Workbooks.Open Filename:="C:\my documents\excel\book1.xla"
End If

End Sub

 

[Guest]

Dave:

The toolbar shows up correctly, now. I also added an auto_close routine
that closes the toolbar and unloads the add-in when the template file closes.


Thank you very much for your help.
--
Barry Carroll

(Cleverly disguised as a responsible adult)
---------
PSC Scanning, Inc. assumes no responsibility whatsoever for any statements
made by me. I''m entirely on my own.

What's the name of the routine that builds the toolbar? Are you using
Auto_Open?

If yes, then you'll have to run that Auto_Open procedure in code. (I should
have mentioned it earlier--but I didn't think of it.)

The last portion of your code could look like:

If myWkbk Is Nothing Then
MsgBox "Opening the toolbar"
'open it
Set myWkbk = Workbooks.Open(Filename:=AddInFullName)
myWkbk.RunAutoMacros which:=xlAutoOpen
End If

====
As an aside: If the code that creates the toolbar were in the workbook_open
event, then you would have been ok.