Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Macro Hotkeys Programmatically
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Macro Hotkeys Programmatically
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 -- Gary "Sprinks" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Macro Hotkeys Programmatically
Thanks, Gary!
Sprinks "Gary Keramidas" wrote: 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 -- Gary "Sprinks" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Macro Hotkeys Programmatically
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 "Gary Keramidas" wrote: 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 -- Gary "Sprinks" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting VBA reference programmatically | Excel Programming | |||
Setting Data Validation Programmatically | Excel Programming | |||
Setting Solver Reference-programmatically | Excel Programming | |||
Setting HotKey Programmatically | Excel Programming | |||
Do macro shortcuts interfere with excel hotkeys? | Excel Discussion (Misc queries) |