Data displayed in ComboBox...

[kurtc]

Morning all, perhaps an easy question here, perhaps i'm being silly. I have a combobox which i have coded to display dynamic info. so it displays info in my A column and will display new info when i add something different.

What i would like it to do though is only display each name in that column once. So at the moment, if someone's name is in the first column and i select it and submit the data, their name will be another option in the drop down.

I would love it to only display each individual name once and not multiple times as the chances are there will be alot of the same name occuring if that makes sense?

The code i am using to make the info appear dynamically is below and i have defined the namelist with the second code

Private Sub UserForm_Initialize()
'Populate Name combo box.
Dim rngName As Range
Dim ws As Worksheet
Set ws = Worksheets("Querylog")
For Each rngName In ws.Range("Namelist")
Me.txtName.AddItem rngName.Value
Next rngName

End Sub

=OFFSET(QueryLog!$A$2:$A$14, 0, 0, COUNTA(QueryLog!$A:$A)-1,1)

 

[Trevor G]

Consider applying finding unique records first from the main column and copy them into another column and then use that to fill the combo.

Here is code for unique records from one column to another. You could then use this when the form is Activated or Initialized.

Sub Macro1()
'
' Macro1 Macro
'

'
Range("E1:E17").Select
Columns("E:E").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns( _
"E:E"), CopyToRange:=Range("Q1"), Unique:=True
End Sub

 

[kurtc]

Hi Trevor, thank you very much. Where would i need to put that code? anywhere in the userform or somewhere else?

 

[Trevor G]

Place it before it does anything else in the Form Initialize as per your example

Adjust to your columns, ranges etc first though

 

[kurtc]

Trevor, you are a gentleman and a scholar! Works perfectly, have just hidden the columns to tidy the look of it up and applied a filter to display the names alphabetically but here is the code as it is now, with the namelist define code also

Thanks again

Private Sub UserForm_Initialize()
' Macro1 Macro
Range("a1:a17").Select
Columns("a:a").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns( _
"a:a"), CopyToRange:=Range("J1"), Unique:=True

'Populate Name combo box.
Dim rngName As Range
Dim ws As Worksheet
Set ws = Worksheets("Querylog")
For Each rngName In ws.Range("Namelist")
Me.txtName.AddItem rngName.Value
Next rngName

End Sub

=OFFSET(QueryLog!$J$2:$J$14, 0, 0, COUNTA(QueryLog!$J:$J)-1,1)

 

[Trevor G]

Pleased to read you have working solution.