Option Button Accelerator Key

[Bob Wall]

I have a UserForm with several option buttons, each with its own
accelerator.
Is there a way to set the accelerator to work without the ALT key? I'd like
the users to be able to hit just one key to choose the particular option.

TIA

 

[Rob Bovey]

I have a UserForm with several option buttons, each with its own
accelerator.
Is there a way to set the accelerator to work without the ALT key? I'd like
the users to be able to hit just one key to choose the particular option.

Hi Bob,

There's no way to do this directly, but if you don't have any controls
on the UserForm that require data entry, meaning you can safely hijack
keystrokes, you can roll your own. Create a UserForm with three
OptionButtons. Leave them with their default names and captions so you have
OptionButton1, OptionButton2 and OptionButton3. To allow the user to select
them by just pressing 1, 2 or 3, respectively, you would add the following
code to the UserForm:

Private Sub OptionButton1_KeyPress( _
ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 49 Then ''' 1
OptionButton1.SetFocus
OptionButton1.Value = True
KeyAscii = 0
ElseIf KeyAscii = 50 Then ''' 2
OptionButton2.SetFocus
OptionButton2.Value = True
KeyAscii = 0
ElseIf KeyAscii = 51 Then ''' 3
OptionButton3.SetFocus
OptionButton3.Value = True
KeyAscii = 0
End If
End Sub

Private Sub OptionButton2_KeyPress( _
ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 49 Then ''' 1
OptionButton1.SetFocus
OptionButton1.Value = True
KeyAscii = 0
ElseIf KeyAscii = 50 Then ''' 2
OptionButton2.SetFocus
OptionButton2.Value = True
KeyAscii = 0
ElseIf KeyAscii = 51 Then ''' 3
OptionButton3.SetFocus
OptionButton3.Value = True
KeyAscii = 0
End If
End Sub

Private Sub OptionButton3_KeyPress( _
ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 49 Then ''' 1
OptionButton1.SetFocus
OptionButton1.Value = True
KeyAscii = 0
ElseIf KeyAscii = 50 Then ''' 2
OptionButton2.SetFocus
OptionButton2.Value = True
KeyAscii = 0
ElseIf KeyAscii = 51 Then ''' 3
OptionButton3.SetFocus
OptionButton3.Value = True
KeyAscii = 0
End If
End Sub

If you have additional controls on the UserForm you need to add a similar
KeyPress event for each of them, with additional ElseIf branches for each
accelerator key you want to simulate.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Brian Murphy]

About a year and a half ago I posted a similar question. With help from
this newsgroup, I ended up creating a Keyup event macro for each button.
The Keyup event gets the code of the key that was pressed. For example...

Private Sub Cancel_Button_Keyup(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Call WhichKey(CInt(KeyCode))
End Sub

Each of the Keyup macros looks like this. The WhickKey macro then looks
like this...

Sub WhichKey(KeyCode As Integer)
Select Case KeyCode
Case 65, 97 'A,a
Call AppendOverwriteDialog_Append_Button_Click
Case 79, 111 'O,o
Call AppendOverwriteDialog_Overwrite_Button_Click
Case 67, 113 'C,c
Call Cancel_Button_Click
Case 72, 104 'H,h
Call Help_Button_Click
Case Else
End Select
End Sub



Brian Murphy
Austin, Texas