For most of Check-Boxes
/ Buttons
I assigned a Key
. Can be execute by pressing:
Alt + Assigned-key
I had googled the following code.
Private Sub UserForm_Initialize()
Me.PASTE.Accelerator = "V"
Me.CEEMEA.Accelerator = "C"
End Sub
Problem is I have to Press Alt key to perform any given task.
Q. Is there any short way of doing this without pressing AltKey?
My progress After Robin's Original-Answer
Firstly I set focus on Macros Button.
Private Sub UserForm_Initialize()
Me.Macros.SetFocus
End Sub
Then on Macro_Keydown
Event
I put the following code.
Private Sub Macros_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyB Then
Bulgaria.Value = Not Bulgaria.Value
ElseIf KeyCode = vbKeyE Then
Estonia.Value = Not Estonia.Value
ElseIf KeyCode = vbKeyH Then
Hungary.Value = Not Hungary.Value
ElseIf KeyCode = vbKeyA Then
Latvia.Value = Not Latvia.Value
ElseIf KeyCode = vbKeyL Then
Lithuania.Value = Not Lithuania.Value
ElseIf KeyCode = vbKeyM Then
Macedonia.Value = Not Macedonia.Value
ElseIf KeyCode = vbKeyP Then
Poland.Value = Not Poland.Value
ElseIf KeyCode = vbKeyR Then
Romania.Value = Not Romania.Value
ElseIf KeyCode = vbKeyU Then
Ukraine.Value = Not Ukraine.Value
End If
End Sub
The original answer didn't really meet the brief because whilst handling the UserForm
events for e.g. KeyDown
works for a form with no other controls, it doesn't work for a form with controls. This is because the event only works when the form has the focus. When the form has other controls, it never receives the focus. Also, it is not possible to set the focus onto the UserForm
. Almost all forms will have some other controls, so the original answer is practically useless. So let's shamelessly adapt an idea from Andy Pope on MSDN to meet the OP's requirements.
First, insert a VBA Class
into the project with this code:
Public WithEvents m_objGroupCheckBox As MSForms.CheckBox
Private Sub m_objGroupCheckBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
MsgBox "Keypress was: " & Chr(KeyCode) & " on " & m_objGroupCheckBox.Caption
Select Case Chr(KeyCode)
Case 1:
UserForm1.CheckBox1.Value = Not UserForm1.CheckBox1.Value
Case 2:
UserForm1.CheckBox2.Value = Not UserForm1.CheckBox2.Value
Case "3"
UserForm1.CheckBox3.Value = Not UserForm1.CheckBox3.Value
End Select
End Sub
The Class
defines a generic event handler for a CheckBox
on the UserForm
. For the purposes of this example, we will make key presses of 1, 2 and 3 toggle the checkbox state for the 3 CheckBox
s on the form.
Second, put the code in the Userform
's initialize event. It creates a collection of this custom class that references back to the original checkboxes created on the UserForm
.
Private m_colCheckBoxes As Collection
Private Sub UserForm_Initialize()
Dim lngIndex As Long
Dim objGroupCheckBox As clsGroupCheckBox
Set m_colCheckBoxes = New Collection
For lngIndex = 1 To 3
Set objGroupCheckBox = New clsGroupCheckBox
Set objGroupCheckBox.m_objGroupCheckBox = Me.Controls("CheckBox" & lngIndex)
m_colCheckBoxes.Add objGroupCheckBox, CStr(m_colCheckBoxes.Count + 1)
Next
End Sub
So now, if we have a UserForm
in the designer like this, with each CheckBox named CheckBox1
, CheckBox2
and CheckBox3
:
Then, our generic event handler will allow us to define a single place to handle the KeyDown
event and set CheckBox
status in one spot.
You can directly handle the KeyDown
event of the UserForm
and enter your specific logic in there. Maybe you should check out KeyUp
and KeyPress
as well depending on how you think the form will work.
MSDN notes that '..."A" and "a" are returned as the same key. They have the identical keycode value. But note that "1" on the typewriter keys and "1" on the numeric keypad are returned as different keys, even though they generate the same character.' - MSDN Link
You can handle SHIFT, CTRL and ALT as well.
Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode >= vbKeyA And KeyCode <= vbKeyZ Then
MsgBox "You pressed " & Chr(KeyCode)
ElseIf KeyCode >= vbKeyF1 And KeyCode <= vbKeyF12 Then
MsgBox "Function time!"
End If
End Sub