vba - 使用 VBA 在 Excel 用户窗体中选择由选项指示的多个下拉框

Question

这个问题是我正在进行的项目的“第 3 部分”。之后adding multiple labels and textboxes to an Excel userform during runtime using vba和 retrieving data from multiple textboxes created during runtime in an Excel userform using vba ，我现在正在尝试使用所有这些数据来选择下拉框中的名称以分配工作。



我遇到的问题是，我将代码设置为循环 MyArray(i)来自 LBound至UBound这给了我们员工的名字，因为它这样做，它还循环通过拆分 MultFLNAmt 创建的数组检索自 UserForm因此我们可以确定每个员工将收到多少 FLN，然后它还会循环查找选择分配给的当前员工的姓名。一旦所有这些都完成并且每个人都分配了正确数量的 FLN，它将单击应用程序中的提交按钮以完成分配。

' Shows and hides the multiple option UserForm
MultipleOptionForm.Show

MultipleOptionForm.Hide

' Creates an array from a comma-delimited
' list of numbers stored in a variable
MFA = Split(MultFLNAmt, ",")

' Activates the application we will be assigning work from
WShell.AppActivate "Non-Keyable Document Management System"

' Table cell node where the dropdown is located
tdNode = 64
a = 1

' Loop through each of the names within the array
For c = LBound(MyArray) + 1 To UBound(MyArray) - 1
    ' Loop through the array to see how many FLNs each person receives
    For b = 1 To MFA(a)
        ' Loop through to locate the current name of the employee
        i = 0
        For Each objOption In objIE.Document.GetElementsByTagName("table")(0).GetElementsByTagName("td")(tdNode).GetElementsByClassName("txt_input1")(0).Options
            Q(i) = objOption.Text & "-" & objOption.Value

            strWQ = Q(i)
            ' Remove "Selected User" from the list of options
            If i = 0 Then
                If strWQ = "--Select User---" Then strWQ = ""
            Else
                ' If an option matches the current name selected,
                ' select that option, then increase the node location
                ' for the next dropdown box
                If InStr(strWQ, MyArray(c)) Then
                    objOption.Selected = True
                    objIE.Document.GetElementsByTagName("table")(0).GetElementsByTagName("td")(tdNode).GetElementsByClassName("txt_input1")(0).OnChange
                    tdNode = tdNode + 23
                Else
                    objOption.Selected = False
                End If
            End If
        Next            
        i = i + 1            
    Next
Next

objIE.Document.all.Item("btn_submit1").Click

虽然代码大部分都在工作，但它失败的地方是，如果 MFA(a)为 2 或更多，则仅选择第一个下拉列表。我将代码置于 Debug模式，但看不到为什么没有选择 2 个或更多。有任何想法吗？

Answer 1

经过大量研究，我终于弄清楚了如何让我的项目工作。

' This line allows for growth/shrinkage of the list of employees
MultipleOptionForm.Height = (UBound(MyArray) - 1) * 20

' This line shows the form
MultipleOptionForm.Show

' This line hides the form after being updated
MultipleOptionForm.Hide

' Creates an array from a comma-delimited
' list of numbers stored in a variable
MFA = Split(MultFLNAmt, ",")

' Activates the application we will be assigning work from
WShell.AppActivate "Non-Keyable Document Management System"

' Table cell node where the dropdown is located
tdNode = 64
' MFA index
a = 1

' Loop through each of the names within the array
For c = LBound(MyArray) + 1 To UBound(MyArray) - 1
    ' Loop through the array to see how many FLNs each person receives
    For b = 1 To MFA(a)
        ' Starts loop at first drop down
        On Error Resume Next
            For Each objOption In objIE.Document.GetElementsByTagName("table")(0).GetElementsByTagName("td")(tdNode).GetElementsByClassName("txt_input1")(0).Options
                ' Stores options within drop down
                strWQ = objOption.Text & "-" & objOption.Value
                If IsEmpty(strWQ) Then
                    Exit Sub
                End If
                ' Remove "Selected User" from the list of options
                If strWQ = "--Select User---" Then
                    strWQ = ""
                Else
                    ' If there's a match between the drop down for the list
                    ' and the list of assigned FLNs, begin assigning
                    If InStr(strWQ, MyArray(c)) Then
                        objOption.Selected = True
                        objIE.Document.GetElementsByTagName("table")(0).GetElementsByTagName("td")(tdNode).GetElementsByClassName("txt_input1")(0).OnChange
                        tdNode = tdNode + 23
                        Exit For
                    Else
                        objOption.Selected = False
                    End If
                End If
            Next
        On Error GoTo 0
    Next
Next