If I press the "+" key, I want to call a macro.
I've looked at the OnKey Method, but it doesn't seem to handle my
situation.
How is this done?

Hi bw -

"To assign a procedure to one of the special characters (+, ^, %, and so
on), enclose the character in braces." - excerpt from the builtin Visual
Basic Help system.

Using the "+" key to call a procedure is gutsy because it's such a popular
key in and of itself... However, the following code will assign your
procedure to the "+" key in the alphanumeric section of your keyboard; the
"+" key in the numberpad section will function as normal.
-------------------------------------------------------
To have the "+" key fire your procedure in all worksheets, copy the
following code to the ThisWorkbook module. Turning off the assignment in
the Deactivate event will ensure that the "+" key is reset to its normal
function in other workbooks.

"To assign a procedure to one of the special characters (+, ^, %, and so
on), enclose the character in braces." - from the builtin Visual Basic Help
system

Private Sub Workbook_Open()
Application.OnKey "{+}", "yourProcedureHere"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "{+}"
End Sub
-------------------------------------------------------

To have the "+" key fire your procedure in one worksheet, copy the following
code to the worksheet's module:

Private Sub Worksheet_Activate()
Application.OnKey "{+}", "yourProcedureHere"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{+}"
End Sub

--
Jay


"bw" wrote:

> If I press the "+" key, I want to call a macro.
> I've looked at the OnKey Method, but it doesn't seem to handle my
> situation.
> How is this done?
>
>
>

Thanks so much, Jay.

I think I understand this and will implement as soon as I get a few
minutes. I'll post my results sometime in the future.

Again, Thanks.
Bernie


"Jay" <(E-Mail Removed)> wrote in message
news:8747133C-6C6A-49D9-B882-(E-Mail Removed)...
> Hi bw -
>
> "To assign a procedure to one of the special characters (+, ^, %, and
> so
> on), enclose the character in braces." - excerpt from the builtin
> Visual
> Basic Help system.
>
> Using the "+" key to call a procedure is gutsy because it's such a
> popular
> key in and of itself... However, the following code will assign your
> procedure to the "+" key in the alphanumeric section of your keyboard;
> the
> "+" key in the numberpad section will function as normal.
> -------------------------------------------------------
> To have the "+" key fire your procedure in all worksheets, copy the
> following code to the ThisWorkbook module. Turning off the
> assignment in
> the Deactivate event will ensure that the "+" key is reset to its
> normal
> function in other workbooks.
>
> "To assign a procedure to one of the special characters (+, ^, %, and
> so
> on), enclose the character in braces." - from the builtin Visual
> Basic Help
> system
>
> Private Sub Workbook_Open()
> Application.OnKey "{+}", "yourProcedureHere"
> End Sub
>
> Private Sub Workbook_Deactivate()
> Application.OnKey "{+}"
> End Sub
> -------------------------------------------------------
>
> To have the "+" key fire your procedure in one worksheet, copy the
> following
> code to the worksheet's module:
>
> Private Sub Worksheet_Activate()
> Application.OnKey "{+}", "yourProcedureHere"
> End Sub
>
> Private Sub Worksheet_Deactivate()
> Application.OnKey "{+}"
> End Sub
>
> --
> Jay
>
>
> "bw" wrote:
>
>> If I press the "+" key, I want to call a macro.
>> I've looked at the OnKey Method, but it doesn't seem to handle my
>> situation.
>> How is this done?
>>
>>
>>

I have this working now, but alas, not the way I expected.

When I use <Shift>+ as my key, I also have to use the <enter> key.
What I was envisioning, was to just enter <Shift>+ (or better yet, the +
on the keypad).
So, this is not possible?

Thanks again,
Bernie

"Jay" <(E-Mail Removed)> wrote in message
news:8747133C-6C6A-49D9-B882-(E-Mail Removed)...
> Hi bw -
>
> "To assign a procedure to one of the special characters (+, ^, %, and
> so
> on), enclose the character in braces." - excerpt from the builtin
> Visual
> Basic Help system.
>
> Using the "+" key to call a procedure is gutsy because it's such a
> popular
> key in and of itself... However, the following code will assign your
> procedure to the "+" key in the alphanumeric section of your keyboard;
> the
> "+" key in the numberpad section will function as normal.
> -------------------------------------------------------
> To have the "+" key fire your procedure in all worksheets, copy the
> following code to the ThisWorkbook module. Turning off the
> assignment in
> the Deactivate event will ensure that the "+" key is reset to its
> normal
> function in other workbooks.
>
> "To assign a procedure to one of the special characters (+, ^, %, and
> so
> on), enclose the character in braces." - from the builtin Visual
> Basic Help
> system
>
> Private Sub Workbook_Open()
> Application.OnKey "{+}", "yourProcedureHere"
> End Sub
>
> Private Sub Workbook_Deactivate()
> Application.OnKey "{+}"
> End Sub
> -------------------------------------------------------
>
> To have the "+" key fire your procedure in one worksheet, copy the
> following
> code to the worksheet's module:
>
> Private Sub Worksheet_Activate()
> Application.OnKey "{+}", "yourProcedureHere"
> End Sub
>
> Private Sub Worksheet_Deactivate()
> Application.OnKey "{+}"
> End Sub
>
> --
> Jay
>
>
> "bw" wrote:
>
>> If I press the "+" key, I want to call a macro.
>> I've looked at the OnKey Method, but it doesn't seem to handle my
>> situation.
>> How is this done?
>>
>>
>>

Bernie,

It works for me - Win XP and XL 2003. When I hold down the shift and then
press the =/+ key, it runs my macro. I'm trying to think if there is some
Excel setting that would produce the behavior you describe, but I can't
think of anything.

Doug


"bw" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have this working now, but alas, not the way I expected.
>
> When I use <Shift>+ as my key, I also have to use the <enter> key.
> What I was envisioning, was to just enter <Shift>+ (or better yet, the +
> on the keypad).
> So, this is not possible?
>
> Thanks again,
> Bernie
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:8747133C-6C6A-49D9-B882-(E-Mail Removed)...
>> Hi bw -
>>
>> "To assign a procedure to one of the special characters (+, ^, %, and so
>> on), enclose the character in braces." - excerpt from the builtin Visual
>> Basic Help system.
>>
>> Using the "+" key to call a procedure is gutsy because it's such a
>> popular
>> key in and of itself... However, the following code will assign your
>> procedure to the "+" key in the alphanumeric section of your keyboard;
>> the
>> "+" key in the numberpad section will function as normal.
>> -------------------------------------------------------
>> To have the "+" key fire your procedure in all worksheets, copy the
>> following code to the ThisWorkbook module. Turning off the assignment
>> in
>> the Deactivate event will ensure that the "+" key is reset to its normal
>> function in other workbooks.
>>
>> "To assign a procedure to one of the special characters (+, ^, %, and so
>> on), enclose the character in braces." - from the builtin Visual Basic
>> Help
>> system
>>
>> Private Sub Workbook_Open()
>> Application.OnKey "{+}", "yourProcedureHere"
>> End Sub
>>
>> Private Sub Workbook_Deactivate()
>> Application.OnKey "{+}"
>> End Sub
>> -------------------------------------------------------
>>
>> To have the "+" key fire your procedure in one worksheet, copy the
>> following
>> code to the worksheet's module:
>>
>> Private Sub Worksheet_Activate()
>> Application.OnKey "{+}", "yourProcedureHere"
>> End Sub
>>
>> Private Sub Worksheet_Deactivate()
>> Application.OnKey "{+}"
>> End Sub
>>
>> --
>> Jay
>>
>>
>> "bw" wrote:
>>
>>> If I press the "+" key, I want to call a macro.
>>> I've looked at the OnKey Method, but it doesn't seem to handle my
>>> situation.
>>> How is this done?
>>>
>>>
>>>
>

Jay,

In your first set of instructions, instead of this Workbook_Open I think you
want to use Workbook_Activate. Otherwise if Bernie activates another
workbook, the OnKey behavior will turn off, as you said, and then if he
reactivates the original workbook, the OnKey behavior won't be turned back
on.

Doug

"Jay" <(E-Mail Removed)> wrote in message
news:8747133C-6C6A-49D9-B882-(E-Mail Removed)...
> Hi bw -
>
> "To assign a procedure to one of the special characters (+, ^, %, and so
> on), enclose the character in braces." - excerpt from the builtin Visual
> Basic Help system.
>
> Using the "+" key to call a procedure is gutsy because it's such a popular
> key in and of itself... However, the following code will assign your
> procedure to the "+" key in the alphanumeric section of your keyboard; the
> "+" key in the numberpad section will function as normal.
> -------------------------------------------------------
> To have the "+" key fire your procedure in all worksheets, copy the
> following code to the ThisWorkbook module. Turning off the assignment in
> the Deactivate event will ensure that the "+" key is reset to its normal
> function in other workbooks.
>
> "To assign a procedure to one of the special characters (+, ^, %, and so
> on), enclose the character in braces." - from the builtin Visual Basic
> Help
> system
>
> Private Sub Workbook_Open()
> Application.OnKey "{+}", "yourProcedureHere"
> End Sub
>
> Private Sub Workbook_Deactivate()
> Application.OnKey "{+}"
> End Sub
> -------------------------------------------------------
>
> To have the "+" key fire your procedure in one worksheet, copy the
> following
> code to the worksheet's module:
>
> Private Sub Worksheet_Activate()
> Application.OnKey "{+}", "yourProcedureHere"
> End Sub
>
> Private Sub Worksheet_Deactivate()
> Application.OnKey "{+}"
> End Sub
>
> --
> Jay
>
>
> "bw" wrote:
>
>> If I press the "+" key, I want to call a macro.
>> I've looked at the OnKey Method, but it doesn't seem to handle my
>> situation.
>> How is this done?
>>
>>
>>

Hi Bernie and Doug -

1. You're right on target, Doug. Bernie, heed Doug's advice and change
the Workbook_Open event to Workbook_Activate if you need this onkey procedure
to operate at the workbook level. Workbook_Activate is more universal as
Doug recommends.

2. As for your having to press the Enter key after <Shift>+, I'm not sure
why that is. The onkey method should not require that. If you want to track
down the cause, post the code that applies and we'll take a look. We'd need
the procedure (in full) that contains the OnKey statement (Applicaton.OnKey
"{+}", "yourProcedureHere") along with the sub that is called by the keypress
(Sub yourProcedureHere()).

3. I could not recall if there is a way to capture the ‘+’ key from the
keypad. The keypad behaves differently and online help does not offer an
alias for capturing it, so I’m guessing that it can’t be done directly.
Maybe we’ll get some input from others on the subject (I’ll keep looking,
too…).

4. An interim solution follows; use the options you want and delete the
others. Note that these are worksheet level procedures; change to Workbook
procedures if needed. The first two statements set the "+/=" key so that
either '<shift>+' or '=' fire the procedure (the best solution is a single
keypress as you suggest). The third statement offers the alternative of
using the "Enter" key on the number pad which is physically close to the ‘+’
key so it might suit you just as well.

Private Sub Worksheet_Activate()
Application.OnKey "{+}", "yourProcedureHere"
Application.OnKey "=", "yourProcedureHere"
Application.OnKey "{ENTER}", "yourProcedureHere"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{+}"
Application.OnKey "="
Application.OnKey "{ENTER}"
End Sub

--
Jay
--
Jay


"Doug Glancy" wrote:

> Jay,
>
> In your first set of instructions, instead of this Workbook_Open I think you
> want to use Workbook_Activate. Otherwise if Bernie activates another
> workbook, the OnKey behavior will turn off, as you said, and then if he
> reactivates the original workbook, the OnKey behavior won't be turned back
> on.
>
> Doug
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:8747133C-6C6A-49D9-B882-(E-Mail Removed)...
> > Hi bw -
> >
> > "To assign a procedure to one of the special characters (+, ^, %, and so
> > on), enclose the character in braces." - excerpt from the builtin Visual
> > Basic Help system.
> >
> > Using the "+" key to call a procedure is gutsy because it's such a popular
> > key in and of itself... However, the following code will assign your
> > procedure to the "+" key in the alphanumeric section of your keyboard; the
> > "+" key in the numberpad section will function as normal.
> > -------------------------------------------------------
> > To have the "+" key fire your procedure in all worksheets, copy the
> > following code to the ThisWorkbook module. Turning off the assignment in
> > the Deactivate event will ensure that the "+" key is reset to its normal
> > function in other workbooks.
> >
> > "To assign a procedure to one of the special characters (+, ^, %, and so
> > on), enclose the character in braces." - from the builtin Visual Basic
> > Help
> > system
> >
> > Private Sub Workbook_Open()
> > Application.OnKey "{+}", "yourProcedureHere"
> > End Sub
> >
> > Private Sub Workbook_Deactivate()
> > Application.OnKey "{+}"
> > End Sub
> > -------------------------------------------------------
> >
> > To have the "+" key fire your procedure in one worksheet, copy the
> > following
> > code to the worksheet's module:
> >
> > Private Sub Worksheet_Activate()
> > Application.OnKey "{+}", "yourProcedureHere"
> > End Sub
> >
> > Private Sub Worksheet_Deactivate()
> > Application.OnKey "{+}"
> > End Sub
> >
> > --
> > Jay
> >
> >
> > "bw" wrote:
> >
> >> If I press the "+" key, I want to call a macro.
> >> I've looked at the OnKey Method, but it doesn't seem to handle my
> >> situation.
> >> How is this done?
> >>
> >>
> >>
>
>
>

Jay, and Doug!

Thanks for taking the time to answer my questions.
I have temporarily solved my problem by simply assigning a key,
<ctrl>a for example, to the macros I am using and this works great.
However, it's not the same as using the "+" key.

I'm sure you have already figured out that I want the "+" key on the
numeric key pad to act just like it does in Quicken (which my <ctrl>a
does currently).

As soon as I have the time, I'll try to use your suggestions again to
try to get this to work with <shift>+ (without having to press the
<Shift> key). I also will keep looking for a method to capture the "+"
key on the numeric key pad.

Much appreciation for your help.
Bernie


"Doug Glancy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Jay,
>
> In your first set of instructions, instead of this Workbook_Open I
> think you want to use Workbook_Activate. Otherwise if Bernie
> activates another workbook, the OnKey behavior will turn off, as you
> said, and then if he reactivates the original workbook, the OnKey
> behavior won't be turned back on.
>
> Doug
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:8747133C-6C6A-49D9-B882-(E-Mail Removed)...
>> Hi bw -
>>
>> "To assign a procedure to one of the special characters (+, ^, %, and
>> so
>> on), enclose the character in braces." - excerpt from the builtin
>> Visual
>> Basic Help system.
>>
>> Using the "+" key to call a procedure is gutsy because it's such a
>> popular
>> key in and of itself... However, the following code will assign your
>> procedure to the "+" key in the alphanumeric section of your
>> keyboard; the
>> "+" key in the numberpad section will function as normal.
>> -------------------------------------------------------
>> To have the "+" key fire your procedure in all worksheets, copy the
>> following code to the ThisWorkbook module. Turning off the
>> assignment in
>> the Deactivate event will ensure that the "+" key is reset to its
>> normal
>> function in other workbooks.
>>
>> "To assign a procedure to one of the special characters (+, ^, %, and
>> so
>> on), enclose the character in braces." - from the builtin Visual
>> Basic Help
>> system
>>
>> Private Sub Workbook_Open()
>> Application.OnKey "{+}", "yourProcedureHere"
>> End Sub
>>
>> Private Sub Workbook_Deactivate()
>> Application.OnKey "{+}"
>> End Sub
>> -------------------------------------------------------
>>
>> To have the "+" key fire your procedure in one worksheet, copy the
>> following
>> code to the worksheet's module:
>>
>> Private Sub Worksheet_Activate()
>> Application.OnKey "{+}", "yourProcedureHere"
>> End Sub
>>
>> Private Sub Worksheet_Deactivate()
>> Application.OnKey "{+}"
>> End Sub
>>
>> --
>> Jay
>>
>>
>> "bw" wrote:
>>
>>> If I press the "+" key, I want to call a macro.
>>> I've looked at the OnKey Method, but it doesn't seem to handle my
>>> situation.
>>> How is this done?
>>>
>>>
>>>
>
>

Jay and Doug (again).

I have installed the Worksheet_Activate procedure and it works great!
Part of my problem the first time, was that I didn't have sense enough
to use the "=" character to execute the macro, so I was always having to
use the <ctrl> character, which I didn't want.

I can't duplicate the previous problem I was having (must use <enter>
before the macro would fire), so I have dismissed that as me not paying
attention to what was happening.

In any case, I now have what I want and it works. Still, it would be
nice to know if this can be done with the "+" on the numeric keypad.

Again, you guys are great. Thanks for the help.
Bernie


"bw" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Jay, and Doug!
>
> Thanks for taking the time to answer my questions.
> I have temporarily solved my problem by simply assigning a key,
> <ctrl>a for example, to the macros I am using and this works great.
> However, it's not the same as using the "+" key.
>
> I'm sure you have already figured out that I want the "+" key on the
> numeric key pad to act just like it does in Quicken (which my <ctrl>a
> does currently).
>
> As soon as I have the time, I'll try to use your suggestions again to
> try to get this to work with <shift>+ (without having to press the
> <Shift> key). I also will keep looking for a method to capture the
> "+" key on the numeric key pad.
>
> Much appreciation for your help.
> Bernie
>
>
> "Doug Glancy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Jay,
>>
>> In your first set of instructions, instead of this Workbook_Open I
>> think you want to use Workbook_Activate. Otherwise if Bernie
>> activates another workbook, the OnKey behavior will turn off, as you
>> said, and then if he reactivates the original workbook, the OnKey
>> behavior won't be turned back on.
>>
>> Doug
>>
>> "Jay" <(E-Mail Removed)> wrote in message
>> news:8747133C-6C6A-49D9-B882-(E-Mail Removed)...
>>> Hi bw -
>>>
>>> "To assign a procedure to one of the special characters (+, ^, %,
>>> and so
>>> on), enclose the character in braces." - excerpt from the builtin
>>> Visual
>>> Basic Help system.
>>>
>>> Using the "+" key to call a procedure is gutsy because it's such a
>>> popular
>>> key in and of itself... However, the following code will assign
>>> your
>>> procedure to the "+" key in the alphanumeric section of your
>>> keyboard; the
>>> "+" key in the numberpad section will function as normal.
>>> -------------------------------------------------------
>>> To have the "+" key fire your procedure in all worksheets, copy the
>>> following code to the ThisWorkbook module. Turning off the
>>> assignment in
>>> the Deactivate event will ensure that the "+" key is reset to its
>>> normal
>>> function in other workbooks.
>>>
>>> "To assign a procedure to one of the special characters (+, ^, %,
>>> and so
>>> on), enclose the character in braces." - from the builtin Visual
>>> Basic Help
>>> system
>>>
>>> Private Sub Workbook_Open()
>>> Application.OnKey "{+}", "yourProcedureHere"
>>> End Sub
>>>
>>> Private Sub Workbook_Deactivate()
>>> Application.OnKey "{+}"
>>> End Sub
>>> -------------------------------------------------------
>>>
>>> To have the "+" key fire your procedure in one worksheet, copy the
>>> following
>>> code to the worksheet's module:
>>>
>>> Private Sub Worksheet_Activate()
>>> Application.OnKey "{+}", "yourProcedureHere"
>>> End Sub
>>>
>>> Private Sub Worksheet_Deactivate()
>>> Application.OnKey "{+}"
>>> End Sub
>>>
>>> --
>>> Jay
>>>
>>>
>>> "bw" wrote:
>>>
>>>> If I press the "+" key, I want to call a macro.
>>>> I've looked at the OnKey Method, but it doesn't seem to handle my
>>>> situation.
>>>> How is this done?
>>>>
>>>>
>>>>
>>
>>
>