Combobox2 displays values of Combobox1 without combobox1 selected value

[Rokine]

Please pardon me if my question is so easy to solve because l am very new excel VBA. I have 2 comboboxes (on the same userform) sourcing data from the same table column C with values A1, A2, A3, A4, A5. What l want to do is that let's say if value A3 is selected in combobox1, combobox2 can only show A1, A2, A4, A5. If A1 is selected in combobox1, combobox2 must display only A2, A3, A4, A5 and so on...

Thanks in advance for your assistance.

 

[DanteAmor]

Hi and welcome to MrExcel!

Put all of the following code in your userform.
Change "Sheet1" to the name of your sheet.

Private Sub ComboBox1_Change()
  Dim i As Long
  ComboBox2.Clear
  If ComboBox1.ListIndex = -1 Then Exit Sub
  If ComboBox1.Value = "" Then Exit Sub
  For i = 0 To ComboBox1.ListCount - 1
    If i <> ComboBox1.ListIndex Then
      ComboBox2.AddItem ComboBox1.List(i)
    End If
  Next
End Sub

Private Sub UserForm_Activate()
  ComboBox1.List = Sheets("Sheet1").Range("C1:C" & Sheets("Sheet1").Range("C" & Rows.Count).End(3).Row).Value
End Sub

 

[Rokine]

Hi and welcome to MrExcel!

Put all of the following code in your userform.
Change "Sheet1" to the name of your sheet.

Private Sub ComboBox1_Change()
  Dim i As Long
  ComboBox2.Clear
  If ComboBox1.ListIndex = -1 Then Exit Sub
  If ComboBox1.Value = "" Then Exit Sub
  For i = 0 To ComboBox1.ListCount - 1
    If i <> ComboBox1.ListIndex Then
      ComboBox2.AddItem ComboBox1.List(i)
    End If
  Next
End Sub

Private Sub UserForm_Activate()
  ComboBox1.List = Sheets("Sheet1").Range("C1:C" & Sheets("Sheet1").Range("C" & Rows.Count).End(3).Row).Value
End Sub

Thank you very much your assistance. Please if l may ask, which code should be put in the Private Sub Combobox2_Change()?

 

[DanteAmor]

Please if l may ask, which code should be put in the Private Sub Combobox2_Change()?

Why do you want the event in combo2?

 

[Rokine]

Why do you want the event in combo2?

Thank you for your effort to help.
I am looking at rock types in an area, but I'm interested in only 2 (dominant and sub dominant) at a time for each entry. Therefore, both combobox1 and combobox2 will display the list of all possible rock types (say A1, A2, A3, A4) in the area. However, if a particular rock type is selected as dominant in combobox1, that same rock type cannot be selected as sub dominant rock in combobox2. To avoid the error of choosing the same rock type as dominant and sub dominant, when a rock type (say A2) is selected in combobox1 , combobox2 dropdown list must not include A2, so the list will be A1, A3, A4.

Sorry if my earlier explanation was not enough.

 

[DanteAmor]

I still don't understand, did you try the code that I put?
The code does exactly what you ask for. If you choose A2 in combobox1, you can only choose A1, A3 or A4 in combobox2

 

[Rokine]

I still don't understand, did you try the code that I put?
The code does exactly what you ask for. If you choose A2 in combobox1, you can only choose A1, A3 or A4 in combobox2

Please, it worked like magic. You've really saved me a huge headache. I'm highly grateful.

 

[DanteAmor]

Im glad to help you, thanks for the feedback.

 

[Rokine]

Hi, I would be thankful if I can get further help on the issue above.

The 2 combo boxes , strat1 and strat2 (image below) sourcing data from sheet "geology_lookups" are working perfectly. Thus, data populated in strat1 does not appear in strat2. Now the challenge for me is that in strat1 cbo, I am able to show multi column (C & D) data in the dropdown menu, but in strat2 cbo, only column C data displays. Can you help me display columns C & D in strat2, and show column C data only when a value is selected, as it happens in strat1.

Book1.xlsx
	A	B	C	D
1	Location		strat	strat_desc
2	AFG		T	Transporter Cover
3	ESN		R	Residual
4	BKS		S	Sediments
5	CHN		V	Volcanoclastics
6	CHS		I	Intrusive
7	FET		E	Extrusive
8			M	Metamorphic
9			B	Breccia
10			U	Protolith Unknown
11			N	Void/Discarded
12
geology_lookups

[/CODE

Private Sub UserForm_Initialize()
Dim wg As Worksheet
Dim iStrat As Long, stratRow As Long
Set wg = Worksheets("geology_lookups")
stratRow = wg.Cells(Rows.Count, 3).End(xlUp).Row
For iStrat = 2 To stratRow
    Me.cmbGeoStrat1.AddItem wg.Cells(iStrat, 3).Value
    Me.cmbGeoStrat1.List(Me.cmbGeoStrat1.ListCount - 1, 1) = wg.Cells(iStrat, 4).Value
Next iStrat
End Sub
]

Private Sub cmbGeoStrat1_Change()
  Dim i As Long
  Me.cmbGeoStrat2.Clear
  If Me.cmbGeoStrat1.ListIndex = -1 Then Exit Sub
  If Me.cmbGeoStrat1.Value = "" Then Exit Sub
  For i = 0 To Me.cmbGeoStrat1.ListCount - 1
    If i <> Me.cmbGeoStrat1.ListIndex Then
      With Me.cmbGeoStrat2
        .AddItem Me.cmbGeoStrat1.List(i)
        .List(.ListCount - 1, 4) = Sheets("geology_lookups").Cells(i, 4).Value
      End With
    End If
  Next
End Sub