时间戳

首页 > 解决方案 > Excel 用户窗体多个组合框

vba - Excel 用户窗体多个组合框

问题描述

我有一个带有 (2) 组合框的 Excel 用户窗体。两个组合框都列出了“H”范围内的信息，ComboBox2 应该列出“V”范围内的信息。有什么想法我在这里做错了吗？我对 VBA 还是很陌生，我知道我的代码可能很草率。请保持通俗易懂，谢谢！

Private Sub Userform_Initialize()
LookAhead.Caption = Span & " Week Look Ahead"

' Sets range for ComboBox lists
    Dim rng As Range, r As Range, rng2 As Range, r2 As Range

    Set rng = Sheet1.Range("H2:H65536")
        For Each r In rng
            AddUnique r.value
        Next r
    Set rng2 = Sheet1.Range("V2:V65536")
        For Each r2 In rng2
            AddUnique r2.value
        Next r2

    End Sub

' Filter out duplicates in lists for UserForm ComboBoxes

Sub AddUnique(value As Variant)
    Dim i As Integer
    Dim inList As Boolean

    inList = False
    With Me.ComboBox1
        For i = 0 To Me.ComboBox1.ListCount - 1
            If Me.ComboBox1.List(i) = value Then
                inList = True
                Exit For
            End If
        Next i

        If Not inList Then
            .AddItem value
        End If
    End With

    Dim ii As Integer
    Dim inList2 As Boolean

    inList2 = False
    With Me.ComboBox2
        For ii = 0 To Me.ComboBox2.ListCount - 1
            If Me.ComboBox2.List(ii) = value Then
                inList2 = True
                Exit For
            End If
        Next ii

        If Not inList2 Then
            .AddItem value
        End If
    End With
End Sub

标签： vbaexcel

解决方案

成功！我这样做了，它似乎正在工作：

Dim Cl As Range
Dim Area As String
Dim TCR As String

    With CreateObject("scripting.dictionary")
        For Each Cl In Sheets("Projects").ListObjects("Table1").ListColumns(22).DataBodyRange
            If Not .exists(Cl.value) Then .Add Cl.value, Nothing
        Next Cl
         ComboBox1.Clear
         ComboBox1.List = Application.Transpose(.keys)
         .RemoveAll
    End With

    With CreateObject("scripting.dictionary")
        For Each Cl In Sheets("Projects").ListObjects("Table1").ListColumns(8).DataBodyRange
            If Not .exists(Cl.value) Then .Add Cl.value, Nothing
        Next Cl
         ComboBox2.Clear
         ComboBox2.List = Application.Transpose(.keys)
         .RemoveAll
    End With
    End Sub

推荐阅读