Reload Combobox based on cell values

[sharky12345]

I've got this which loads a series of Comboboxes on a Userform;

Lastrow = Sheet5.Range("J43").End(xlUp).Row + 1
For Each cell In Sheet5.Range("J2:J" & Lastrow)
If cell.Value = "No" Then
For i = 1 To 24
Me.Controls("ComboStaff" & i).AddItem cell.Offset(0, -3).Value
Next
End If
Next

I need each Combobox to be effectively reloaded as each of the others change, (because as a value is selected in the Combobox it updates the relevant column with 'Yes').

I've tried adding the following;

Me.Controls("ComboStaff" & i).Clear

before the .AddItem part but all that does it clear every item except the last used row.

Can anyone suggest a simple workaround?

 

[My Aswer Is This]

To clear a Combobox use this:

ComboBox1.Clear

 

[My Aswer Is This]

I would think you should run this first and then run your script to reload your Comboboxes.

To clear your Comboboxes this should work.

Private Sub CommandButton3_Click()
For i = 1 To 2
Me.Controls("Combobox" & i).Clear
Next
End Sub

 

[My Aswer Is This]

It appears as if you want all 25 comboboxes loaded with the same values.

In my example I named My Comboxes Combobox1 and then Combobox2 and so on.

My script clears Combobox1
Then loads Combobox1 with the values you wanted.

And then I have a loop that loads all the other 24 comboboxes with the same value

Like this Combobox2.List=Combobox1.List

Private Sub CommandButton4_Click()
'Modified 3-9-18 9:00 PM EST
Dim i As Long
Lastrow = Sheet5.Range("J43").End(xlUp).Row + 1
ComboBox1.Clear
    For Each cell In Sheet5.Range("J2:J" & Lastrow)
        If cell.Value = "No" Then
            ComboBox1.AddItem cell.Offset(0, -3).Value
        End If
    Next
For i = 2 To 24
Controls("Combobox" & i).List = ComboBox1.List
Next
End Sub

 

[My Aswer Is This]

I would think you should run this first and then run your script to reload your Comboboxes.

To clear your Comboboxes this should work.

Private Sub CommandButton3_Click()
For i = 1 To 2
Me.Controls("Combobox" & i).Clear
Next
End Sub

This script will not be needed if you use my script in last post number 4

 

[sharky12345]

Thanks!

Will try your suggestions!!