Solved: Removing Duplicate items from a combobox

[ukdane]

Hi,
I have a combobox, which loads items from some code, so as to work like an autofilter. However the code loads items that appear more than once.
The code is below. Working with Excel 2000. How do I remove the duplicate items from the list?
The combobox is called filter_shipment

Dim usedshipment as string
Dim totalshipments as string
Dim cell as range

On Error Resume Next

usedshipment = "$C$3:" & ActiveSheet.Range("C65536").End(xlUp).Address
totalshipments = Range(usedshipment).Count 'counts the shipments on the transport
With filter_shipment
.Clear
For Each cell In Range(usedshipment).SpecialCells(xlCellTypeVisible)
.AddItem cell.Value
Next cell
End With


Thanks for helping.

[Bob Phillips]

Dim usedshipment As String
Dim totalshipments As String
Dim cell As Range
Dim arytIndex As Long
Dim ary As Variant


ReDim ary(1 To 1)
For Each cell In Range(usedshipment).SpecialCells(xlCellTypeVisible)

If IsError(Application.Match(cell.Value, ary, 0)) Then

aryindex = aryindex + 1
ReDim Preserve ary(1 To aryindex)
ary(aryindex) = cell.Value
Next cell
On Error Resume Next

usedshipment = "$C$3:" & ActiveSheet.Range("C65536").End(xlUp).Address
totalshipments = Range(usedshipment).Count 'counts the shipments on the transport
With filter_shipment
.Clear
.List = ary
End With

[Krishna Kumar]

Hi,

Or..

Dim a, v, z
a = Range("c3", Range("c" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For Each v In a
If Not IsEmpty(v) Then
If Not .exists(v) Then .Add v, Nothing
End If
Next
z = .keys
End With
With filter_shipment
.Clear
.List = Application.Transpose(z)
End With

HTH

[ukdane]

XLD: I was getting an error (Next without For) error for your code :-(

Krishna: However your code works a treat. Exactly what I needed, thanks.

There is just one minor problem.

I need to repeat the code, for a few more comboboxes, (more filter criteria), which in itself isn't a problem, I've just adapted the code for each additional filter.

HOWEVER, If the user then filters one box, and the next filter box is no longer valid (the worksheet is effectively empty, but the search criteria are still visible). Instead of returning an empty field, it's returning the contents of field C2, and I'd like the field to just be empty.
How do I adjust the code so that the search criteria is " "?

Thanks again, for the code above.

[Krishna Kumar]

Hi,

Sorry, it's not clear to me. If you could attach a stripped version of your workbook here with the expected result, that would be fine.

[ukdane]

Sure, I'll try and explain a little better.
First the code:
Dim usedshipment As String
Dim totalshipments
Dim cell As Range
Dim a, v, z
'set shipmentnr
On Error Resume Next
usedshipment = "$C$3:" & ActiveSheet.Range("C65536").End(xlUp).Address
If usedshipment = "$C$3:$C$2" Then
With filter_shipment
.Clear
End With
Else
totalshipments = Range(usedshipment).Count 'counts the shipments on the transport
With filter_shipment
.Clear
For Each cell In Range(usedshipment).SpecialCells(xlCellTypeVisible)
.AddItem cell.Value
Next cell
End With
End If
'set user
a = Range("E3", Range("E" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For Each v In a
If Not IsEmpty(v) Then
If Not .exists(v) Then .Add v, Nothing
End If
Next
z = .keys
End With
With filter_user
.Clear
.List = Application.Transpose(z)
.AddItem ("Alle")
End With
'set status
a = Range("D3", Range("D" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For Each v In a
If Not IsEmpty(v) Then
If Not .exists(v) Then .Add v, Nothing
End If
Next
z = .keys
End With
With filter_status
.Clear
.List = Application.Transpose(z)
.AddItem ("Alle")
End With

Note; I've adapted your code for comboboxes: filter_user, and filter_status, whilst I've allowed filter_shipment to continue showing multiple items.

Now imagine the table:

USER | STATUS | SHIPMENT
1 | A | Result 1
1 | B | Result 2
2 | A | Result 3

If the end user starts by filtering the combobox USER, to only show 2, then in the SHIPMENT combobox Result 3 will be visible, but in STATUS, it still shows options: A, B, and Alle (from the code). If the user then chooses to additionally filter STATUS to B, the SHIPMENT box is empty. which is fine. BUT If I then look at the contents of the USER and STATUS boxes, it reports the filtered contents, plus Alle (in both as per the code) and the column heading which happens to be User and Status respectively.

So the question is, if a user filters one of the comboboxes, and it results in removing one of the valid options is another combobox, how do I remove that option from the box? (I've tried recalling the code to repopulate the boxes, but it didn't work)

Hope that's a little clearer.

Cheers.

[Krishna Kumar]

Hi,

Try,

Private Sub filter_shipment_Change()
Dim a, i As Long, z
a = Range("c3:d", Range("c" & Rows.Count).End(xlUp).Row) ' i hope user in Col D
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For i = 1 To UBound(a, 1)
If Not IsEmpty(a(i, 1)) Then
If a(i, 1) = Me.filter_shipment.Value Then
If Not .exists(a(i, 3)) Then .Add a(i, 3), Nothing
End If
End If
Next
z = .keys
End With
With filter_user
.Clear
.List = Application.Transpose(z)
.AddItem ("Alle")
End With
End Sub

Private Sub filter_user_Change()
Dim a, i As Long, z
a = Range("d3:e", Range("d" & Rows.Count).End(xlUp).Row) 'hope status in Col E
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For i = 1 To UBound(a, 1)
If Not IsEmpty(a(i, 1)) Then
If a(i, 1) = Me.filter_user.Value Then
If Not .exists(a(i, 2)) Then .Add a(i, 2), Nothing
End If
End If
Next
z = .keys
End With
With filter_status
.Clear
.List = Application.Transpose(z)
End With
End Sub

Private Sub UserForm_Initialize()
Dim usedshipment As String
Dim totalshipments
Dim cell As Range

'set shipmentnr
On Error Resume Next
usedshipment = "$C$3:" & ActiveSheet.Range("C65536").End(xlUp).Address
If usedshipment = "$C$3:$C$2" Then
With filter_shipment
.Clear
End With
Else
totalshipments = Range(usedshipment).Count 'counts the shipments on the transport
With filter_shipment
.Clear
For Each cell In Range(usedshipment).SpecialCells(xlCellTypeVisible)
.AddItem cell.Value
Next cell
End With
End If
End Sub

HTH

[ukdane]

Hi, and thanks for your reply.

I've been looking through the code, and I'm not sure I understand it exactly. Can you annotate it a little, to explain what is going on.

I should point out, that in the example above there are only 3 comboboxes, however in my actual code I have about 4. So I would have to adapt the code for all the comboboxes.

Also, I note there is no code for a change in Status.
And what do you mean by "i hope user in Col D"?

I'll try and explain where my code is located.
The ShipmentNr appears in Range C. (Cell C2 contains the column header, so data to populate the combobox is from C3 and down). This combobox does not need to remove duplicate items.
The Status appears in Range D. (Cell D2 contains the column header, so data to populate the combobox is from D3 and down)
The User appears in Range E. (Cell E2 contains the column header, so data to populate the combobox is from E3 and down)

One of the other comboboxes I have is called Dept.
The Dept appears in Range F. (Cell F2 contains the column header, so data to populate the combobox is from F3 and down)

The final combobox I have is populated in the programming itself (as there are only 3 options). However, it could still effect the values that appear in the other comboboxes.

I appreciate your time and help.

[Krishna Kumar]

Hi,

Can you please upload a stripped version of your workbook here?

[ukdane]

Sure, hopefully this will work for you.

Press the button to show the form that I'm having problems with.
You can see on the form, the drop down boxes.
Bruger | Status | Afdeling | FCL/LCL | Shipment.

In this example, if you select Bruger (user): MLL, everything still works.
However, if I then also select under FCL/LCL: LCL, no shipments are visible (this is correct), but if you look at the values of the other comboboxes, the values have changed incorrectly, and now also show the value of row 2. (This is incorrect, the values should NOT change at all).

As always, thanks for looking.

[Krishna Kumar]

Hi,

I didn't look at the whole code.

But I think adding 'Exit Sub' before the Else in populateform1 would solve the problem.

[ukdane]

Hi Krishna,
Thank you, thank you. It seems to have helped no end. Although items still disappear from the lists, at least it doesn't show the wrong information (the user just has to amend the filters).

Can the Attached File be removed from the post above?

Cheers

[ukdane]

What do I need to add to the code, so that the user can't enter any text in the combobox, but can only select from the options available?

[GTO]

Hi Ukdane,

Spotted this after logging out. Didn't read thru thread, but only to last question... To eliminate user from entering or editing your items in a combobox:

During Design time: in the Properties window, change Style from the default (0, or fmStyleDropDownCombo) to: 2 (fmStyleDropDownList).

(note: may also be done during run time)

Have a great day,

Mark

[ukdane]

Thanks GTO, I'll give it a go.

[Nobody]

Just stumbled across this while looking for the same question XLD`s code worked a treat, adapted it a bit for my application, but it was missing and ENDIF which caused the error UKdane reported. Easier for me to follow and I already had a for next loop set up which was finding and then adding each item as I went, I just needed to make sure it didn't duplicate, creating an array instead and checking the array on each loop, and then adding the whole array once, to populate the combo box is great.