Two marcos, same shortcut

[Jan Kronsell]

In each of two workbooks I have a macro. Both are assigned to ctrl+d.
When only one of the workbooks are opened it works ok. When both workvooks
are opened, prbolems occur.

No matter which workbook I open first, its always the macro in the workbook,
where I first assigned the shortcut, that is run.

Is thetre any way, that I can ensure, that the macro in the active workbook
is run, when more macros are assigned til the same shortcut key?

Jan

 

[CurlyDave]

In each of two workbooks I have a macro. Both are assigned to ctrl+d.
When only one of the workbooks are opened it works ok. When both workvooks
are opened, prbolems occur.

No matter which workbook I open first, its always the macro in the workbook,
where I first assigned the shortcut, that is run.

Is thetre any way, that I can ensure, that the macro in the active workbook
is run, when more macros are assigned til the same shortcut key?

Jan

Depending on what time of macro it is...
You can have one workbook that just has the macro and then save it as
an add-in, when you open excel again, goto Tools=>add-ins=>select your
new add-in, the macro should work for all workbooks.
another way is to save the macro in your
Personal Macro Workbook.
If you have never opened the Personal Macro Workbook, turn on the
Macro Recorder, when the box shows to name the macro and where to
store it, select
Personal Macro Workbook
Record a macro.
Now you have a Personal Macro Workbook. Go to the VBA editor and Copy
and paste the code you want in the Personal Macro Workbook projects,
save and close the workbook.
Open excel and the macro will be available for all workbooks

 

[Dave Peterson]

I don't think so.

Maybe you can use D and d as the letters in your shortcuts????

 

[Jan Kronsell]

I agree to all below, but thats not my problem. As the worksbooks are
distributed by mail to other users, the makro has to be in the workbook, not
in the personal macro workbook. As for the Add-in the same issue apply. If I
use an add-in I have to distributed that as well, and tell users, that I do
not acually know, how to install it.

As is, I have a text in my workbooks, saying "Press Ctrl+d" to start." I
like to keep it that way, and what I would really like, is that it is always
Ctrl+d when using one the workbooks, even if two are open at the same
time.In dont know how many workbooks the users will open at the same time,
so using Ctrl+d, Ctrl+e, Ctrl+f and sp on, doesn't seem to be the solution.
But maybe its the only one?

Jan

 

[Dave Peterson]

ps.

You may want to run the macros another way:

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)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

 

[Jan Kronsell]

Hi Dave

I'm not sure, if that will do the trick. I have a lot of workbooks, that I
send to other users. In each is a single macro. They do not do excactly the
same, but do something related to the workbook they are in. Right now all
the macros are assigned to Ctrl+d. When a user opens a wokbook a text in one
of the cells tell the user "Fill in the form and Press Ctrl+d" to execute".
Så thats what the user does. Problem is, when the users opens more than one
workbook at a time, the Ctrl+d does not do the right thing, if a "wrong"
workbook is active. And that it is not so good, if the macro deletes a
worksheet that should not have been deleted, just because the wrong workbook
is open.

I can of course use diffenrent shortcuts, but one time or another, I run out
of keys. And for an untrained user, I think it would be to difficult to
remember, which shortcut goes with which workbook.

But anyway, you have almost convinced me, that it cannot be done, so I have
to think of another way.

Jan

 

[Dave Peterson]

If a single macro is used in multiple workbooks, then the code is duplicated
(like if you can have 8 macros spread over 4232 workbooks), then I would remove
all the macros in all the workbooks and build an addin that contains all 8
macros.

You could build a toolbar (xl2003 and below) that gives the user access to each
function.

Then tell the user to open this addin whenever they open one of these 4232 type
workbooks.

 

[Jan Kronsell]

I get that. And I think I wll end up with that. The problem is, that the
users dont know anything about add-ins. And there will be produced new
macros from time to time, meaning that they have to install new add-ins from
time to time. I "fear" that i will have to teach them how to install and how
to use them every time. And I)m not sure how to make buttonss in the
toolbar, that intuitively tells the user, which botton to use in which
workbook?

Jan

 

[Rick Rothstein]

You might be able to put a If..Then check as the first line of each macro that checks if the ThisWorkbook object's Name property is the name of the workbook the macro was designed to work with and, if not, call the other macro from within the current macro.... I think that should work.

 

[Dave Peterson]

Use descriptive captions on buttons. Use tooltips. Include some documentation
for the user.

Put your addin on a common network drive if possible.

And in xl2003 menus:
tools|addins|browse
Tell them to use the UNC path (\\servername\sharename\path\add.xla)
to install it.

 

[Rick Rothstein]

Actually, in thinking about it, you only need to put the test in the macro
for the first workbook you created the shortcut in.

--
Rick (MVP - Excel)


You might be able to put a If..Then check as the first line of each macro
that checks if the ThisWorkbook object's Name property is the name of the
workbook the macro was designed to work with and, if not, call the other
macro from within the current macro.... I think that should work.

 

[Jan Kronsell]

Hi Dave

I have to figure something out. The users are not connected in anyway, so a
common drive is not an option. I have to write some kind of manual I think.

Jan

 

[Jan Kronsell]

I though of that myself, but I have to do it in all the macros, as I dont
know which to, three or four the users will have open at any tine. I will go
with Daves solution, but thank you for your effort.

Jan

 

[Dave Peterson]

If you have to distribute individual files to users, give them specific
instructions where to store the file.

Each should use the same drive and folder name (like: C:\JansUntils\addin.xla)

If you ever include UDFs called from a worksheet cell, you'll be much happier
than dealing with phone calls about links not working correctly.