excel - 通过按 TAB 键在工作表上的 ActiveX 控件之间导航的 VBA 类模块

Question

尝试完成：在 Excel 工作表 (Sheet1) 上按顺序排列了一些文本框和组合框（ActiveX 控件），例如用户窗体。我想通过 Tab 键（按 TAB 键）在这些控件之间导航。

部分成功：我能够使用下面显示的方法和代码在文本框之间导航。但是，当还涉及 ComboBoxes 时，我不知道该怎么做。

请注意：所有这些控件都是分组的，并且必须保持这样。

我如何能够在文本框之间导航：

按名称 ClsEventTxtBx 插入一个类模块并添加以下代码

    Public WithEvents CTxtBx As MSForms.TextBox

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

插入标准模块并添加子程序 JumpingToNextTextBox

    Sub JumpingToNextTextBox(ActiveCtl As MSForms.TextBox)

    Dim shp As Shape, oleshp As Shape, i As Integer, ctlArr()

    For Each shp In Sheet1.Shapes
       If shp.Type = msoGroup Then
           For Each oleshp In shp.GroupItems
               If TypeName(oleshp.OLEFormat.Object.Object) = "TextBox" Then
                  i = i + 1
                  ReDim Preserve ctlArr(1 To i)
                  ctlArr(i) = oleshp.OLEFormat.Object.Name
               End If
           Next oleshp
      End If
    Next shp

    i = 0

    For i = LBound(ctlArr) To UBound(ctlArr)
        If ActiveCtl.Name = ctlArr(i) Then
            If Not i = UBound(ctlArr) Then
                Sheet1.OLEObjects(ctlArr(i + 1)).Activate
            Else
                Sheet1.OLEObjects(ctlArr(1)).Activate
            End If
        End If
    Next I

   End Sub

在 ThisWorkBook 中添加了以下代码

    Dim ctlArr() As New ClsEventTxtBx

    Private Sub Workbook_Open()
       Dim i As Integer, shp As Shape, oleshp As Shape, oleArr(), oleObject As oleObject
       Dim oleColl As New Collection

    
       For Each shp In Sheet1.Shapes

           If shp.Type = msoGroup Then
               For Each oleshp In shp.GroupItems
                   If oleshp.Type = msoOLEControlObject Then
                       If TypeName(oleshp.OLEFormat.Object.Object) = "TextBox" Then
                           i = i + 1
                           ReDim Preserve ctlArr(1 To i)
                           Set ctlArr(i).CTxtBx = oleshp.OLEFormat.Object.Object
                       End If
                   End If
               Next oleshp
           End If

       Next shp
    End Sub

标签： excelvbaactivexkeyevent