MACRO MODIFICATION, onkey

Question

hi,  i am trying to modify a macro from use of:  hitting 1 key,  to:  2 keys,  to make a macro work.  although i can have it perform any function wanted, i have included the home example at the bottom (lets home key go to more than 1 location in a line).  thanks in advance.

    Application.OnKey "{HOME}", "myHome"  'name myhome as desired

using example of:  "ALT"  and:  "a"  keys.  STATUS:  got just the "a" key to work by itself,  but in trying to combine with ALT, there is a problem.

would seem i need some other way to define the object for the:  ALT  key

these do not seem to work:

    'Application.OnKey "{ALT}" And "{A}", "Cut1"    'failed

    'Application.OnKey "{ALT}" + "{A}", "Cut1"   'failed

    'Application.OnKey "{ALT+a}", "cut1"    'method of object failed

    'Application.OnKey "ALT+a", "cut1"      'failed

    'Application.OnKey "ALT", "cut1"      'failed

    'Application.OnKey "Alt", "cut1"      'failed

    'Application.OnKey "{ALT}", "myend"      'failed

    'Application.OnKey "{Alt}", "myend"      'failed

    'Application.OnKey "{a}", "cut1"      'progress, a works for "end", prob: Cut1 cannot be found, oops, not case sens macro or ltr A,

    Application.OnKey "{ALT+a}", "cut1"     'ohono, starting over (but cannot be found is after error), stuck: ALT+a does not work

==========  ANSWER:

    Application.OnKey "%{a}", "cut1"     'ANSWER for: cntrl alt f12: "^%{F12}"    close all files & reopen

    Application.OnKey "%3", "cut4"     'PROBLEM:   numbers do not seem to work,  ANSWER:  rem brackets around 3.

PROBLEM:    SHORT CUTS IN TOOLBARS FOR MACROS HAVE TO BE UNDONE:  by removing ampersand infront of the intended  ALT-shortcut.  IS THERE A WAY TO MAKE LINKS WORK FROM the  OnKey   or some other work around ??

( a in brackets,  or cut1 items are Not case sensitive)

==========  extra info:  (what using the home key to do:  2 home destinations instead of just one)

Sub myHome()    'Home key has 2 destinations automated

    Dim L2 As String

    L2 = Range("L2")

    Dim N8 As String

    N8 = Range("N8")

If ActiveCell.Column = Range(L2).Column Then   'manual:  "DD:DD"

Cells(ActiveCell.Row, N8).Select        'manual:  "DM"

Else

Cells(ActiveCell.Row, L2).Select        'manual:  "DD"

End If

'L2 is a fixed/ permanent cell location that has a dynamic reference:  (4 is row formula resides in:  no $4 sign = DD:DD)

'=SUBSTITUTE(SUBSTITUTE(CELL("address",$DD4),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$DD4),"$",""),ROW(),"")

End Sub

==========    END KEY MULTIPLE DESTINATIONS

Sub myend()    'insert in a standard module, part 2 of reassign End key has 3 destinations automated

    Dim L2 As String    'home    CT

    L2 = Range("L2")

    Dim L3 As String    'fm    DI

    L3 = Range("L3")

    Dim L4 As String    'to    DP

    L4 = Range("L4")

If ActiveCell.Column = Range(L3).Column Then

Cells(ActiveCell.Row, L4).Select

Else

If ActiveCell.Column = Range(L4).Column Then

Cells(ActiveCell.Row, L2).Select

Else

If ActiveCell.Column > Range(L3).Column Then    'os, works with 1 endif here

If ActiveCell.Column < Range(L4).Column Then

Cells(ActiveCell.Row, L4).Select

End If

Else

Cells(ActiveCell.Row, L3).Select

'End If  'extra

End If

End If

End If

'L2 is a fixed/ permanent cell location that has a dynamic reference:  (2 is row formula resides in:  no $2 sign = CT:CT)

'=SUBSTITUTE(SUBSTITUTE(CELL("address",$CT2),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$CT2),"$",""),ROW(),"")

End Sub

Answer 1

Hi,

try this..

Application.OnKey "%3", "cut4"

don't use the letters 'a' or 'b'

try  "%g"  "%h", "%k",...

Answer 2

Yes, you are right,

in xl2002, pressing ALT+a,

is opening the: File (on the ribbon)

and ALT+b, is opening the: Help

(in xl2003, 2007,... it is okay)

Answer 3

hah.. got answer same time.. basically,  until i did a refresh.

i have run out of keys..  reason not to use:  "a"  ?   otherwise it is working.  most other keys already used.

been having a problem with excel having 2 iteration of file open in task manager,  and macros in module 1 not working.  with bypass using onkey,  have to go around making adjustments to macros so will work both ways..  Problem:  links made in toolbar no longer work,  wonder if work around for that  (vb wise?,  maybe try to link 'links' to the onkey?)

if get problem fixed  (sec app work over time or op sys out of wack..)

losing a day of work looking this rap up.  no answers on other posts..   cannot get answer on how to repair xp op sys.   thanks..

Answer 4

yep...  answer for:  Alt-a

==========  ANSWER:

    Application.OnKey "%{a}", "cut1"     'ANSWER for: cntrl alt f12: "^%{F12}"

    Application.OnKey "%3", "cut4"     'PROBLEM:   numbers do not seem to work,  ANSWER:  remove the brackets,  close all files & reopen