Rowsource in combobox

[kelly mort]

Hello,
I want to load my comboboxes dynamically.

I have four comboxes and I am loading them with I rowsource.

What I want to do now is that, when I select an item from the first combobox, then inside the second combobox, show the list from the first combobox except the one selected in the first combobox . Then inside the third combobox, show the list from the second combobox except the one selected from the second combobox etc.

I hope this is achievable.

Thanks

Kelly

 

[MickG]

Re: Rowsource in combobox challenge

Try this:-
Keep the "Rowsource" for combobox1, remove the other rowsources.
See the codes below for combobox1 & 2, repeat for other comboboxes.
NB:- the addition of the previous combobox selection for each new combobox.

Private Sub ComboBox1_Change()
Dim R As Range
Me.ComboBox2.Clear
For Each R In Range(Me.ComboBox1.RowSource)
    If Not R = Me.ComboBox1.Value Then
        Me.ComboBox2.AddItem R.Value
    End If
Next R
End Sub



Private Sub ComboBox2_Change()
Dim R As Range
Me.ComboBox3.Clear
For Each R In Range(Me.ComboBox1.RowSource)
    If Not R = Me.ComboBox2.Value And Not R = Me.ComboBox1.Value Then
        Me.ComboBox3.AddItem R.Value
    End If
Next R
End Sub

 

[dmt32]

Re: Rowsource in combobox challenge

Hi,
untested by try

Private Sub ComboBox1_Change()
    PopulateComboBox Me.ComboBox1, Me.ComboBox2
End Sub


Private Sub ComboBox2_Change()
    PopulateComboBox Me.ComboBox2, Me.ComboBox3
End Sub


Sub PopulateComboBox(ByVal SelectionBox As Object, ByVal FillBox As Object)
    Dim Item As Variant
    FillBox.Clear
    For Each Item In SelectionBox.List
      If SelectionBox.Value <> Item Then FillBox.AddItem Item
    Next
End Sub

Add other Comboboxes if needed


Dave

 

[kelly mort]

Re: Rowsource in combobox challenge

Try this:-
Keep the "Rowsource" for combobox1, remove the other rowsources.
See the codes below for combobox1 & 2, repeat for other comboboxes.
NB:- the addition of the previous combobox selection for each new combobox.

Private Sub ComboBox1_Change()
Dim R As Range
Me.ComboBox2.Clear
For Each R In Range(Me.ComboBox1.RowSource)
    If Not R = Me.ComboBox1.Value Then
        Me.ComboBox2.AddItem R.Value
    End If
Next R
End Sub



Private Sub ComboBox2_Change()
Dim R As Range
Me.ComboBox3.Clear
For Each R In Range(Me.ComboBox1.RowSource)
    If Not R = Me.ComboBox2.Value And Not R = Me.ComboBox1.Value Then
        Me.ComboBox3.AddItem R.Value
    End If
Next R
End Sub

The combobox3 lists only the selected item I the first combobox

 

[MickG]

Re: Rowsource in combobox challenge

I'm not quite sure what this means:-

The combobox3 lists only the selected item I the first combobox

What is "I" ???

 

[kelly mort]

Re: Rowsource in combobox challenge

Hi,
untested by try

Private Sub ComboBox1_Change()
    PopulateComboBox Me.ComboBox1, Me.ComboBox2
End Sub


Private Sub ComboBox2_Change()
    PopulateComboBox Me.ComboBox2, Me.ComboBox3
End Sub


Sub PopulateComboBox(ByVal SelectionBox As Object, ByVal FillBox As Object)
    Dim Item As Variant
    FillBox.Clear
    For Each Item In SelectionBox.List
      If SelectionBox.Value <> Item Then FillBox.AddItem Item
    Next
End Sub

Add other Comboboxes if needed


Dave

Okay this works cool yet the fourth combobox I don't know how to end it.

I did

Private Sub ComboBox4_Change ()
PopulateCombobox Me.ComboBox4, Me.ComboBox4
End Sub

Yet did not work

Oops just found out I don't need that last private Sub

Thanks again

 

[kelly mort]

Re: Rowsource in combobox challenge

I'm not quite sure what this means:-



What is "I" ???

Oh sorry that's typo.

"I" means in .

The item selected in the first combobox, shows as the only list in the third box

 

[dmt32]

Re: Rowsource in combobox challenge

Oops just found out I don't need that last private Sub

Thanks again

Glad figured it out & solution working for you.

Dave

 

[MickG]

Re: Rowsource in combobox challenge

dmt32's code, is a good solution, I should stick with that !!

 

[kelly mort]

Re: Rowsource in combobox challenge

dmt32's code, is a good solution, I should stick with that !!

Thanks.

Have a nice time
Kelly