| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Which Macro Runs...?
Over time I've accumulated a lot of macros I've written for various things. And
inevitably, sometimes they get the same Ctrl key assigned to them since they're not expected to both be active at once. Occasionally however I'll have one spreadsheet just parked in the background while I quickly check or touch another. I keep getting screwed up when I try to execute a macro on the active sheet, but one from the idle sheet executes instead -- totally screwing up my active data. It would seem there would be some way to tell Excel that when there's unexpected conflict, just execute the macro from the active sheet. I haven't been able to stumble into it however. Is there a general solution to this general problem, or must I be more rigorous in not assigning the same letter to two different macros in unrelated spreadsheets? Which of course would beg the question what to do when more than 26 macros exist in my total Excel system... Thanks... Bill |
|
#2
|
|||
|
|||
|
Not sure if I fully understand your problem.. it is a Friday afternoon after
all. I assume you save your macros to file rather than to workbook. Either way I would suggest not using keyboard shortcuts to execute macros. Use Alt + F8 to bring up the macro box and select the macro you wish to execute. If you have to run more than 2 macros on the same sheet, you could write a macro to execute X macros one after the other. for example: Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_1" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_2" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_3" etc... I havent tested this myself however... If the macros are running on the wrong sheet because you have 2 or more open. You could set your macros to only run on a specifically "named" sheet. So if you do not rename a sheet it wont run. Hope some of this helps you, if not I hope it was an interesting read. Ernest "Bill Martin" wrote: Over time I've accumulated a lot of macros I've written for various things. And inevitably, sometimes they get the same Ctrl key assigned to them since they're not expected to both be active at once. Occasionally however I'll have one spreadsheet just parked in the background while I quickly check or touch another. I keep getting screwed up when I try to execute a macro on the active sheet, but one from the idle sheet executes instead -- totally screwing up my active data. It would seem there would be some way to tell Excel that when there's unexpected conflict, just execute the macro from the active sheet. I haven't been able to stumble into it however. Is there a general solution to this general problem, or must I be more rigorous in not assigning the same letter to two different macros in unrelated spreadsheets? Which of course would beg the question what to do when more than 26 macros exist in my total Excel system... Thanks... Bill |
|
#3
|
|||
|
|||
|
You're right -- that post was more ambiguous than intended. Let me be more
specific. The two macros are stored in two different workbooks. The macros are stored in a module within the workbook from which they are intended to be executed. One workbook is in the background unused at the moment, though loaded. When I open up the second workbook for a quick use, that workbook has a macro which randomly happens to have the same Ctrl key assigned. Somehow Excel seems to run the first macro which was opened, rather than the one from the active workbook. When I have macros which will be used widely I keep them in a common library workbook and do not have conflicted assignments. The ones which are only intended for use private in a particular workbook are kept in the relevant workbook. Bill --------------------- Ernest Lai wrote: Not sure if I fully understand your problem.. it is a Friday afternoon after all. I assume you save your macros to file rather than to workbook. Either way I would suggest not using keyboard shortcuts to execute macros. Use Alt + F8 to bring up the macro box and select the macro you wish to execute. If you have to run more than 2 macros on the same sheet, you could write a macro to execute X macros one after the other. for example: Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_1" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_2" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_3" etc... I havent tested this myself however... If the macros are running on the wrong sheet because you have 2 or more open. You could set your macros to only run on a specifically "named" sheet. So if you do not rename a sheet it wont run. Hope some of this helps you, if not I hope it was an interesting read. Ernest "Bill Martin" wrote: Over time I've accumulated a lot of macros I've written for various things. And inevitably, sometimes they get the same Ctrl key assigned to them since they're not expected to both be active at once. Occasionally however I'll have one spreadsheet just parked in the background while I quickly check or touch another. I keep getting screwed up when I try to execute a macro on the active sheet, but one from the idle sheet executes instead -- totally screwing up my active data. It would seem there would be some way to tell Excel that when there's unexpected conflict, just execute the macro from the active sheet. I haven't been able to stumble into it however. Is there a general solution to this general problem, or must I be more rigorous in not assigning the same letter to two different macros in unrelated spreadsheets? Which of course would beg the question what to do when more than 26 macros exist in my total Excel system... Thanks... Bill |
|
#4
|
|||
|
|||
|
When you assign the shortcut key to that macro, it's an application level
shortcut. I would think the safest thing to do would be to either use separate shortcut keys or give the user some other way to run the macro (a toolbar?). Bill Martin wrote: You're right -- that post was more ambiguous than intended. Let me be more specific. The two macros are stored in two different workbooks. The macros are stored in a module within the workbook from which they are intended to be executed. One workbook is in the background unused at the moment, though loaded. When I open up the second workbook for a quick use, that workbook has a macro which randomly happens to have the same Ctrl key assigned. Somehow Excel seems to run the first macro which was opened, rather than the one from the active workbook. When I have macros which will be used widely I keep them in a common library workbook and do not have conflicted assignments. The ones which are only intended for use private in a particular workbook are kept in the relevant workbook. Bill --------------------- Ernest Lai wrote: Not sure if I fully understand your problem.. it is a Friday afternoon after all. I assume you save your macros to file rather than to workbook. Either way I would suggest not using keyboard shortcuts to execute macros. Use Alt + F8 to bring up the macro box and select the macro you wish to execute. If you have to run more than 2 macros on the same sheet, you could write a macro to execute X macros one after the other. for example: Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_1" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_2" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_3" etc... I havent tested this myself however... If the macros are running on the wrong sheet because you have 2 or more open. You could set your macros to only run on a specifically "named" sheet. So if you do not rename a sheet it wont run. Hope some of this helps you, if not I hope it was an interesting read. Ernest "Bill Martin" wrote: Over time I've accumulated a lot of macros I've written for various things. And inevitably, sometimes they get the same Ctrl key assigned to them since they're not expected to both be active at once. Occasionally however I'll have one spreadsheet just parked in the background while I quickly check or touch another. I keep getting screwed up when I try to execute a macro on the active sheet, but one from the idle sheet executes instead -- totally screwing up my active data. It would seem there would be some way to tell Excel that when there's unexpected conflict, just execute the macro from the active sheet. I haven't been able to stumble into it however. Is there a general solution to this general problem, or must I be more rigorous in not assigning the same letter to two different macros in unrelated spreadsheets? Which of course would beg the question what to do when more than 26 macros exist in my total Excel system... Thanks... Bill -- Dave Peterson |
|
#5
|
|||
|
|||
|
It sounds like I'll need to troll through VBA to find a way to automagically
assign particular macros to a toolbar when that particular workbook is active. Rather than always having access to a huge tool bar whose individual elements are rarely used. Thanks... Bill ----------------------------- Dave Peterson wrote: When you assign the shortcut key to that macro, it's an application level shortcut. I would think the safest thing to do would be to either use separate shortcut keys or give the user some other way to run the macro (a toolbar?). Bill Martin wrote: You're right -- that post was more ambiguous than intended. Let me be more specific. The two macros are stored in two different workbooks. The macros are stored in a module within the workbook from which they are intended to be executed. One workbook is in the background unused at the moment, though loaded. When I open up the second workbook for a quick use, that workbook has a macro which randomly happens to have the same Ctrl key assigned. Somehow Excel seems to run the first macro which was opened, rather than the one from the active workbook. When I have macros which will be used widely I keep them in a common library workbook and do not have conflicted assignments. The ones which are only intended for use private in a particular workbook are kept in the relevant workbook. Bill --------------------- Ernest Lai wrote: Not sure if I fully understand your problem.. it is a Friday afternoon after all. I assume you save your macros to file rather than to workbook. Either way I would suggest not using keyboard shortcuts to execute macros. Use Alt + F8 to bring up the macro box and select the macro you wish to execute. If you have to run more than 2 macros on the same sheet, you could write a macro to execute X macros one after the other. for example: Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_1" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_2" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_3" etc... I havent tested this myself however... If the macros are running on the wrong sheet because you have 2 or more open. You could set your macros to only run on a specifically "named" sheet. So if you do not rename a sheet it wont run. Hope some of this helps you, if not I hope it was an interesting read. Ernest "Bill Martin" wrote: Over time I've accumulated a lot of macros I've written for various things. And inevitably, sometimes they get the same Ctrl key assigned to them since they're not expected to both be active at once. Occasionally however I'll have one spreadsheet just parked in the background while I quickly check or touch another. I keep getting screwed up when I try to execute a macro on the active sheet, but one from the idle sheet executes instead -- totally screwing up my active data. It would seem there would be some way to tell Excel that when there's unexpected conflict, just execute the macro from the active sheet. I haven't been able to stumble into it however. Is there a general solution to this general problem, or must I be more rigorous in not assigning the same letter to two different macros in unrelated spreadsheets? Which of course would beg the question what to do when more than 26 macros exist in my total Excel system... Thanks... Bill |
|
#6
|
|||
|
|||
|
I like John Walkenbach's menumaker:
http://j-walk.com/ss/excel/tips/tip53.htm I can categorize my macros and then "bury" them deeper. Kind of like tools|Macro|macros... Bill Martin wrote: It sounds like I'll need to troll through VBA to find a way to automagically assign particular macros to a toolbar when that particular workbook is active. Rather than always having access to a huge tool bar whose individual elements are rarely used. Thanks... Bill ----------------------------- Dave Peterson wrote: When you assign the shortcut key to that macro, it's an application level shortcut. I would think the safest thing to do would be to either use separate shortcut keys or give the user some other way to run the macro (a toolbar?). Bill Martin wrote: You're right -- that post was more ambiguous than intended. Let me be more specific. The two macros are stored in two different workbooks. The macros are stored in a module within the workbook from which they are intended to be executed. One workbook is in the background unused at the moment, though loaded. When I open up the second workbook for a quick use, that workbook has a macro which randomly happens to have the same Ctrl key assigned. Somehow Excel seems to run the first macro which was opened, rather than the one from the active workbook. When I have macros which will be used widely I keep them in a common library workbook and do not have conflicted assignments. The ones which are only intended for use private in a particular workbook are kept in the relevant workbook. Bill --------------------- Ernest Lai wrote: Not sure if I fully understand your problem.. it is a Friday afternoon after all. I assume you save your macros to file rather than to workbook. Either way I would suggest not using keyboard shortcuts to execute macros. Use Alt + F8 to bring up the macro box and select the macro you wish to execute. If you have to run more than 2 macros on the same sheet, you could write a macro to execute X macros one after the other. for example: Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_1" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_2" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_3" etc... I havent tested this myself however... If the macros are running on the wrong sheet because you have 2 or more open. You could set your macros to only run on a specifically "named" sheet. So if you do not rename a sheet it wont run. Hope some of this helps you, if not I hope it was an interesting read. Ernest "Bill Martin" wrote: Over time I've accumulated a lot of macros I've written for various things. And inevitably, sometimes they get the same Ctrl key assigned to them since they're not expected to both be active at once. Occasionally however I'll have one spreadsheet just parked in the background while I quickly check or touch another. I keep getting screwed up when I try to execute a macro on the active sheet, but one from the idle sheet executes instead -- totally screwing up my active data. It would seem there would be some way to tell Excel that when there's unexpected conflict, just execute the macro from the active sheet. I haven't been able to stumble into it however. Is there a general solution to this general problem, or must I be more rigorous in not assigning the same letter to two different macros in unrelated spreadsheets? Which of course would beg the question what to do when more than 26 macros exist in my total Excel system... Thanks... Bill -- Dave Peterson |
|
#7
|
|||
|
|||
|
Thanks ... I'll look into that. In the mean time I figured out how to add a
couple of buttons to a tool bar only while the particular spreadsheet is active. I'll play with that approach a bit to see how convenient I find it with real usage experience. Bill ------------------------ Dave Peterson wrote: I like John Walkenbach's menumaker: http://j-walk.com/ss/excel/tips/tip53.htm I can categorize my macros and then "bury" them deeper. Kind of like tools|Macro|macros... Bill Martin wrote: It sounds like I'll need to troll through VBA to find a way to automagically assign particular macros to a toolbar when that particular workbook is active. Rather than always having access to a huge tool bar whose individual elements are rarely used. Thanks... Bill ----------------------------- Dave Peterson wrote: When you assign the shortcut key to that macro, it's an application level shortcut. I would think the safest thing to do would be to either use separate shortcut keys or give the user some other way to run the macro (a toolbar?). Bill Martin wrote: You're right -- that post was more ambiguous than intended. Let me be more specific. The two macros are stored in two different workbooks. The macros are stored in a module within the workbook from which they are intended to be executed. One workbook is in the background unused at the moment, though loaded. When I open up the second workbook for a quick use, that workbook has a macro which randomly happens to have the same Ctrl key assigned. Somehow Excel seems to run the first macro which was opened, rather than the one from the active workbook. When I have macros which will be used widely I keep them in a common library workbook and do not have conflicted assignments. The ones which are only intended for use private in a particular workbook are kept in the relevant workbook. Bill --------------------- Ernest Lai wrote: Not sure if I fully understand your problem.. it is a Friday afternoon after all. I assume you save your macros to file rather than to workbook. Either way I would suggest not using keyboard shortcuts to execute macros. Use Alt + F8 to bring up the macro box and select the macro you wish to execute. If you have to run more than 2 macros on the same sheet, you could write a macro to execute X macros one after the other. for example: Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_1" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_2" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_3" etc... I havent tested this myself however... If the macros are running on the wrong sheet because you have 2 or more open. You could set your macros to only run on a specifically "named" sheet. So if you do not rename a sheet it wont run. Hope some of this helps you, if not I hope it was an interesting read. Ernest "Bill Martin" wrote: Over time I've accumulated a lot of macros I've written for various things. And inevitably, sometimes they get the same Ctrl key assigned to them since they're not expected to both be active at once. Occasionally however I'll have one spreadsheet just parked in the background while I quickly check or touch another. I keep getting screwed up when I try to execute a macro on the active sheet, but one from the idle sheet executes instead -- totally screwing up my active data. It would seem there would be some way to tell Excel that when there's unexpected conflict, just execute the macro from the active sheet. I haven't been able to stumble into it however. Is there a general solution to this general problem, or must I be more rigorous in not assigning the same letter to two different macros in unrelated spreadsheets? Which of course would beg the question what to do when more than 26 macros exist in my total Excel system... Thanks... Bill |
|
#8
|
|||
|
|||
|
Interesting, a menu system. Would be handy as I run several macros on the
same original set of data. Going to save this thread for later. Ernest "Bill Martin" wrote: Thanks ... I'll look into that. In the mean time I figured out how to add a couple of buttons to a tool bar only while the particular spreadsheet is active. I'll play with that approach a bit to see how convenient I find it with real usage experience. Bill ------------------------ Dave Peterson wrote: I like John Walkenbach's menumaker: http://j-walk.com/ss/excel/tips/tip53.htm I can categorize my macros and then "bury" them deeper. Kind of like tools|Macro|macros... Bill Martin wrote: It sounds like I'll need to troll through VBA to find a way to automagically assign particular macros to a toolbar when that particular workbook is active. Rather than always having access to a huge tool bar whose individual elements are rarely used. Thanks... Bill ----------------------------- Dave Peterson wrote: When you assign the shortcut key to that macro, it's an application level shortcut. I would think the safest thing to do would be to either use separate shortcut keys or give the user some other way to run the macro (a toolbar?). Bill Martin wrote: You're right -- that post was more ambiguous than intended. Let me be more specific. The two macros are stored in two different workbooks. The macros are stored in a module within the workbook from which they are intended to be executed. One workbook is in the background unused at the moment, though loaded. When I open up the second workbook for a quick use, that workbook has a macro which randomly happens to have the same Ctrl key assigned. Somehow Excel seems to run the first macro which was opened, rather than the one from the active workbook. When I have macros which will be used widely I keep them in a common library workbook and do not have conflicted assignments. The ones which are only intended for use private in a particular workbook are kept in the relevant workbook. Bill --------------------- Ernest Lai wrote: Not sure if I fully understand your problem.. it is a Friday afternoon after all. I assume you save your macros to file rather than to workbook. Either way I would suggest not using keyboard shortcuts to execute macros. Use Alt + F8 to bring up the macro box and select the macro you wish to execute. If you have to run more than 2 macros on the same sheet, you could write a macro to execute X macros one after the other. for example: Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_1" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_2" Application.Run "'Macro for xxxxxxxx.xls'!z_DONT_TOUCH_ME_3" etc... I havent tested this myself however... If the macros are running on the wrong sheet because you have 2 or more open. You could set your macros to only run on a specifically "named" sheet. So if you do not rename a sheet it wont run. Hope some of this helps you, if not I hope it was an interesting read. Ernest "Bill Martin" wrote: Over time I've accumulated a lot of macros I've written for various things. And inevitably, sometimes they get the same Ctrl key assigned to them since they're not expected to both be active at once. Occasionally however I'll have one spreadsheet just parked in the background while I quickly check or touch another. I keep getting screwed up when I try to execute a macro on the active sheet, but one from the idle sheet executes instead -- totally screwing up my active data. It would seem there would be some way to tell Excel that when there's unexpected conflict, just execute the macro from the active sheet. I haven't been able to stumble into it however. Is there a general solution to this general problem, or must I be more rigorous in not assigning the same letter to two different macros in unrelated spreadsheets? Which of course would beg the question what to do when more than 26 macros exist in my total Excel system... Thanks... Bill |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
| Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
| Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
| Date macro | Excel Discussion (Misc queries) | |||
| Macro for multiple charts | Excel Worksheet Functions | |||
Linear Mode
