CODEWITHSUNDEEP
vbaexceleventsuserform
PKB
PKB

Reputation: 390

Event handling class doesn't fire unless I break userform initialization

This is a follow-up to the following question: Can't set Userform.KeyPreview to true

To recap: the goal is to build a form with some command buttons and a frame containing check boxes. The check boxes are dynamically populated at userform_initialize in the frame so the user can scroll through them. My problem was with keyboard shortcuts. It wasn't possible to brute force write KeyDown handlers for each of the checkboxes because I don't know which ones will exist. Unfortunately, Excel doesn't support KeyPreview so I had to mock up my own version. Thank you to @UGP for giving me promising avenues that seem to work, but not quite...

First, this is my class module called clsReasonPickKP. I create a new instance for each checkbox to listen for KeyDown events:

Option Explicit

Dim WithEvents vChkBx As MSForms.CheckBox

Friend Sub initializeListener(cControl As control)
Set vChkBx = cControl
End Sub

Private Sub vChkBx_KeyDown(ByVal keyCode As MSForms.ReturnInteger, ByVal shift As Integer)
frm2.keyChooser keyCode
End Sub

The line frm2.keyChooser keyCode launches a quick sub located in the userform code module. Code below:

Public Sub keyChooser(ByVal keyCode As MSForms.ReturnInteger)
Select Case keyCode
    Case vbKeyEscape: cancelBtn_Click
    Case vbKeyReturn: completeDecision_Click
    Case vbKeyN: customizeNote_Click
    Case vbKeyS: resetDecisionNote_Click
    Case vbKeyR: chkRefGrnds_Click
End Select
End Sub

I've copied the relevant part of the UserForm_Initialize sub below. The loop creates the checkboxes and an event listener for each.

Sub UserForm_Initialize()
Dim x As Long, maxWidth as Long
Dim cControl As control
Dim keyPreviewCollection As New Collection
Dim keyPreviewer As clsReasonPickKP
For x = 1 To dTbl.Rows.Count - 1
    Set cControl = chkBoxFrame.Controls.Add("Forms.CheckBox.1", "chkBox" & x, True)
    With cControl
        .AutoSize = True
        .WordWrap = False
        .Left = 10
        .Top = 16 * x - 12
        .Caption = dTbl(x, 1).Value
        If .Width > maxWidth Then maxWidth = .Width
    End With
Set keyPreviewer = New clsReasonPickKP
keyPreviewer.initializeListener cControl
keyPreviewCollection.Add keyPreviewer
Next x
'Additional initialization code here
End Sub

The odd thing is that unless I break code some time after keyPreviewCollection.Add keyPreviewer, the listener doesn't seem to handle the event. For example, if I set a break point at Next x or for x > 1 and then complete initialization, then when the form is finished initializing and appears the listener calls keyChooser and all is well; if I don't break code like that, it doesn't trap the event or call the sub, etc.

To trouble-shoot, I've tried not adding keyPreviewer to the collection, and then the listener also doesn't work, no matter if or when I break. It seems adding the object to the collection, and being in code break mode after adding it to the collection, somehow makes the listener trap the event.

Also interesting, if I put a breakpoint in the vChkBx_KeyDown module, it breaks when the event is raised (assuming an appropriate break as described above). After I then run the code, however, it stops handling the KeyDown event when its raised.

In case it helps, I'm currently working in Excel 2010.

Does anybody have any idea what's going on? Any idea how to solve this, even with a different code approach?

Thank you as always for everybody's help.

Upvotes: 0

Views: 434

Answers (2)

Travis Bennett
Travis Bennett

Reputation: 143

DoEvents might be the ticket. See the article below:

https://www.automateexcel.com/vba/doevents/

Upvotes: 0

PKB
PKB

Reputation: 390

It turns out that the problem was so simple and right in front of my eyes. I just had to make the keyPreviewer and keyPreviewCollection variables public in my userform code module.

That still doesn't answer why breaking code execution after adding the object to the collection made VBA treat it as public, but just happy that it all works.

Upvotes: 1

Related Questions