Assign Excel Menu to Shortcut Key

[Joshua]

How do I assign an Excel menu item to a shortcut using VBA?

For example, I would like to add a procedure that inserts a worksheet
when the user presses Ctrl + Shift + I, instead of having to go to
through the native excel commands of Alt, then I, then W, or using the
mouse to navigate to Insert, then Worksheet

Thanks,

Joshua.

 

[Ron de Bruin]

Hi Joshua

Use Shift F11 to insert a new worksheet

 

[Joshua]

Hi Joshua

Use Shift F11 to insert a new worksheet

I would like to do this for a variety of native excel menu commands.
How do you call a native excel menu command in VBA using a keyboard
shortcut?

Thanks,

J

 

[Ron de Bruin]

Hi Joshua

You must build a macro that will do the same and asign a shortcut to that macro
Or call the menu item in a macro like this

Sub insertsheet()
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=852, Recursive:=True).Execute
End Sub


You can find the ID numbers on this page (see also OLE's add-in
http://www.rondebruin.nl/menuid.htm





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Joshua

Use Shift F11 to insert a new worksheet

I would like to do this for a variety of native excel menu commands.
How do you call a native excel menu command in VBA using a keyboard
shortcut?

Thanks,

J

 

[Gord Dibben]

Record a macro whilst performing a task with a native menu command.

Assign a shortcut key to that macro.

From your example, recording inserting a worksheet returns this code.

Sub Macro1()
Sheets.Add
End Sub

But I would first see Help on keyboard shortcuts...........many native
commands already have shortcut keys.

Like Shift + F11 to insert a new sheet.


Gord Dibben MS Excel MVP