Assigning Keyboard Shortcut to Macro

[cowe6453]

Hi,

I currently have a command button to run a macro that hides unwanted rows but want to assign a keyboard short i.e. "Ctrl a". Do I need to edit the following code in order to assign the shortcut or does it involve messing with the properties? I am just starting to learn basic VBE and macros so any help would be much appreciated. Thanks

Private Sub CommandButton1_Click()


Range("C31").Select
Do While ActiveCell.Value <> ""
ActiveCell.Select
If ActiveCell.Value = False Then
ActiveCell.EntireRow.Hidden = True
End If
ActiveCell.Offset(1, 0).Select
Loop


End Sub

 

[Jim885]

You could go to the Developer Tab, click Record Macro. There it will prompt for the keyboard shortcut where you add Cntrl+A
Then click Stop macro
Go to the VB editor.
Your new macro will be in a module
You will see this;

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+A
'
End Sub

Then paste your code;

Range("C31").Select
Do While ActiveCell.Value <> ""
ActiveCell.Select
If ActiveCell.Value = False Then
ActiveCell.EntireRow.Hidden = True
End If
ActiveCell.Offset(1, 0).Select
Loop

directly under this line;

' Keyboard Shortcut: Ctrl+A

Also, remove the " ' " that appears in the line above End Sub

Then close the VB editor

 

[cowe6453]

Thank you.

 

[Audioa84]

Also worth noting, if you are using 2007+, the macros feature under the developer tab (if you have enabled it under Excel Options - Popular) will allow you to do the assign keyboard shortcuts to subs without going into the vb editor. Just click Macros, then select the macro name and click the options button. This does assume you have entered the macro as Jim suggested above.

Good Luck.