Macro'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

"Al Franz" <[email protected]> wrote in message
news:[email protected]...
> 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.

"Earl Kiosterud" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Al Franz" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>>
>>
>
>

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.

"Earl Kiosterud" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Al Franz" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>>
>>
>
>

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

Al Franz wrote:
>
> >> 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.
>
> "Earl Kiosterud" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > "Al Franz" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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.
> >>
> >>
> >>
> >
> >

--

Dave Peterson

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

"Al Franz" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Earl Kiosterud" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>> "Al Franz" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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.
>>>
>>>
>>>
>>
>>
>
>

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