userform combobox fills from selection on sheet

Hello all,

I am stuck trying to fill comboboxs on a user form from what a person selects on the sheet.

In attached if person picks 5 fruits from the list each combobox will load the 5 choices.
The fruits are listed in column B while the person will put an X in column C.
The fruits in B with the X in column C will load in combo box.

This sounds easy but not for me.

Please take a look at attached.
Any help is appreciated.

Hi mdshotgun

Remove the code from open workbook event
and in the userform add

Option Explicit
Private Sub ComboBox1_Enter()
    Dim rng As Range
    Dim xFruit
    Me.ComboBox1.Clear
    Set rng = Range("Fruits")
    For Each xFruit In rng
        Debug.Print xFruit
        If UCase(xFruit.Offset(0, 1).Value) = "X" Then
            Me.ComboBox1.AddItem xFruit
        End If
    Next
End Sub
Private Sub ComboBox2_Enter()
    Dim rng As Range
    Dim xFruit
    Me.ComboBox2.Clear
    Set rng = Range("Fruits")
    For Each xFruit In rng
        Debug.Print xFruit
        If UCase(xFruit.Offset(0, 1).Value) = "X" Then
            Me.ComboBox2.AddItem xFruit
        End If
    Next
End Sub

and change the showmodlal setting of the userform to false

Hi,

Put this in the userforms code

Option Explicit
Sub Userform_Initialize()

Dim arr() As String
Dim counter As Integer
Dim i As Integer

counter = 0
For i = 4 To 13
    If Sheets(1).Cells(i, 3) = "x" Then
        ReDim Preserve arr(counter)
        arr(counter) = Sheets(1).Cells(i, 2)
        counter = counter + 1
    End If
Next i

With ComboBox1
    .Clear
    .List = arr
    .ListIndex = 0
End With

With ComboBox2
    .Clear
    .List = arr
    .ListIndex = 0
End With

With ComboBox3
    .Clear
    .List = arr
    .ListIndex = 0
End With

With ComboBox4
    .Clear
    .List = arr
    .ListIndex = 0
End With

With ComboBox5
    .Clear
    .List = arr
    .ListIndex = 0
End With

End Sub

When the userform loads, it will take the values marked with x and put them in each combobox

Yes,

Put this in the userform code

Sub ComboBox1_Click

With Combobox2
    .RemoveItem Combobox1.value
end with

With Combobox3
    .RemoveItem Combobox1.value
end with

With Combobox4
    .RemoveItem Combobox1.value
end with

With Combobox5
    .RemoveItem Combobox1.value
end with

End sub

Create similar codes for cb2-5

or:

Sub Userform_Initialize()
  for j=1 to 5
   me("Combobox" & j).list=sheets(1).range("B4:B13").value
  next
End Sub

On which line is it giving an error?

*EDIT

By the way, if it's only possible to select a different value in each combobox, why not populate each combobox with a different value, and skip the user selection?

You could have adapted the code yourself

Sub Userform_Initialize()
  for j=1 to 5
   me("Combobox" & j).list=filter([transpose(if(C4:C13="X","~",B4:B13))],"~",false)
  next
End Sub

Try this

Option Explicit
Dim iX As Integer

Private Sub ComboBox1_Change()
    RemoveIt (2)
End Sub


Private Sub ComboBox2_Change()
    RemoveIt (3)
End Sub

Private Sub ComboBox3_Change()
    RemoveIt (4)
End Sub

Private Sub ComboBox4_Change()
    RemoveIt (5)
End Sub

Private Sub UserForm_Initialize()
    Dim rCl As Range
    For iX = 1 To 5
        For Each rCl In Range("Fruits")
            Me("Combobox" & iX).AddItem rCl.Text
        Next rCl
    Next iX
End Sub

Sub RemoveIt(iJ As Integer)
    For iX = iJ To 5
        Me("Combobox" & iX).RemoveItem Me("ComboBox" & iJ - 1).ListIndex
    Next iX
End Sub

Thanks for the rep. whicc code did you use?

I know you cannot use RemovItem if the Combo is populated using RowSource, I'm not sure about List Ptroprty. Hence, I used a Loop to load each combo using AddItem