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

 

[kelly mort]

Re: Rowsource in combobox challenge

Glad figured it out & solution working for you.

Dave

Hello , I am having some problems :

From the above code you gave me, I am populating those controls back with a listbox. Those four comboxes are not the only ones I am populating. I am looping through 21 controls and those comboboxes are the last four controls in the loop.

Everything is working fine from my end here until when I click those comboboxes, then I get the error:380

Could not set the value property. Invalid property value.


I know I am doing something the wrong way but I can't seem to figure it out yet.

 

[dmt32]

Re: Rowsource in combobox challenge

Hi,
suggested solution was given on basis of information provided - if using suggestion in a more complex way you will need to share all your code to help forum understand exactly what it is you are trying to do


Dave

 

[kelly mort]

Re: Rowsource in combobox challenge

These are my four comboxes

Private Sub RW18_Change()
Select Case Me.CmdAdd.Enabled
Case Is = True
If Me.Rw3.Text = "SCIENCE" Or Me.Rw3.Text = "GENERAL ARTS" Then
        PopulateCombobox Me.RW18, Me.RW19
    End If
    Case Else
    Exit Sub
End Select
End Sub


Private Sub RW19_Change()
Select Case Me.CmdAdd.Enabled
Case Is = True
If Me.Rw3.Text = "SCIENCE" Or Me.Rw3.Text = "GENERAL ARTS" Then
        PopulateCombobox Me.RW19, Me.RW20
    End If
    Case Else
    Exit Sub
End Select
End Sub
Private Sub RW20_Change()
Select Case Me.CmdAdd.Enabled
Case Is = True
If Me.Rw3.Text = "SCIENCE" Or Me.Rw3.Text = "GENERAL ARTS" Then
        PopulateCombobox Me.RW20, Me.RW21
    End If
    Case Else
    Exit Sub
End Select
End Sub
Sub PopulateCombobox(ByVal selectionBox As Object, ByVal FillBox As Object)
    Dim Item As Variant
    On Error Resume Next
    FillBox.Clear
    For Each Item In selectionBox.List
        If selectionBox.Value <> Item Then FillBox.AddItem Item
    Next
    On Error GoTo 0
    Exit Sub
End Sub

This ia another combobox that determines which rowsource to fill the above comboboxes.

Private Sub Rw3_Change()
If Me.CmdAdd.Enabled = True Then
    Select Case Me.Rw3.Text
        Case Is = "SCIENCE"
            Me.RW18.Value = ""
            Me.RW19.Value = ""
            Me.RW20.Value = ""
            Me.RW21.Value = ""
            Me.RW18.RowSource = "SCIENCE"
        Case Is = "GENERAL ARTS"
            Me.RW18.Value = ""
            Me.RW19.Value = ""
            Me.RW20.Value = ""
            Me.RW21.Value = ""
            Me.RW18.RowSource = "G_ARTS"
        Case Is = "VISUAL ARTS"
            Me.RW18.Value = ""
            Me.RW19.Value = ""
            Me.RW20.Value = ""
            Me.RW21.Value = ""
            Me.RW18.RowSource = "V_ART1"
            Me.RW19.RowSource = "V_ART2"
            Me.RW20.RowSource = "V_ART3"
            Me.RW21.RowSource = "V_ART4"
        Case Is = "HOME ECONOMICS"
            Me.RW18.Value = ""
            Me.RW19.Value = ""
            Me.RW20.Value = ""
            Me.RW21.Value = ""
            Me.RW18.RowSource = "H_E1"
            Me.RW19.RowSource = "H_E2"
            Me.RW20.RowSource = "H_E3"
            Me.RW21.RowSource = "H_E4"
        Case Is = "BUSINESS"
            Me.RW18.Value = ""
            Me.RW19.Value = ""
            Me.RW20.Value = ""
            Me.RW21.Value = ""
            Me.RW18.RowSource = "B_S1"
            Me.RW19.RowSource = "B_S2"
            Me.RW20.RowSource = "B_S3"
            Me.RW21.RowSource = "B_S4"
    End Select
    End If
End Sub

This is the listbox click events and this is where I get the error after I click any of the four comboboxes and gets back here. This line

 Me.Controls("Rw" & ac).Value = .Text

shows the error.

Private Sub lstView_Click()
Dim n As Integer, ac As Integer, i As Integer, j As Integer
Me.CmdAdd.Enabled = False
Me.CmdEdit.Enabled = True
Me.CmdDelete.Enabled = True
Application.ScreenUpdating = True
        For ac = 1 To 21
        Me.Controls("Rw" & ac).Value = ""
    Next ac
        Me.Rw3.RowSource = ""
        
        i = Me.lstView.ListIndex
    With lstView
        For ac = 1 To .ColumnCount
        .TextColumn = ac
        Me.Controls("Rw" & ac).Value = .Text
        Next ac
    End With
    Me.Rw3.RowSource = "PROGRAMMES"


    Application.ScreenUpdating = True
    End Sub

 

[dmt32]

Re: Rowsource in combobox challenge

Hi,
My solution uses the Additem method to populate the Combobox based on the List values of the specified Combobox. AddItem method will not work if the ComboBox is bound to data using RowSource - RowSource must be cleared before using AddItem.

Either remove any calls in your codes that set the Combobox RowSource where they are to be populated with PopulateComboBox code or just add the line shown in RED & see if this resolves your issue,

Sub PopulateComboBox(ByVal SelectionBox As Object, ByVal FillBox As Object)
    Dim Item As Variant
    FillBox.Clear ': FillBox.Enabled = False
    FillBox.RowSource = ""
    For Each Item In SelectionBox.List
      If SelectionBox.Value <> Item Then FillBox.AddItem Item
    Next
    FillBox.Enabled = CBool(FillBox.ListCount > 0)
End Sub

Dave

 

[My Aswer Is This]

Re: Rowsource in combobox challenge

I always load my comboboxes like this:
You can then add or remove items to the Combobox

ComboBox2.List = Range("E1:E20").Value

 

[kelly mort]

Re: Rowsource in combobox challenge

Hi,
My solution uses the Additem method to populate the Combobox based on the List values of the specified Combobox. AddItem method will not work if the ComboBox is bound to data using RowSource - RowSource must be cleared before using AddItem.

Either remove any calls in your codes that set the Combobox RowSource where they are to be populated with PopulateComboBox code or just add the line shown in RED & see if this resolves your issue,

Sub PopulateComboBox(ByVal SelectionBox As Object, ByVal FillBox As Object)
    Dim Item As Variant
    FillBox.Clear ': FillBox.Enabled = False
    FillBox.RowSource = ""
    For Each Item In SelectionBox.List
      If SelectionBox.Value <> Item Then FillBox.AddItem Item
    Next
    FillBox.Enabled = CBool(FillBox.ListCount > 0)
End Sub

Dave

Hi,
Thanks for your suggestions.

I tried the updated version of the script you gave and still had that issue so I tried to handle the error from the listbox click event.
Now I am cool to go.

Thanks again.

Kelly

 

[kelly mort]

Re: Rowsource in combobox challenge

I always load my comboboxes like this:
You can then add or remove items to the Combobox

ComboBox2.List = Range("E1:E20").Value

Okay thanks very much for this tip.

I will adapt to this style soon.

For the issue I had above there, this may not help me.

Thanks again.

Kelly

 

[dmt32]

Re: Rowsource in combobox challenge

Hi,
glad manage to resolve.

On personal note, I would generally avoid using rowsource as method to populate comboboxes / listboxes - Use List property (from a range or an array) as already mentioned or Additem

Dave

 

[kelly mort]

Re: Rowsource in combobox challenge

Hi,
glad manage to resolve.

On personal note, I would generally avoid using rowsource as method to populate comboboxes / listboxes - Use List property (from a range or an array) as already mentioned or Additem

Dave

Okay thanks Dave. As it is now, I am now learning so there are some mistakes I may be prone to making. Haha. Thanks a lot.

I don't really understand the array concept yet though. But I will look into it shortly.

Kelly

 

[My Aswer Is This]

Re: Rowsource in combobox challenge

Here are two example of Arrays

Private Sub CommandButton2_Click()
ComboBox1.List = Array("One", "Ten", "Twelve")
ComboBox1.List = Array(Cells(1, 1).Value, Cells(5, 1).Value, Cells(7, 1).Value)
End Sub