Macro's with Ctrl or Ctrl-Shift?

A

Al Franz

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.
 
E

Earl Kiosterud

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.
 
A

Al Franz

You can assign a Ctrl or Ctrl-Shift combination (excluding number keys)
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.
 
A

Al Franz

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.
 
D

Dave Peterson

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?)
 
E

Earl Kiosterud

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.
 
H

Harlan Grove

...
....
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.
Click to expand...
....

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.
 
Top