动态创建的用户窗体，带有2个相关的组合框 [英] Dynamic created User-Form, with 2 dependent Combo-Boxes

问题描述

我正在尝试创建一个动态的User_form，其中所有控件都是在运行时创建的。

I am trying to create a dynamic User_form, where all the Controls are created at Run-Time.

我有2个组合框数组，第一个组合框数组是 Catgeory（ CatCBArr ），第二个组合框数组是 Item（ ItemCBArr ）。

I have 2 array of Combo-Boxes, first array of Combo-Boxes is "Catgeory" (CatCBArr) , and the second array of Combo-Boxes is "Item" (ItemCBArr).

我想要的是，一旦我从类别的第一个组合框中选择一个值，比如说 CatCBArr（0），将只显示 ItemCBArr（0）中的相关项目。

I want, that once I select a value from the first Combo-Box of "Category", let's say CatCBArr(0), that only the related Items in ItemCBArr(0) will be displayed.

问题：我不知道如何修改第二个组合框（ ItemCBArr（0））根据在第一个组合框中选择的值（ CatCBArr（0））

Issue: I can't figure out how to modify the second Combo-box (ItemCBArr(0)) according to the value selected in the first Combo-box (CatCBArr(0))

User_Form 代码（相关部分）

User_Form Code (relevant section)

Option Explicit

Dim ItemsNumofRows As Long    
Dim QtyTB As MSForms.TextBox
Dim CatCB As MSForms.ComboBox
Dim ItemCB As MSForms.ComboBox

Dim Key As Variant

' dynamic Form controls (related to new Classes)
Dim CatCBArr()                     As New cComboBox
Dim ItemCBArr()                    As New cComboBox    
Dim QtyTBArr()                     As New cTextBox         

Private Sub UserForm_Initialize()

' reset flags
ItemsNumofRows = 5
TasksNamesUpd = False
TasksColUpd = False

ItemsRows_ControlsInit '<-- upload all Controls at run-time
Check_FormHeight

End Sub

'======================================================

Private Sub ItemsRows_ControlsInit()

For ItemRow = 0 To ItemsNumofRows

    ' add Category Combo-boxes
    Set CatCB = Me.Controls.Add("Forms.ComboBox.1", "Cb" & ItemRow, True)
    With CatCB
        ' loop through Dictionay items (view category)
        For Each Key In Dict.Keys
            .AddItem Key
        Next Key

        .SpecialEffect = fmSpecialEffectSunken
        .Left = 40
        .Width = 100
        .Height = 18
        .Top = 54 + 20 * ItemRow

        ReDim Preserve CatCBArr(0 To ItemRow)
        Set CatCBArr(ItemRow).ComboBoxEvents = CatCB
    End With

    ' add Item Combo-boxes
    Set ItemCB = Me.Controls.Add("Forms.ComboBox.1", "Cb_" & ItemRow, True)
    With ItemCB

        .SpecialEffect = fmSpecialEffectSunken
        .Left = 160
        .Width = 100
        .Height = 18
        .Top = 54 + 20 * ItemRow

        ReDim Preserve ItemCBArr(0 To ItemRow)
        Set ItemCBArr(ItemRow).ComboBoxEvents = ItemCB
    End With    
Next ItemRow

End Sub

cComboBox 类代码

cComboBox Class Code

Public WithEvents ComboBoxEvents As MSForms.ComboBox

Private Sub ComboBoxEvents_Change()

Dim CBIndex                            As Long

' get for ID number (row number), from third character in String Name.
' e.g "Cb1" will result 1)
CBIndex = CInt(Mid(ComboBoxEvents.Name, 3))

' ??? How do I get the Value, and update the second combo-box Items

Select Case ComboBoxEvents.Value


End Select

End Sub

GUI User_Form 屏幕截图

GUI User_Form screen-shot

推荐答案

好的，这是基础。
您的课程 cCombobox 我复制了以下内容：

Alright, here's the basics. Your class cCombobox I replicated as follows:

Private WithEvents ComboBoxEvents As MsForms.ComboBox
Private Sub ComboBoxEvents_Change()
    Select Case ComboBoxEvents.value
        Case "1":
            UserForm1.DependentBox.Clear
            UserForm1.DependentBox.AddItem "3"
            UserForm1.DependentBox.AddItem "4"
        Case "2":
            UserForm1.DependentBox.Clear
            UserForm1.DependentBox.AddItem "5"
            UserForm1.DependentBox.AddItem "6"
        Case Default:
            'Do Nothing
    End Select
End Sub
Public Property Let box(value As MsForms.ComboBox)
    Set ComboBoxEvents = value
End Property
Public Property Get box() As MsForms.ComboBox
    Set box = ComboBoxEvents
End Property

接下来，我创建了一个 UserForm1 ，其中添加了2个组合框，其中一个是我的广告d到类型为cComboBox的局部变量。

Next, I created a UserForm1 that adds 2 comboboxes, one of which I add to a local variable of type cComboBox.

Public DependentBox As MsForms.ComboBox
Private InitialBox As cComboBox
Private Sub UserForm_Initialize()
    Dim cBox As MsForms.ComboBox
    Set InitialBox = New cComboBox

    Set cBox = Me.Controls.Add("Forms.ComboBox.1", "initial", True)
    With cBox
        .Left = 6
        .Width = 100
        .Height = 25
        .Top = 6
        .AddItem "1"
        .AddItem "2"
    End With
    InitialBox.box = cBox

    Set DependentBox = Me.Controls.Add("Forms.ComboBox.1", "dependent", True)
    With DependentBox
        .Top = 6
        .Left = 126
        .Height = 25
        .Width = 100
    End With
End Sub

即使这样做可行，上述方法也不是很干净，由于您的课程不是自包含的，因此必须注意UserForm。更好的方法是先链接类中的框，然后在初始化控件数组时从Userform中传递它们。
然后是：

Even though this works, the above approach is not very clean, since your class is not self-contained - It has to be aware of the UserForm. A better way would be to link the boxes in the class already and then just pass them from the Userform when you initialize your arrays of controls. Then it would be:

cComboBox 类：

Private WithEvents p_ComboBoxEvents As MSForms.ComboBox
Private p_DependBox As MSForms.ComboBox
Private Sub p_ComboBoxEvents_Change()
    Select Case p_ComboBoxEvents.value
        Case "1":
            p_DependBox.Clear
            p_DependBox.AddItem "3"
            p_DependBox.AddItem "4"
        Case "2":
            p_DependBox.Clear
            p_DependBox.AddItem "5"
            p_DependBox.AddItem "6"
        Case Default:
            'Do Nothing
    End Select
End Sub
Public Property Let TriggerBox(value As MSForms.ComboBox)
    Set p_ComboBoxEvents = value
End Property
Public Property Get TriggerBox() As MSForms.ComboBox
    Set TriggerBox = p_ComboBoxEvents
End Property
Public Property Let DependBox(value As MSForms.ComboBox)
    Set p_DependBox = value
End Property
Public Property Get DependBox() As MSForms.ComboBox
    Set DependBox = p_DependBox
End Property

在这里您已经将框链接到一个独立的类中。
在事件处理程序中，您可以创建值的查找，等等。
然后在 UserForm1 代码中按如下所示初始化它们：

Here you see you already link the boxes in a self-contained class. In the event handler you could create a lookup for the values, etc. Then in the UserForm1 code you initialize them as follows:

Option Explicit
Private LinkedComboBox As cComboBox
Private Sub UserForm_Initialize()
    Dim cBox As MSForms.ComboBox
    Set LinkedComboBox = New cComboBox

    Set cBox = Me.Controls.Add("Forms.ComboBox.1", "initial", True)
    With cBox
        .Left = 6
        .Width = 100
        .Height = 25
        .Top = 6
        .AddItem "1"
        .AddItem "2"
    End With
    LinkedComboBox.TriggerBox = cBox

    Set cBox = Me.Controls.Add("Forms.ComboBox.1", "dependent", True)
    With cBox
        .Top = 6
        .Left = 126
        .Height = 25
        .Width = 100
    End With
    LinkedComboBox.DependBox = cBox
End Sub

编辑：
B根据它必须是数组的事实，可以如下修改用户表单：

Based on the fact that it needs to be an array, you can amend the userform as follows:

Option Explicit
Private LinkedComboBox(0 To 4) As cComboBOx
Private Sub UserForm_Initialize()
    Dim cBox As MSForms.ComboBox
    Dim i As Integer

    For i = 0 To 4
        Set LinkedComboBox(i) = New cComboBOx
        Set cBox = Me.Controls.Add("Forms.ComboBox.1", "initial", True)
        With cBox
            .Left = 6
            .Width = 100
            .Height = 25
            .Top = 6 + (i * 25)
            .AddItem "1"
            .AddItem "2"
        End With
        LinkedComboBox(i).TriggerBox = cBox

        Set cBox = Me.Controls.Add("Forms.ComboBox.1", "dependent", True)
        With cBox
            .Top = 6 + (i * 25)
            .Left = 126
            .Height = 25
            .Width = 100
        End With
        LinkedComboBox(i).DependBox = cBox
    Next i

End Sub

在数组中，您可以通过 LinkedComboBox（i）.DependBox 和 LinkedComboBox（i）.TriggerBox 访问每个框。您将不再需要两个单独的数组，因为此 LinkedComboBox 数组

In the array you can access each box as LinkedComboBox(i).DependBox and LinkedComboBox(i).TriggerBox. You won't need the two seperate arrays anymore, since everything is already contained in this LinkedComboBox array

这篇关于动态创建的用户窗体，带有2个相关的组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！