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 wrote:
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

Dave Peterson wrote:

I don't think so.

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

Jan Kronsell wrote:

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

--

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 wrote:

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 wrote:
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

Dave Peterson wrote:

I don't think so.

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

Jan Kronsell wrote:

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

--

Dave Peterson

--

Dave Peterson

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

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.

--
Rick (MVP - Excel)


"Jan Kronsell" wrote in message ...
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

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.

Jan Kronsell wrote:

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

--

Dave Peterson

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)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Jan Kronsell" wrote in message
...
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

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

Dave Peterson wrote:
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.

Jan Kronsell wrote:

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

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

Rick Rothstein wrote:
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)


"Rick Rothstein" wrote in
message ...
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" wrote in message
...
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

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.

Jan Kronsell wrote:

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

Dave Peterson wrote:
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.

Jan Kronsell wrote:

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

--

Dave Peterson