Assigning Keystrokes to Excel Macro

[YossiD]

Is there any way to assign Excel macros to keyboard shortcuts other than CTRL and CTRL-SHIFT?

I found some nice Excel macros (https://superuser.com/questions/249743/how-to-easily-reorder-rows-in-excel-with-drag-and-drop-or-move-up-or-move-down) for moving rows up and down and I want to assign them to the same shortcuts as in Word for the same functionality (ALT-SHIFT-up arrow/down arrow). Can it be done?

[Aflatoon]

Yes, you can use Application.OnKey to assign more key combinations to your macros. You'd use either an AutoOpen or Workbook_Open event to ensure that they are available whenever the workbook with the code is opened.

[YossiD]

Yes, I came across that possibility at this website (https://msdn.microsoft.com/en-us/library/office/ff197461.aspx) after posting my query, but have not had time to try it yet. Will give it a shot when time allows.

Just wondering, where do you get the time and patience to follow this forum so closely and reply to so many postings? I, for one, and very grateful that you do.

Thanks

[Aflatoon]

I average only 0.5 posts per day here, which is really not that onerous for me. :) There are plenty of others who post far more here than I do. I do appreciate the thanks though - which is why I do it.

[YossiD]

Worked great.

Here's the whole thing if anyone's interested.


Sub auto_open()
' https://msdn.microsoft.com/en-us/library/office/ff197461.aspx
' https://stackoverflow.com/questions/9818047/excel-vba-assign-keyboard-shortcut-to-run-procedure

Application.OnKey "%+{UP}", "move_rows_up"
Application.OnKey "%+{DOWN}", "move_rows_down"
End Sub

Sub move_rows_down()
'https://superuser.com/questions/249743/how-to-easily-reorder-rows-in-excel-with-drag-and-drop-or-move-up-or-move-down

Dim rOriginalSelection As Range
Set rOriginalSelection = Selection.EntireRow
With rOriginalSelection
.Select
.Cut
.Offset(rOriginalSelection.rows.Count + 1, 0).Select
End With
Selection.Insert
rOriginalSelection.Select
End Sub

Sub move_rows_up()
Dim rOriginalSelection As Range
Set rOriginalSelection = Selection.EntireRow
With rOriginalSelection
.Select
.Cut
.Offset(-1, 0).Select
End With
Selection.Insert
rOriginalSelection.Select
End Sub

The comment lines show where I found the information.

Does the forum have a library of macros where I could put this for the convenient use of others?

[krishnak]

This is a very useful post...this works like a charm.