 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2005Macro's with Ctrl or Ctrl-Shift?
When you record a new macro it asks you what Ctrl key you want to access it with. If you Edit the Properties of a Macro it asks you what Ctrl-Shift key you want to use to access a macro. How can you have more control over this to assign the proper key to a macro? Thanks. Al, I don't know what you mean by "edit the properties of a macro." You can assign a Ctrl or Ctrl-Shift combination (excluding number keys) to macros either when about to record a macro, or later with Tools - Macro - Macros - Options. If by "have more control," you mean keys other than Ctrl or Ctrl-Shift combinations, you can't assign a macro directly, but you can use the Application.Onkey method to assign pretty much any key or key combination to run a macro. -- Earl Kiosterud www.smokeylake.com > When you record a new macro it asks you what Ctrl key you want to access > it with. If you Edit the Properties of a Macro it asks you what > Ctrl-Shift key you want to use to access a macro. How can you have more > control over this to assign the proper key to a macro? Thanks. >> You can assign a Ctrl or Ctrl-Shift combination (excluding number keys) >> to macros either when about to record a macro, or later with Tools - >> Macro - Macros - Options. Under Macro Options it seems that you can only assign Ctrl. Ctrl is shown and grayed out, you can only change the letter. How can you assign Ctrl-Shift from this menu. Sorry I wasn't more clear, thanks. > Al, > [quoted text clipped - 13 lines] >> Ctrl-Shift key you want to use to access a macro. How can you have more >> control over this to assign the proper key to a macro? Thanks. Hold the shift key when you type in your letter. (The dialog will change to display ctrl-shift to give you some feedback.) (That one is gonna hurt, huh?) > >> You can assign a Ctrl or Ctrl-Shift combination (excluding number keys) > >> to macros either when about to record a macro, or later with Tools - [quoted text clipped - 21 lines] > >> Ctrl-Shift key you want to use to access a macro. How can you have more > >> control over this to assign the proper key to a macro? Thanks.  SignatureDave Peterson Earl, I think I see now, it depends if you have a lower case or upper case letter and it picks Ctrl or Ctrl-Shift. Could you explain the "OnKey Method" a bit more, searched help but did not find anything. Thanks. > Al, > [quoted text clipped - 13 lines] >> Ctrl-Shift key you want to use to access a macro. How can you have more >> control over this to assign the proper key to a macro? Thanks. Al, The OnKey method is something that runs in a macro. It's run once, and assigns any key (This, that, Shift-this, Ctrl-Shift-that, Alt-other etc) to run a macro when the key is pressed. Here are some things to consider before we get into the coding: Once in effect, it causes the key to run the macro in any open workbook in the instance of Excel that's running. It's Excel-wide, not just workbook-wide. You can code around it, but it gets a bit tiresome. It remains in effect after the workbook has been closed, unless the workbook also contains code to reset it when closed. If the workbook has been closed, pressing the key will cause the workbook to open. You can't use the key as the first character to be typed into a cell. It will trigger the macro instead. You can use it once typing in a cell, and can get around this first character problem by pressing F2 or double-clicking the cell to first get into Edit mode. To implement this, you'll need to be able to move about the Visual Basic Environment (Alt-F11). David McRitchie has a tutorial to get you started with that. If you're still interested, post back. We'll give you some code. It isn't all that much, actually. -- Earl Kiosterud www.smokeylake.com > Earl, > [quoted text clipped - 21 lines] >>> Ctrl-Shift key you want to use to access a macro. How can you have more >>> control over this to assign the proper key to a macro? Thanks. "Earl Kiosterud" wrote... ... >Once in effect, it causes the key to run the macro in any open >workbook in the instance of Excel that's running. It's Excel-wide, >not just workbook-wide. You can code around it, but it gets a bit >tiresome. ... It only requires using a global variable holding a table of keys and corresponding applicable objects in order to limit the scope of OnKey. Each macro called by OnKey would need to start with code that calls a common function subroutine to check whether the current active object has an entry for the key(s) pressed, if so return immediately with a value indicating to the calling routine that it should proceed; otherwise, use SendKeys to send the key combination to Excel and return another value to the calling routine indicating it should exit immediately. Basic table-driven programming. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.