VBA KeyDown Event Assigned to ActiveX Control through Class Module Not Working

Question

Aim: To assign a keydown event to 4 TextBoxes on Worksheet (Sheet1) via Class Module

I have an Excel Worksheet (Sheet1) that has 4 TextBoxes on it (default names - TextBox1, TextBox2, TextBox3, TextBox4).

Steps Taken:

(1) Created a class module named ClsEventTxtBx with following codes.

    Public WithEvents CTxtBx As MSForms.TextBox

      Private Sub CTxtBx_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = vbKeyTab Then Debug.Print CTxtBx.Name
      End Sub

(2) Added the following codes in the Workbook Open event.

    Dim TxtBxArr() As New ClsEventTxtBx
 
    Private Sub Workbook_Open()
      Dim i As Integer, shp As Shape

      For Each shp In Sheet1.Shapes
        If shp.Type = msoOLEControlObject Then
            If TypeName(shp.OLEFormat.Object.Object) = "TextBox" Then
                i = i + 1
                ReDim TxtBxArr(1 To i)
                Set TxtBxArr(i).CTxtBx = shp.OLEFormat.Object.Object
            End If
        End If
      Next shp
    
    End Sub

Could you please find what is wrong with the code?

You need to use Redim Preserve if you want to maintain the existing array content. — Tim Williams

Dy.Lee Dy.Lee · Accepted Answer · 2021-02-02T01:19:05

Try,

Dim TxtBxArr() As New ClsEventTxtBx
Private Sub Workbook_Open()
        Dim i As Integer, shp As OLEObject

    For Each shp In Sheet1.OLEObjects
          If shp.progID = "Forms.TextBox.1" Then
              i = i + 1
              ReDim Preserve TxtBxArr(1 To i)
              shp.Name = "myshp" & i
              Set TxtBxArr(i).CTxtBx = shp.Object
          End If
    Next shp
End Sub

VBA KeyDown Event Assigned to ActiveX Control through Class Module Not Working

1 Answers