[RESOLVED] Default Excel User form Using VBA : Help on Unique Values.

[sachinns]

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]:

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
End Function



Thanks in Advance

[westconn1]

possibly the simplest method is to add the values to a collection, collections can not have duplicate values, then only add the value that can be added to the collection to be also added to the combobox

an alternative is to use sql to generate a recordset of unique values

you do not need the worksheet to be visible or activated for the code to work in most cases

[sachinns]

Thanks.

I am very new to VBA, so can you please modify the existing code . In which it will give only unique values in the combobox called "cmbSelChartAttr" .

[westconn1]

I am very new to VBA, so can you please modify the existing code

you always will be if you do not have a go for yourself

[westconn1]

try modifying like

Dim  c As Collection
Set c = New Collection

On Error Resume Next


For i = i + 1 To lc
tecAttrVal = Cells(1, i).Value
    c.Add tecAttrVal , tecAttrVal 
    If Err.Number = 0 Then
        cmbSelChartAttr.AddItem tecAttrVal 
        Else
        Err.Clear
    End If

Next i
Me.Label3.Visible = False
Me.lsbOrderSet4SelAttr.Visible = False

i have only put in the additional and modified parts

[sachinns]

Thanks a lot. Its working for me...

Client is asking for one weird condition. Ex: if in ‘Inconsistency order’ sheet there are two columns called “ABC” and “ABC – Check” in the dropdown will be reported only “ABC” .

Is it possible to do this on VBA ?

[westconn1]

Is it possible to do this on VBA ?

probably, but i do not understand what you want

[sachinns]

Hi,
I will explain it.

Suppose we have sheet with some column values. These values are listed in a combobox list. Example : We have values like : ABC , DEF , GHI , ABC - check , DEF , GHI - check .

So In our combobox droplist , It should only have ABC , DEF , GHI .

Basically if there are two columns called “Release 2” and “Release 2 – Check” , The droplist will only show “Release 2”


The code for this combobox is same as above.

[westconn1]

set a range of the 2 columns then add to the combobox using a collection as before

lr = usedrange.rows.count
set r = range("a1:b" & lr)
for each cel in r

you would have to specify the worksheet for the usedrange

[sachinns]

Where to add this code ?? My sheet name is "Inconsistency Order" and combobox name is "cmbSelChartAttr" .

This is the code for my combobox list.

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 cell As Range
Dim tecAttrVal As String
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
i = 0

Dim c As Collection
Set c = New Collection

On Error Resume Next


For i = i + 1 To lc
tecAttrVal = Cells(1, i).Value
c.Add tecAttrVal, tecAttrVal
If Err.Number = 0 Then
cmbSelChartAttr.AddItem tecAttrVal
Else
Err.Clear
End If

Next i
Me.Label3.Visible = False
Me.lsbOrderSet4SelAttr.Visible = False



End Function