Reload Combobox based on cell values

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,422
Office Version
  1. 2016
Platform
  1. Windows
I've got this which loads a series of Comboboxes on a Userform;

Code:
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;

Code:
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?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I would think you should run this first and then run your script to reload your Comboboxes.

To clear your Comboboxes this should work.
Code:
Private Sub CommandButton3_Click()
For i = 1 To 2
Me.Controls("Combobox" & i).Clear
Next
End Sub
 
Upvote 0
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
Code:
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
 
Upvote 0
I would think you should run this first and then run your script to reload your Comboboxes.

To clear your Comboboxes this should work.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,198
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top