filling ID in combobox without duplicates Doesn't work

Alaa

Don't have time to check but try removing .Value so that line looks in a range (not a single value) using:

For Each x In .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row) 

Revision #1 13 July 2024

In the attached revised file, I've done the change mentioned above and also corrected the code elsewhere - see bold items and comments below:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim dar As Object
Dim x

Set dar = CreateObject("System.Collections.ArrayList")
' say which worksheet ws is (ActiveSheet, given it's launched from a button on it...
Set ws = ActiveSheet

    With ws
       For Each x In .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row) '.Value
         ' check the cell value is in dar
         If Not dar.Contains(x.Value) Then dar.Add CStr(x.Value)
        Next
   End With

dar.Sort
Me.ComboBox1.List = dar.Toarray()


End Sub

Now, when you launch the form, ComboBox1 shows a duplicate-free list like Alaa, ALIAN and ALIIN. Note that wiithout the added qualifier x.Value, you were checking if the ArrayList dar contained the range (cell) x (not what it contains) so every cell value was added and you had several duplicates.

This file works well on both Excel 2010 and 365. I don't have 2019 to check but hope it works. (If not, it may be to do with the absence of .NET or the version of .NET on your 2019 laptop- since I think the System.Collections.ArrayList needs the .NET framework).

Hope this fixes things for you. If so, please select this Answer.

 Hello Alaa,

First I would recommend you change the title of yout post to reflect the question asked in your post. Your title would lead others to think it has to do with opening the workbook when the question you ask is about the userform code for the combobox

I could only find 2 small errors with your code.

1) You declare variable ws as a Worksheet but in your With block you don't specify which worksheet the code applies to. I changed this to:

With Sheets("AZX")

2) in your line:

For Each x In .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row).Value

After making these 2 small changes, the combobox of the userform only has 3 names in it.

If this is the rsult you are wanting please mark my answer as selected.

Cheers   :-)