Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Combo Box Duplicates

Status
Not open for further replies.

cmonthetic

IS-IT--Management
Oct 18, 2004
27
GB
Hi,

I am creating a form in Excel to enable easier searching for users of the spreadsheet.

I plan to have 3 combo boxes on the form.

The first combo box will be populated direct from the data in the main spreadsheet, with the other 2 combo boxes populated dependant on the data filtered from the selection made in Combo box 1.

The spreadsheet contains nearly 20000 rows and there is a lot of duplicated data within the column I would like to use for the first combo box.

Is it possible to only display unique values in the combo box from the selected range? and any ideas on how I would populate combo box 2 from the user selection made in combo box 1.

Any help would be greatly appreciated.

TIA

 




Hi,

take a look at Data > Filter > Advanced Filter -

Copy to another location
NO Criteria
Unique Values


Skip,

[glasses] [red][/red]
[tongue]
 
TIA,

It sounds to me like you are basically creating your own filter. You could insert an autofilter (data>filter>autofilter) to do the same thing as you are coding.

Anyways, here is one way you could populate your first combobox. There are others ways to accomplish this, but this way works. It's set up to only add items not already in the combobox. Keep in mind that I designed this code for a combobox/sheet which would only hold at a max 100 items. If your sheet has 20,000 items this code could be slow.

Code:
Private Sub UserForm_Initialize()

Dim Count As Long

    Count = 2

'Adds items to combobox, while preventing duplicate items
While Sheets(1).Cells(Count, 1).Value <> ""
    If ComboBox1.ListCount - 1 >= 0 Then
        For i = ComboBox1.ListCount - 1 To 0 Step -1
            If Sheets(1).Cells(Count, 1).Value <> ComboBox1.List(i) And i = 0 Then
                Me.ComboBox1.AddItem Sheets(1).Cells(Count, 1).Value
            ElseIf Sheets(1).Cells(Count, 1).Value = ComboBox1.List(i) Then
                Exit For
            End If
        Next i
    Else
        Me.ComboBox1.AddItem Sheets(1).Cells(Count, 1).Value
    End If
    Count = Count + 1
Wend

End Sub

You could populate the second combobox by a similar method. First you would need to filter the data based upon the user's combobox one selection (the advanced or autofilter would work, or you could write a code to do it). Then you would add the items the same way.

Hope this helps. Not sure I understand exactly what you are asking. Seems like adding an AutoFilter would solve your problem without having to create a form.

BD
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top