Setting Macro Hotkeys Programmatically

[Sprinks]

I've created a library of macros for our small workgroup. All are available
from a custom menu which loads as Excel loads based on some John Walkenbach
routines. I maintain the file on a local drive, post changes to a shared
network folder, and users use a desktop shortcut to copy the new file to
their auto-load folder.

The principal of our firm, however, prefers a different hotkey assignment
than the rest of us. To avoid having him to customize his configuration each
time, I began maintaining two different versions of the macro file. This
has, predictably, become more of a maintenance headache than I want to
have--life's too short.

Can someone tell me how I can read the username on file load and configure
hotkey assignments based on each user? I figured that I would use a table on
a hidden worksheet to store each user's preferences, as well as a default
configuration.

Thank you for any assistance.
Sprinks

 

[Gary Keramidas]

you can try something like this. paste this in a general module. it runs when
the workbook is opened. and assigns the letter to run the macro called test.
it uses the windows log in name.
change test to your macro name
choose your shortcut keys carefully

Sub auto_open()
Dim skey1 As String
Select Case UCase(Environ("username"))
Case "SPRINKS"
skey1 = "a"
Case "GARYK"
skey1 = "b"
Case Else
skey1 = "c"
End Select
Application.MacroOptions Macro:="test", ShortcutKey:=skey1
End Sub

 

[Sprinks]

Thanks, Gary!

Sprinks

you can try something like this. paste this in a general module. it runs when
the workbook is opened. and assigns the letter to run the macro called test.
it uses the windows log in name.
change test to your macro name
choose your shortcut keys carefully

Sub auto_open()
Dim skey1 As String
Select Case UCase(Environ("username"))
Case "SPRINKS"
skey1 = "a"
Case "GARYK"
skey1 = "b"
Case Else
skey1 = "c"
End Select
Application.MacroOptions Macro:="test", ShortcutKey:=skey1
End Sub

 

[Sprinks]

Gary,

Interestingly, the code you provided proved itself a caution to use care in
assigning hot keys. I cut and pasted the Sub to a module, and went back to
work on my deadline, thinking I'd work on the program later.

All of a sudden, after reopening Excel I couldn't cut or paste anything,
since I actually have a subroutine called Test that I use when trying out a
new macro and
the Else case condition had been satisfied!

Thanks again; it's going to work great.
Sprinks