CODEWITHSUNDEEP
vbams-worduserformword-2007
VBAbyMBA
VBAbyMBA

Reputation: 826

Handling / Performing / Managing tasks in Userform with the help of keyboard ( shortcut keys )

I have a User-form userform

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

Upvotes: 1

Views: 1047

Answers (2)

Froid Andrewson
Froid Andrewson

Reputation: 1

'VBA Shortcut Keys not work in UserForm [Partially Solved]

Public Sub CallSub() 'code must be in Module
'-do this code-


Private Sub Workbook_Activate() 'code must be in (ThisWorkbook)
Application.OnKey "^{f5}", "callSub"

'^ this code only work with Excel Worksheet not in Userform


Private Sub XxX_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'code must be in Userform with SHOWMODAL = False
If KeyCode = 17 Then AppActivate "microsoft excel"  

'XxX means all CommandButton and Textbox and Listbox and Combobox
'Keycode 17 is Ctrl Key if you are using Ctrl+F5 - when you press Ctrl it will activate Excel Worksheet

Upvotes: 0

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

Updated answer

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 CheckBoxs 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:

enter image description here

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.

enter image description here

Original answer - not as useful as it looks :(

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

Upvotes: 4

Related Questions