OnKey question

[Eli Panic]

I have a control page with a single cell named range 'LETTER' to determine my index letter for filtered lists on another sheet. I want a shortcut to be able to change the index letter without having to leave my list sheet. To do this I have set up the following:

Sub HotKeys()
Application.OnKey "^%+A", "'ChangeLetter ""A""'"
Application.OnKey "^%+B", "'ChangeLetter ""B""'"
Application.OnKey "^%+C", "'ChangeLetter ""C""'"
Application.OnKey "^%+D", "'ChangeLetter ""D""'"
...
Application.OnKey "^%+Z", "'ChangeLetter ""Z""'"
End Sub

Sub ChangeLetter(Arg As String)
Worksheets("Control").Range("LETTER") = Arg
End Sub

The code is activated and deactivated with the appropriate Worksheet_Activate and Worksheet_Deactivate events.

This all works pretty well, but my problem is that some of the key combinations ([Ctrl][Alt][Shift][Letter]) are already mapped to other functions from outside of Excel, and these take precedence over my attempted shortcuts.

Does anyone know of a more elegant or an alternative method of achieving my aims?

Thanks

 

[Yongle]

How about using a dropdown in a cell (say A1)
- select from A-Z
- change in value in A1 triggers Worksheet_Change
- Worksheet_Change amends value of Range("LETTER")

Place in sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0,0) = "[COLOR=#ff0000]A1"[/COLOR] Then Worksheets("Control").Range("LETTER") = Target.Value
End Sub

 

[Eli Panic]

OK, thanks Yongle. I'll give that a try.