Unique item with conditions in combobox problem

Question

I try to make a list for a combobox with unique item and a selection. I wrote this code without positive conclusion. Any helpfull people to guide me please ?

Private Sub UserForm_Initialize()
    'déclaration variable dictionnaire et nombre de ligne article
    Dim oDictionary As Object
    Dim nb_ligne As Integer

    'création objet dictionnaire
    Set oDictionary = CreateObject("Scripting.Dictionary")

    'détermination du nombre de lignes artcile
    nb_lignes = Sheets("Articles").Cells(4, no_colonne).End(xlDown).Row

    With ComboBox_depart_article
        'création de la boucle
        For i = 4 To nb_lignes
            'Condition si article en stock
            If Cells(i, 10) = "Stock" Then 'si vrai do nothing condition suivante
                'Condition si article existe dans dictionnaire
                If oDictionary.exists(Sheets("Articles").Cells(i, 1).Value) Then 'si vrai do nothing
                Else
                    'Ajout de la valeure de la celule dans le dictionnaire et le combobox
                    oDictionary.Add Cells(i, 1).Value, 0
                    .AddItem Cells(i, 1).Value
                End If
            Else
            End If
        Next i
    End With
End Sub

Maybe you'll find an answer in this rather similar [question](https://stackoverflow.com/q/59480927/9758194). Edit the range to be put into your array, and add a check for `i,10` — JvdV, Dec 30 '19 at 09:28

score 0 · Answer 1 · answered Dec 30 '19 at 09:39

try to be more precise with specifying the ranges

for example, here:

    If Cells(i, 10) = "Stock" Then

you are referencing the active sheet. Shoud it not be:

    If Sheets("Articles").Cells(i, 10) = "Stock" Then

?

the same applies here:

            oDictionary.Add Cells(i, 1).Value, 0
            .AddItem Cells(i, 1).Value

one more small suggestion to enhance your code: get rid of your "else" in if-else statement with:

       If NOT oDictionary.exists(Sheets("Articles").Cells(i, 1).Value) Then

pay attention to how you declared the variable "nb_lignes" (you declared it as "nb_ligne"). Always use "Option Explicit" in the very above of the page.

thanks for your advice.I will pay attention of it !! – Sebastien Blanc Dec 30 '19 at 09:55 — Sebastien Blanc, Dec 30 '19 at 09:55
But I still have a execution error message "1004" or "9" – Sebastien Blanc Dec 30 '19 at 09:56 — Sebastien Blanc, Dec 30 '19 at 09:56

Unique item with conditions in combobox problem

1 Answers1