Consulting

Results 1 to 5 of 5

Thread: Populate and Sort a ComboBox with visible unique values from an Excel table

  1. #1

    Populate and Sort a ComboBox with visible unique values from an Excel table

    Good Morning,
    I'm new here. My name is Edoardo.
    I'm using this code for populing a ComboBox with visible unique values from an Excel table filtered:

    Private Sub UserForm_Initialize()

    Dim Lrow As Long, test As New Collection
    Dim Value As Variant, temp As Range
    Dim i As Single

    On Error Resume Next
    Set temp = Worksheets("Sheet1").ListObjects("Table1").ListColumns(1).Range

    For i = 2 To temp.Cells.Rows.Count
    If Len(temp.Cells(i)) > 0 And Not temp.Cells(i).EntireRow.Hidden Then
    test.Add temp.Cells(i), CStr(temp.Cells(i))
    End If
    Next i

    UserForm1.ComboBox1 = Clear

    For Each Value In test
    UserForm1.ComboBox1.AddItem Value
    Next Value

    Set test = Nothing

    End Sub

    That code works but I would like the values displayed in the ComboBox to be also sorted progressively. Can someone help me?
    My english is not perfect because I'm italian, I apologize.

    Thanks a lot to anyone who will help me

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Option Explicit
    
    
    Private Sub UserForm_Initialize()
        Dim a As Object
        Dim t As ListObject
        Dim rr As Range, r As Range
        Dim s As String
        
        Set a = CreateObject("system.collections.arraylist")
        Set t = Worksheets("Sheet1").ListObjects("Table1")
    
        With t.Range
            .AutoFilter 1, "<>"
            On Error Resume Next
            Set rr = Intersect(.Offset(1), .Columns(1).SpecialCells(xlCellTypeVisible))
            On Error GoTo 0
            If Not rr Is Nothing Then
                For Each r In rr
                    s = CStr(r.Value)
                    If Not a.contains(s) Then
                        a.Add s
                    End If
                Next
                a.Sort
                ComboBox1.List = a.toarray
            End If
        End With
          
    End Sub


    マナ

  3. #3
    Quote Originally Posted by mana View Post
    Option Explicit
    
    
    Private Sub UserForm_Initialize()
        Dim a As Object
        Dim t As ListObject
        Dim rr As Range, r As Range
        Dim s As String
        
        Set a = CreateObject("system.collections.arraylist")
        Set t = Worksheets("Sheet1").ListObjects("Table1")
    
        With t.Range
            .AutoFilter 1, "<>"
            On Error Resume Next
            Set rr = Intersect(.Offset(1), .Columns(1).SpecialCells(xlCellTypeVisible))
            On Error GoTo 0
            If Not rr Is Nothing Then
                For Each r In rr
                    s = CStr(r.Value)
                    If Not a.contains(s) Then
                        a.Add s
                    End If
                Next
                a.Sort
                ComboBox1.List = a.toarray
            End If
        End With
          
    End Sub


    マナ
    Thanks Mana for your reply, but your code doesn't work as I would like. In the previous message maybe I was not clear enough.

    After I've applied a filter to column 1, I would like to see, in the combobox, just the filtered data, without duplicates and in progressive sort. Instead, with your code, in the combobox, I see all data in column 1 (without duplicates and in progressive sort), even when I'm using a filter.

    Anyway, thank a lot for your reply

  4. #4
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Option Explicit
    
    
    Private Sub UserForm_Initialize()
        Dim a As Object
        Dim t As ListObject
        Dim rr As Range, r As Range
        Dim s As String
        
        Set a = CreateObject("system.collections.arraylist")
        Set t = Worksheets("Sheet1").ListObjects("Table1")
    
        On Error Resume Next
        Set rr = t.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    
    
        If Not rr Is Nothing Then
            For Each r In rr
                s = CStr(r.Value)
                If Not a.contains(s) Then
                    a.Add s
                End If
            Next
            a.Sort
            ComboBox1.List = a.toarray
        End If
        
    End Sub

  5. #5
    Quote Originally Posted by mana View Post
    Option Explicit
    
    
    Private Sub UserForm_Initialize()
        Dim a As Object
        Dim t As ListObject
        Dim rr As Range, r As Range
        Dim s As String
        
        Set a = CreateObject("system.collections.arraylist")
        Set t = Worksheets("Sheet1").ListObjects("Table1")
    
        On Error Resume Next
        Set rr = t.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    
    
        If Not rr Is Nothing Then
            For Each r In rr
                s = CStr(r.Value)
                If Not a.contains(s) Then
                    a.Add s
                End If
            Next
            a.Sort
            ComboBox1.List = a.toarray
        End If
        
    End Sub
    Thanks Mana, it works perfectly. It's exactly what I wanted!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •