PDA

View Full Version : Menu Keyboard Shortcuts



ukdane
11-12-2008, 12:36 PM
I have my own menus in Excel 2000, as can be seen in the example below.

Set myCommandBarCtl = myBar.Controls.Add(Type:=msoControlButton)
With myCommandBarCtl
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Oprette Link"
.TooltipText = "Link til arkiv folder"
.OnAction = "addlink"
End With

However a user has asked if it is possible to add a keyboard shortcut to run the code, instead of having to use the mouse to navigate.
Is there a quick addition I can make to the code above to solve the problem, or will I have to create a new Macro for each shortcut?

GTO
11-12-2008, 01:16 PM
Greetings Ukdane,

You can add a shortcut key combination to run a macro, but it wouldn't be added to the command bar building proedure. Rather, try adding akin to below, to the workbook activate and deactivate events. This way, its only in effect while the user has your workbook in use.

See the help topic OnKey for different key assigning; the below assigns CTRL + "d" to your macro; and the OnKey w/no arg resets the combo to its default action.

Hope this helps,

Mark

Private Sub Workbook_Activate()
Application.OnKey "^d", "addlink"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "^d"
End Sub

ukdane
11-12-2008, 01:39 PM
Cool. Thanks.
Any idea how I can utilise the F1-F12 buttons, and F13-F25 (if we use SHIFT)?

Cheers

GTO
11-12-2008, 04:16 PM
Cool. Thanks.
Any idea how I can utilise the F1-F12 buttons, and F13-F25 (if we use SHIFT)?

Cheers

Happy to be of assistance. For help on string construction, in Excel 2000, select the 'Index' tab in VBA Help and type the word "onkey" in the search box. The help topic will assist you in string construction, such as where to add french braces, etc...

I found this particular help topic to be fairly clear, but if after reading the help file your code "jams", just include the code and we can take a 'looksee'.

Mark