Search code examples
excelvbacomboboxdefault-value

Is there a way to toggle between all values in a combobox in a userform with vba?

I have multiple comboboxes through multiple multipage pages. These comboboxes are either imperial or metric. Is there a way to change the default value on all the comboboxes on all multipages?

Private Sub UserForm_Initialize()
    ComboBox1.List = Array("KPa", "psi")
    ComboBox2.List = Array("mm", "inch")
    ComboBox3.List = Array("m", "ft")
    ComboBox4.List = Array("m3", "bbl")
    ComboBox5.List = Array("m^3", "ft^3")
    ComboBox6.List = Array("m", "ft")
    ComboBox7.List = Array("MPa", "psi")
    ComboBox8.List = Array("KPa/m", "psi/ft")
    ComboBox9.List = Array("KPa", "psi")
    ComboBox10.List = Array("m^3", "ft^3")
    ComboBox11.List = Array("m3", "bbl")
    ComboBox12.List = Array("mm", "inch")
    ComboBox13.List = Array("m", "ft")
    ComboBox14.List = Array("KPa", "psi")
    ComboBox15.List = Array("m3/min", "bbl/min")
    ComboBox16.List = Array("m3", "bbl")
    ComboBox17.List = Array("mps", "fps")
    ComboBox18.List = Array("mps", "fps")
    ComboBox19.List = Array("m^3", "ft^3")
    ComboBox20.List = Array("m3/min", "bbl/min")
    ComboBox21.List = Array("m", "ft")
    ComboBox22.List = Array("m/min", "ft/min")
    ComboBox23.List = Array("m^3", "ft^3")
    ComboBox24.List = Array("m^3", "ft^3")
    ComboBox25.List = Array("m3", "bbl")
    ComboBox26.List = Array("m3", "bbl")
    ComboBox27.List = Array("m^3", "ft^3")
    ComboBox28.List = Array("m^3", "ft^3")
    ComboBox29.List = Array("m^3", "ft^3")
End Sub

I have the value property in the macro editor set to metric. I would like to be able to toggle between value 0 and value 1 in all comboboxes at once.

Solution

  • You can loop through all of the controls on your userforms and set with the following

    Dim cBox As Control

For Each cBox In Me.Controls
    If TypeName(cBox) = "ComboBox" Then
        cBox.ListIndex = IIf(prop = "Metric", 0, 1)
    End If
Next cBox

    Replace prop with the variant that decides whether it is Metric or Imperial. I'm assuming all your controls are set with the Metric value in index 0 and Imperial in Index 1