duplicate entries

[Pookiemeister]

Our Company has Production Lines A, B, and C. On my userform I have two comboboxes. First Combobox is called cmbSDPFLine and the secondCombobox is called cmbPrdCde. If the user selects "A-Line" under the SDPFLine combobox, PrdCde combobox will only display the products run on that line, and so on for the other two lines. The problem that occurs is if the user selects the wrong product or the wrong production line accidentally and clicks on the dropdown menu for either combobox, that combobox will double the selection. For example, if the combobox containing the list of production lines is selected the first time, it displays A-Line, B-Line, C-Line as it is supposed to. But when the user exits that combobox and enters another textbox and returns back to the combobox, the combobox now displays A-Line, B-Line, C-Line, A-Line, B-Line, C-Line. This occurs in both comboboxes and each time I return to a combobox it adds another duplication. How can I fix this issue? I really hope this makes sense. Thank you for your help in advance.

Private Sub cmbPrdCde_Enter()    If cmbSDPFLine.Value = "A-Line" Then
        With Me.cmbPrdCde
            .AddItem "0095-0-45 (Gelcap Club Pack Rework)"
            .AddItem "0096-1-93 (180MG 45CT LBL B)"
            .AddItem "0096-1-94 (5MG 55CT LBL BTL)"
            .AddItem "0096-1-98 (180MG 45CT LBL B)"
            .AddItem "0096-2-05 (5 MG 55 CT B UNLABELED)"
            .AddItem "0096-2-07 (5MG 55CT LBL BTL LABELED BOTTLE)"
            .AddItem "3510-2-01 (5MG 55CT)"
            .AddItem "3510-2-02 (5MG 55CT FROM BRITE STOCK)"
            .AddItem "3510-3-01 (5MG 80CT)"
            .AddItem "3510-1-01 (5mg 35ct)"
            .AddItem "0314-3-10 (150mg 90ct)"
            .AddItem "0013-3-40 (Sleep Gel 32ct)"
     End With
        
    ElseIf cmbSDPFLine.Value = "B-Line" Then
    
        With Me.cmbPrdCde
            .AddItem "4233-3-10 (CHILDRENS ODT 12 CT LAM GRAPHICS)"
            .AddItem "4232-6-02 (CHILDRENS ODT 24CT LAM GRAPHICS)"
            .AddItem "4233-3-15 (ODT 12ct)"
            .AddItem "4131-4-15 (60mg 24ct)"
            .AddItem "4131-2-15 (60mg 12ct)"
        End With
    Else
        With Me.cmbPrdCde
            .AddItem "4122-0-04 (24 HR GELCAPS)"
            .AddItem "4122-7-01 (24 HR GELCAP 8CT)"
            .AddItem "4123-5-01 (180MG 15CT - )"
        End With
    End If
End Sub

 

[JonXL]

Instead of building your lists with .AddItem, try to creating an array for each list and load it using .List. For example,

Dim aLine(1 To 12) As Variant

aLine(1) = "0095-0-45 (Gelcap Club Pack Rework)"
aLine(2) = "0096-1-93 (180MG 45CT LBL B)"
aLine(3) = "0096-1-94 (5MG 55CT LBL BTL)"
aLine(4) = "0096-1-98 (180MG 45CT LBL B)"
aLine(5) = "0096-2-05 (5 MG 55 CT B UNLABELED)"
aLine(6) = "0096-2-07 (5MG 55CT LBL BTL LABELED BOTTLE)"
aLine(7) = "3510-2-01 (5MG 55CT)"
aLine(8) = "3510-2-02 (5MG 55CT FROM BRITE STOCK)"
aLine(9) = "3510-3-01 (5MG 80CT)"
aLine(10) = "3510-1-01 (5mg 35ct)"
aLine(11) = "0314-3-10 (150mg 90ct)"
aLine(12) = "0013-3-40 (Sleep Gel 32ct)"

Then..

With Me.cmbPrdCde
            .List = aLine()
End With

Build each list and then just use the conditional to call the list you want. That will avoid the code adding the items over and over again each time you activate the combobox.

 

[Pookiemeister]

Thank you for the quick response. So what happens to the If...Then statements. Do's this process eliminate their use?

 

[JonXL]

You can declare all your lists first and set their values per the first code example I posted. Then use this to conditionally put them in your combobox:

Select Case cmbSDPFLine.Value
    Case "A-Line"
          Me.cmbPrdCde.List = aLine
    Case "B-Line"
          Me.cmbPrdCde.List = bLine
    Case "C-Line"
          Me.cmbPrdCde.List = cLine
End Select

Curious, though...

What happens if someone chooses A-Line and picks an A item from the combobox, then switches the first to B- Line but doesn't reselect the combobox and leaves it with an A item?

Would that combination be invalid for your data?

 

[Pookiemeister]

I'm sorry, I don't I follow where to put these lines of codes. Here's what I did but it didn't populate the cmbSDPFLine_Enter() Sub. I just tried the A-Line and B-Line first to see if it would display those production lines in that combo box, but it doesn't.

Private Sub cmbSDPFLine_Enter()    Select Case cmbSDPFLine.Value
        Case "A-Line"
            Me.cmbPrdCde.List = "aLine"
        Case "B-Line"
            me.cmbPrdCde.List = "bline"
    End Select
End Sub

Nothing is displayed inside the cmbSDPFLine.

Private Sub cmbPrdCde_Enter()

Dim aline(1 To 12) As Variant
Dim bline(1 To 10) As Variant


aline(1) = "0095-0-45 (Gelcap Club Pack Rework)"
aline(2) = "0096-1-93 (180MG 45CT LBL B)"
aline(3) = "0096-1-94 (5MG 55CT LBL BTL)"
aline(4) = "0096-1-98 (180MG 45CT LBL B)"
aline(5) = "0096-2-05 (5 MG 55 CT B UNLABELED)"
aline(6) = "0096-2-07 (5MG 55CT LBL BTL LABELED BOTTLE)"
aline(7) = "3510-2-01 (5MG 55CT)"
aline(8) = "3510-2-02 (5MG 55CT FROM BRITE STOCK)"
aline(9) = "3510-3-01 (5MG 80CT)"
aline(10) = "3510-1-01 (5mg 35ct)"
aline(11) = "0314-3-10 (150mg 90ct)"
aline(12) = "0013-3-40 (Sleep Gel 32ct)"


bline(1) = "4233-3-10 (CHILDRENS ODT 12 CT)"
bline(2) = "4232-6-02 (CHILDRENS ODT 24CT)"
bline(3) = "4233-3-15 (ODT 12ct)"
bline(4) = "4131-4-15 (60mg 24ct)"
bline(5) = "4131-2-15 (60mg 12ct)"


With Me.cmbPrdCde
    .List = aline()
    .List = bline()
End With

 

[JonXL]

Since you are setup to populate the list upon entering the combobox, put the whole procedure in there:

Private Sub cmbPrdCde_Enter()

Dim aline(1 To 12) As Variant
Dim bline(1 To 5) As Variant


aline(1) = "0095-0-45 (Gelcap Club Pack Rework)"
aline(2) = "0096-1-93 (180MG 45CT LBL B)"
aline(3) = "0096-1-94 (5MG 55CT LBL BTL)"
aline(4) = "0096-1-98 (180MG 45CT LBL B)"
aline(5) = "0096-2-05 (5 MG 55 CT B UNLABELED)"
aline(6) = "0096-2-07 (5MG 55CT LBL BTL LABELED BOTTLE)"
aline(7) = "3510-2-01 (5MG 55CT)"
aline(8) = "3510-2-02 (5MG 55CT FROM BRITE STOCK)"
aline(9) = "3510-3-01 (5MG 80CT)"
aline(10) = "3510-1-01 (5mg 35ct)"
aline(11) = "0314-3-10 (150mg 90ct)"
aline(12) = "0013-3-40 (Sleep Gel 32ct)"


bline(1) = "4233-3-10 (CHILDRENS ODT 12 CT)"
bline(2) = "4232-6-02 (CHILDRENS ODT 24CT)"
bline(3) = "4233-3-15 (ODT 12ct)"
bline(4) = "4131-4-15 (60mg 24ct)"
bline(5) = "4131-2-15 (60mg 12ct)"

Select Case cmbSDPFLine.Value
        Case "A-Line"
            Me.cmbPrdCde.List = aline
        Case "B-Line"
            me.cmbPrdCde.List = bline
End Select

Nothing is displayed inside the cmbSDPFLine.

What differently do you want in there? Your first post made it sound like you already had the population of that list figured out...