Short key/ hot key for CommandButton

[noexcelguru]

One of my commandbuttons will every now and then be covered by the FormulaBar, is it possible to assign a short key/ hot key for its function (CTRL+q)?

CommandButton's code:

Private Sub CommandButton3_Click()
Select Case ActiveCell.Interior.ColorIndex
Case xlNone: ActiveCell.Interior.ColorIndex = 3
Case 3: ActiveCell.Interior.ColorIndex = 6
Case 6: ActiveCell.Interior.ColorIndex = 10
Case 10: ActiveCell.Interior.ColorIndex = xlNone
End Select
End Sub

 

[noexcelguru]

As i'm bumping this one back up, i'll also clarify my problem; One of my commandbuttons will every now and then be hidden under the FormulaBar, is it possible to assign a short key/ hot key (CTRL+q) wich will run the CommandButton's code/ function?

 

[RoryA]

If it's an ActiveX button, then you can set a letter as its accelerator and use Alt+letter to run the button code. (normally you would choose a letter in the button caption, which will then be underlined). If it's a forms button, then just assign a shortcut key to its macro in the macros dialog.

 

[noexcelguru]

Thanks for your reply.
Doesn't seem to work, probably because of macro? When i click the button the cell remains blanc (xlnone), however if i click the button several times fast, i see the colors flashing, but not 'sticking' to the cell.

Macro:
Sub Makro1()
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Interior.ColorIndex = 6
Selection.Interior.ColorIndex = 10
Selection.Interior.ColorIndex = xlNone
End Sub

As i wrote above, i believe the macro/ code is wrong as it looks to just 'run through' the colors rather than selecting one and end. Any suggestions? Isn't it possible to assign a macro or code to a VB CommandButton rather than an ActiveX?

 

[RoryA]

Why have you changed the code? The original code was fine.

 

[noexcelguru]

It did work with the original code Thanks, Roya
I thought i had to use different codes for macros (i'm not that familiar with excel as you probably have understood)

So it isn't possible to assign hot keys to VB CommandButtons?

 

[RoryA]

It is, using the Accelerator as I mentioned, but you have to use Alt+letter rather than Ctrl+ or Ctrl+Shift+ as you can with macros.

 

[noexcelguru]

I found it, and of course it works.
Thanks, rorya, thread solved