Exclude Duplicates in Drop Down

[excelenergy]

Hello...

In a way this is sort of the second half to a topic I posted here:
https://www.mrexcel.com/forum/excel-questions/1052587-problem-userform-initialize.html


This is an entirely different question though. In my first drop down I have three categories:
"Phones"
"Monitors"
"Computers"

Once a user selects "phones" > the second drop down is populated with
"Cisco"
"Bell"
"Oracle"

The issue is lets say, I change the selection in the first drop down from Phones to > Monitors. It will list the brands of monitors BUT it also is listing the brands of phones too.

What I'm curious about is, is there a way to exclude the brands of "phones" when a user selects monitors instead of phones. Right now - if you change the drop down to Monitors, it will list monitors, but it will still list the phones too.

Private Sub ComboBox1_Change()
Dim index As Integer
 index = ComboBox1.ListIndexComboBox2.Clear
Select Case index
     
Case Is = 0
With ComboBox2
.AddItem "CISCO
.AddItem "Bell"
.AddItem "Oracle"
        End With
     
     
     Case Is = 1
         With ComboBox2
.AddItem "Sony"
.AddItem "Hewlet Packard"
.AddItem "Dell"
End With
Case Is = 2
With ComboBox2
.AddItem "Acer"
.AddItem "Dell"
.AddItem "Samsung"
End With
End Select
End Sub
Private Sub ComboBox2_Change()
Select Case index
Case Is = 0
With Combobox3
.Additem "Cisco-Phone1"
.AddITEM "Cisco-Phone2"
End With
Case is = 1
With Combobox3
.Additem "Bell-Phone1"
.Additem "Bell-Phone2"
End With
Case is = 2
With Combobox3
.Additem "Oracle-Phone1"
.Additem "Oracle-Phone2"
End With
End Select
End Sub

 

[hotabae]

I'm seeing the same problem from the last thread.

You have

index = ComboBox1.ListIndexComboBox2.Clear

And it needs to read:

index = ComboBox1.ListIndex
ComboBox2.Clear

(needs to be on its own line)

If you're seeing multiple choices in the 2nd dropdown, it's probably because the 'Clear' Command isn't working properly.

 

[hotabae]

I am seeing something else though. Your third dropdown never resets. It (probably) needs a "clear" as well.

Also, It appears to be based on the first combobox rather than the second.

To me, the ComboBox2_Change() sub looks very incomplete. Is that where your issues are appearing?

 

[excelenergy]

Hey Hotbae,

I did correct that, sorry. I just copied the original code, from the original thread and just altered it a bit before posting my question.

As far as the changes you and Fluff suggested...they've all been made and the drop downs work the way they should..

My question is more so just around the third drop down.

See, when I change the second drop down from 'Phones' to 'Monitors'...It will show the Monitor brands, but it still also lists the phones too. So I'm wondering if there is a way to get it not to list the phones when monitors are selected...

I'm seeing the same problem from the last thread.

You have

index = ComboBox1.ListIndexComboBox2.Clear

And it needs to read:

index = ComboBox1.ListIndex
ComboBox2.Clear

(needs to be on its own line)

If you're seeing multiple choices in the 2nd dropdown, it's probably because the 'Clear' Command isn't working properly.

 

[hotabae]

First off, the basic issue I'm seeing is that there is no "ComboBox3.Clear" before you add the new options, which would cause the list to grow any time combobox2 is changed.

Try adding this line to the top of ComboBox2_Change()

Private Sub ComboBox2_Change()

'This Line will clear the third dropdown to prevent extra options
ComboBox3.Clear

I was playing around with my recreation of this form and I rewrote the code how I would've done it. Maybe that can provide some insight.

Private Sub ComboBox2_Change()

'This Line will clear the third dropdown to prevent extra options
ComboBox3.Clear

'These vars will "build" the options for later ("sony-mntr2")
Dim brand As String
Dim suffix As String

'Prevents errors from resetting Cb2
If Not Me.ComboBox2.ListIndex = -1 Then

    'Store the brandname for later (reading the value in CB2)
    brand = Me.ComboBox2.List(Me.ComboBox2.ListIndex)
    
End If

'Check the product type entry from cb1
Select Case Me.ComboBox1.ListIndex

    Case 0
        suffix = "phone"
    Case 1
        suffix = "comp"
    Case 2
        suffix = "mntr"
        
End Select
        
'Add two dropdown options to cb3 based on the prior two selections
With ComboBox3
    .AddItem brand & "-" & suffix & "1"
    .AddItem brand & "-" & suffix & "2"
End With
    
End Sub