Can't set Userform.KeyPreview to true

Question

I've built a form in Excel. It consists of 3 command buttons and a frame containing checkboxes. The checkboxes are dynamically populated at userform_initialize based on tables in an excel sheet (the idea being easy user customization). The reason for the frame is that there can be a lot of checkboxes and I want the user to be able to scroll through them.

My goal now is to create keyboard shortcuts for the form. Where I get stuck is that I can't brute force write KeyDown handlers for each of the checkboxes because I don't know which ones will exist. I realize that it would also just be better if I could have the event handler at the form level. Googling has found me the form's KeyPreview property. Unfortunately, the properties window in VBA IDE doesn't show it and when I try to access it programmatically by setting Me.KeyPreview = True at userform_initialize VBA throws a compile error: "Method or data member not found" - what I would expect given it isn't in the properties window, but was worth a try.

I feel like there's something I'm obviously missing so I thought I'd ask before spending time learning how to write and then rewriting the form entirely as a class as in the MSDN example code: https://msdn.microsoft.com/en-us/library/system.windows.forms.form.keypreview(v=vs.110).aspx. Am I that lucky?

I confess to being at the limit of my VBA knowledge and I'm looking to go expand on it. Any general concepts or context I should red would be greatly appreciated.

UPDATE

I'm now thinking about GetAsyncKeyState and Application.Onkey.

From what I understand, GetAsyncKeyState only works within an infinite DoEvents loop. I tried initiating one hoping the form would still load but of course it didn’t – I’m stuck in the loop.

The problem with Application.Onkey is that I can't assign the event function to the key within the userform module. This puzzles me because other event handlers can go in the userform module. In fact, I’d put it in the Userform_Initialize procedure. Is it because it's not a form event but an application event?

EDIT

I seem to have something that works, but for the strange issue described here: Event handling class will not fire unless I use a breakpoint when initializing form Thank you @UGP

Answer 1

Here is an example how it could work, found here:

To put in a class named "KeyPreview":

Option Explicit

Dim WithEvents u As MSForms.UserForm
Dim WithEvents t As MSForms.TextBox
Dim WithEvents ob As MSForms.OptionButton
Dim WithEvents lb As MSForms.ListBox
Dim WithEvents dp As MSComCtl2.DTPicker

Event KeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer)
'Event KeyPress(ByVal KeyAscii As Integer)

Private FireOnThisKeyCode As Integer

Friend Sub AddToPreview(Parent As UserForm, KeyCode As Integer)
Dim c As Control
Set u = Parent
FireOnThisKeyCode = KeyCode
For Each c In Parent.Controls
Select Case TypeName(c)
Case "TextBox"
Set t = c
Case "OptionButton"
Set ob = c
Case "ListBox"
Set lb = c
Case "DTPicker"
Set dp = c
End Select
Next c
End Sub

Private Sub u_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = FireOnThisKeyCode Then RaiseEvent KeyDown(KeyCode, Shift)
End Sub

Private Sub t_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = FireOnThisKeyCode Then RaiseEvent KeyDown(KeyCode, Shift)
End Sub

Private Sub ob_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = FireOnThisKeyCode Then RaiseEvent KeyDown(KeyCode, Shift)
End Sub

Private Sub lb_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = FireOnThisKeyCode Then RaiseEvent KeyDown(KeyCode, Shift)
End Sub

Private Sub dp_KeyDown(KeyCode As Integer, ByVal Shift As Integer)
If KeyCode = FireOnThisKeyCode Then RaiseEvent KeyDown(KeyCode, Shift)
End Sub

To put in the userform:

Option Explicit 

Dim WithEvents kp As KeyPreview 

Private Sub UserForm_Initialize() 
Set kp = New KeyPreview 
kp.AddToPreview Me, 114 
End Sub 

Private Sub kp_KeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer) 
MsgBox "F3 was pressed..." 
End Sub 

It works with TextBoxes, OptionButtons, ListBoxes and DTPickers. Other Controls that could get focus will need to be handled aswell.