Excel User form Using VBA : Help on Unique Values.

sachinns

Board Regular
Joined
Jun 21, 2019
Messages
52
Hi Friends,

I am stuck with one thing in a User form . I have a dropdown where the data is taking from a sheet. I want only unique values in the droplist. I already have the code , but can someone please help me to edit this code which will give only unique values in the droplist.

Note : Dropdown Button Name is cmbSelChartAttr and datas for this dropdown is taking from "Inconsistency Order" Sheet.

Code:
:[/B]

Function formInitialize(ws As Worksheet)
Set ws = ThisWorkbook.Worksheets("Inconsistency Order")
If ws.Visible = True Then
    ws.Activate
Else
    ws.Visible = xlSheetVisible
    ws.Activate
End If
Dim i, lc
Dim j
Dim tecAttrVal As String
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
i = 0


For i = i + 1 To lc
    tecAttrVal = Cells(1, i).Value
    Me.cmbSelChartAttr.AddItem tecAttrVal
    'Me.cmbAttrXAxis.AddItem tecAttrVal
    tecAttrVal = ""
    Me.Label3.Visible = False
    Me.lsbOrderSet4SelAttr.Visible = False
Next i


Set ws = ThisWorkbook.Worksheets("Inconsistency")
If ws.Visible = True Then
    ws.Activate
Else
    ws.Visible = xlSheetVisible
    ws.Activate
End If


j = 0
For j = j + 1 To 103
    tecAttrVal = Cells(8, j).Value
    'Me.cmbSelChartAttr.AddItem tecAttrVal
    Me.cmbAttrXAxis.AddItem tecAttrVal
    tecAttrVal = ""
    Me.Label3.Visible = False
    Me.lsbOrderSet4SelAttr.Visible = False
Next j


End Function



[B]Thanks in Advance :)[/B]
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,919
Office Version
365
Platform
Windows
You could use a collection to filter unique values
- adapt generic code below to your needs

Unique values in A2:A30 populate ComboBox1
Code:
Sub GetUniqueValuesFromRange()
    Dim cel As Range, unique As New Collection
        For Each cel In [COLOR=#ff0000]Range("A2:A30")[/COLOR]
            If cel.Value <> "" Then
                On Error Resume Next
                unique.Add CStr(cel), CStr(cel)
                If Err.Number > 0 Then
                    On Error GoTo 0
                Else
                    [COLOR=#ff0000]ComboBox1[/COLOR].AddItem cel.Value
                End If
            End If
        Next
End Sub
 

Forum statistics

Threads
1,085,513
Messages
5,384,103
Members
401,881
Latest member
Dato

Some videos you may like

This Week's Hot Topics

Top