PDA

View Full Version : Multi ComboBox2 and TextBox how to update this whit less code



mhscholl
08-19-2016, 05:13 AM
Hell All,

This is the first time here. I hope some of you can help me. the code is working only can i make it simpler.

i haven a Userform "Settings_Userform" with comboboxes, textboxes and checkboxes. see attached file.


now the code this is very long because it is repeating it self. is there a way to make this easier.


__________________________________________________________________________


Private Sub CommandButton1_oke_Click()


sheet_name = ActiveSheet.Name
Setting_sheet = "settings"

' Eerste kolom
Worksheets(Setting_sheet).Range("B2").Value = Me.ComboBox2_1.ListIndex
Worksheets(Setting_sheet).Range("B3").Value = Me.ComboBox3_1.ListIndex
Worksheets(Setting_sheet).Range("B4").Value = Me.ComboBox4_1.ListIndex
Worksheets(Setting_sheet).Range("B5").Value = Me.ComboBox5_1.ListIndex
Worksheets(Setting_sheet).Range("B6").Value = Me.ComboBox6_1.ListIndex
Worksheets(Setting_sheet).Range("B7").Value = Me.ComboBox7_1.ListIndex
Worksheets(Setting_sheet).Range("B8").Value = Me.ComboBox8_1.ListIndex
Worksheets(Setting_sheet).Range("B9").Value = Me.ComboBox9_1.ListIndex
Worksheets(Setting_sheet).Range("B10").Value = Me.ComboBox10_1.ListIndex
Worksheets(Setting_sheet).Range("B11").Value = Me.ComboBox11_1.ListIndex
Worksheets(Setting_sheet).Range("B12").Value = Me.ComboBox12_1.ListIndex
Worksheets(Setting_sheet).Range("B13").Value = Me.ComboBox13_1.ListIndex
Worksheets(Setting_sheet).Range("B14").Value = Me.ComboBox14_1.ListIndex
Worksheets(Setting_sheet).Range("B15").Value = Me.ComboBox15_1.ListIndex
Worksheets(Setting_sheet).Range("B16").Value = Me.ComboBox16_1.ListIndex
Worksheets(Setting_sheet).Range("B17").Value = Me.ComboBox17_1.ListIndex
Worksheets(Setting_sheet).Range("B18").Value = Me.ComboBox18_1.ListIndex
Worksheets(Setting_sheet).Range("B19").Value = Me.ComboBox19_1.ListIndex
Worksheets(Setting_sheet).Range("B20").Value = Me.ComboBox20_1.ListIndex
Worksheets(Setting_sheet).Range("B21").Value = Me.ComboBox21_1.ListIndex
Worksheets(Setting_sheet).Range("B22").Value = Me.ComboBox22_1.ListIndex
Worksheets(Setting_sheet).Range("B23").Value = Me.ComboBox23_1.ListIndex
Worksheets(Setting_sheet).Range("B24").Value = Me.ComboBox24_1.ListIndex
Worksheets(Setting_sheet).Range("B25").Value = Me.ComboBox25_1.ListIndex

Worksheets(Setting_sheet).Range("B26").Value = Me.ComboBox26_1.ListIndex
Worksheets(Setting_sheet).Range("B27").Value = Me.ComboBox27_1.ListIndex
Worksheets(Setting_sheet).Range("B28").Value = Me.ComboBox28_1.ListIndex
Worksheets(Setting_sheet).Range("B29").Value = Me.ComboBox29_1.ListIndex
Worksheets(Setting_sheet).Range("B30").Value = Me.ComboBox30_1.ListIndex
Worksheets(Setting_sheet).Range("B31").Value = Me.ComboBox31_1.ListIndex




' Tweede kolom


Worksheets(Setting_sheet).Range("C2").Value = Me.ComboBox2_2.ListIndex
Worksheets(Setting_sheet).Range("C3").Value = Me.ComboBox3_2.ListIndex
Worksheets(Setting_sheet).Range("C4").Value = Me.ComboBox4_2.ListIndex
Worksheets(Setting_sheet).Range("C5").Value = Me.ComboBox5_2.ListIndex
Worksheets(Setting_sheet).Range("C6").Value = Me.ComboBox6_2.ListIndex
Worksheets(Setting_sheet).Range("C7").Value = Me.ComboBox7_2.ListIndex
Worksheets(Setting_sheet).Range("C8").Value = Me.ComboBox8_2.ListIndex
Worksheets(Setting_sheet).Range("C9").Value = Me.ComboBox9_2.ListIndex
Worksheets(Setting_sheet).Range("C10").Value = Me.ComboBox10_2.ListIndex
Worksheets(Setting_sheet).Range("C11").Value = Me.ComboBox11_2.ListIndex
Worksheets(Setting_sheet).Range("C12").Value = Me.ComboBox12_2.ListIndex
Worksheets(Setting_sheet).Range("C13").Value = Me.ComboBox13_2.ListIndex
Worksheets(Setting_sheet).Range("C14").Value = Me.ComboBox14_2.ListIndex
Worksheets(Setting_sheet).Range("C15").Value = Me.ComboBox15_2.ListIndex
Worksheets(Setting_sheet).Range("C16").Value = Me.ComboBox16_2.ListIndex
Worksheets(Setting_sheet).Range("C17").Value = Me.ComboBox17_2.ListIndex
Worksheets(Setting_sheet).Range("C18").Value = Me.ComboBox18_2.ListIndex
Worksheets(Setting_sheet).Range("C19").Value = Me.ComboBox19_2.ListIndex
Worksheets(Setting_sheet).Range("C20").Value = Me.ComboBox20_2.ListIndex
Worksheets(Setting_sheet).Range("C21").Value = Me.ComboBox21_2.ListIndex
Worksheets(Setting_sheet).Range("C22").Value = Me.ComboBox22_2.ListIndex
Worksheets(Setting_sheet).Range("C23").Value = Me.ComboBox23_2.ListIndex
Worksheets(Setting_sheet).Range("C24").Value = Me.ComboBox24_2.ListIndex
Worksheets(Setting_sheet).Range("C25").Value = Me.ComboBox25_2.ListIndex

Worksheets(Setting_sheet).Range("C26").Value = Me.ComboBox26_2.ListIndex
Worksheets(Setting_sheet).Range("C27").Value = Me.ComboBox27_2.ListIndex
Worksheets(Setting_sheet).Range("C28").Value = Me.ComboBox28_2.ListIndex
Worksheets(Setting_sheet).Range("C29").Value = Me.ComboBox29_2.ListIndex
Worksheets(Setting_sheet).Range("C30").Value = Me.ComboBox30_2.ListIndex
Worksheets(Setting_sheet).Range("C31").Value = Me.ComboBox31_2.ListIndex




' Derdede kolom
Worksheets(Setting_sheet).Range("D2").Value = Me.ComboBox2_3.ListIndex
Worksheets(Setting_sheet).Range("D3").Value = Me.ComboBox3_3.ListIndex
Worksheets(Setting_sheet).Range("D4").Value = Me.ComboBox4_3.ListIndex
Worksheets(Setting_sheet).Range("D5").Value = Me.ComboBox5_3.ListIndex
Worksheets(Setting_sheet).Range("D6").Value = Me.ComboBox6_3.ListIndex
Worksheets(Setting_sheet).Range("D7").Value = Me.ComboBox7_3.ListIndex
Worksheets(Setting_sheet).Range("D8").Value = Me.ComboBox8_3.ListIndex
Worksheets(Setting_sheet).Range("D9").Value = Me.ComboBox9_3.ListIndex
Worksheets(Setting_sheet).Range("D10").Value = Me.ComboBox10_3.ListIndex
Worksheets(Setting_sheet).Range("D11").Value = Me.ComboBox11_3.ListIndex
Worksheets(Setting_sheet).Range("D12").Value = Me.ComboBox12_3.ListIndex
Worksheets(Setting_sheet).Range("D13").Value = Me.ComboBox13_3.ListIndex
Worksheets(Setting_sheet).Range("D14").Value = Me.ComboBox14_3.ListIndex
Worksheets(Setting_sheet).Range("D15").Value = Me.ComboBox15_3.ListIndex
Worksheets(Setting_sheet).Range("D16").Value = Me.ComboBox16_3.ListIndex
Worksheets(Setting_sheet).Range("D17").Value = Me.ComboBox17_3.ListIndex
Worksheets(Setting_sheet).Range("D18").Value = Me.ComboBox18_3.ListIndex
Worksheets(Setting_sheet).Range("D19").Value = Me.ComboBox19_3.ListIndex
Worksheets(Setting_sheet).Range("D20").Value = Me.ComboBox20_3.ListIndex
Worksheets(Setting_sheet).Range("D21").Value = Me.ComboBox21_3.ListIndex
Worksheets(Setting_sheet).Range("D22").Value = Me.ComboBox22_3.ListIndex
Worksheets(Setting_sheet).Range("D23").Value = Me.ComboBox23_3.ListIndex
Worksheets(Setting_sheet).Range("D24").Value = Me.ComboBox24_3.ListIndex
Worksheets(Setting_sheet).Range("D25").Value = Me.ComboBox25_3.ListIndex

Worksheets(Setting_sheet).Range("D26").Value = Me.ComboBox26_3.ListIndex
Worksheets(Setting_sheet).Range("D27").Value = Me.ComboBox27_3.ListIndex
Worksheets(Setting_sheet).Range("D28").Value = Me.ComboBox28_3.ListIndex
Worksheets(Setting_sheet).Range("D29").Value = Me.ComboBox29_3.ListIndex
Worksheets(Setting_sheet).Range("D30").Value = Me.ComboBox30_3.ListIndex
Worksheets(Setting_sheet).Range("D31").Value = Me.ComboBox31_3.ListIndex




' Derdede kolom


Worksheets(Setting_sheet).Range("E2").Value = Me.ComboBox2_4.ListIndex
Worksheets(Setting_sheet).Range("E3").Value = Me.ComboBox3_4.ListIndex
Worksheets(Setting_sheet).Range("E4").Value = Me.ComboBox4_4.ListIndex
Worksheets(Setting_sheet).Range("E5").Value = Me.ComboBox5_4.ListIndex
Worksheets(Setting_sheet).Range("E6").Value = Me.ComboBox6_4.ListIndex
Worksheets(Setting_sheet).Range("E7").Value = Me.ComboBox7_4.ListIndex
Worksheets(Setting_sheet).Range("E8").Value = Me.ComboBox8_4.ListIndex
Worksheets(Setting_sheet).Range("E9").Value = Me.ComboBox9_4.ListIndex
Worksheets(Setting_sheet).Range("E10").Value = Me.ComboBox10_4.ListIndex
Worksheets(Setting_sheet).Range("E11").Value = Me.ComboBox11_4.ListIndex
Worksheets(Setting_sheet).Range("E12").Value = Me.ComboBox12_4.ListIndex
Worksheets(Setting_sheet).Range("E13").Value = Me.ComboBox13_4.ListIndex
Worksheets(Setting_sheet).Range("E14").Value = Me.ComboBox14_4.ListIndex
Worksheets(Setting_sheet).Range("E15").Value = Me.ComboBox15_4.ListIndex
Worksheets(Setting_sheet).Range("E16").Value = Me.ComboBox16_4.ListIndex
Worksheets(Setting_sheet).Range("E17").Value = Me.ComboBox17_4.ListIndex
Worksheets(Setting_sheet).Range("E18").Value = Me.ComboBox18_4.ListIndex
Worksheets(Setting_sheet).Range("E19").Value = Me.ComboBox19_4.ListIndex
Worksheets(Setting_sheet).Range("E20").Value = Me.ComboBox20_4.ListIndex
Worksheets(Setting_sheet).Range("E21").Value = Me.ComboBox21_4.ListIndex
Worksheets(Setting_sheet).Range("E22").Value = Me.ComboBox22_4.ListIndex
Worksheets(Setting_sheet).Range("E23").Value = Me.ComboBox23_4.ListIndex
Worksheets(Setting_sheet).Range("E24").Value = Me.ComboBox24_4.ListIndex
Worksheets(Setting_sheet).Range("E25").Value = Me.ComboBox25_4.ListIndex

Worksheets(Setting_sheet).Range("E26").Value = Me.ComboBox26_4.ListIndex
Worksheets(Setting_sheet).Range("E27").Value = Me.ComboBox27_4.ListIndex
Worksheets(Setting_sheet).Range("E28").Value = Me.ComboBox28_4.ListIndex
Worksheets(Setting_sheet).Range("E29").Value = Me.ComboBox29_4.ListIndex
Worksheets(Setting_sheet).Range("E30").Value = Me.ComboBox30_4.ListIndex
Worksheets(Setting_sheet).Range("E31").Value = Me.ComboBox31_4.ListIndex



Worksheets(Setting_sheet).Range("G2").Value = Me.TextBox2.Value
Worksheets(Setting_sheet).Range("G3").Value = Me.TextBox3.Value
Worksheets(Setting_sheet).Range("G4").Value = Me.TextBox4.Value
Worksheets(Setting_sheet).Range("G5").Value = Me.TextBox5.Value
Worksheets(Setting_sheet).Range("G6").Value = Me.TextBox6.Value
Worksheets(Setting_sheet).Range("G7").Value = Me.TextBox7.Value
Worksheets(Setting_sheet).Range("G8").Value = Me.TextBox8.Value
Worksheets(Setting_sheet).Range("G9").Value = Me.TextBox9.Value
Worksheets(Setting_sheet).Range("G10").Value = Me.TextBox10.Value
Worksheets(Setting_sheet).Range("G11").Value = Me.TextBox11.Value
Worksheets(Setting_sheet).Range("G12").Value = Me.TextBox12.Value
Worksheets(Setting_sheet).Range("G13").Value = Me.TextBox13.Value
Worksheets(Setting_sheet).Range("G14").Value = Me.TextBox14.Value
Worksheets(Setting_sheet).Range("G15").Value = Me.TextBox15.Value
Worksheets(Setting_sheet).Range("G16").Value = Me.TextBox16.Value
Worksheets(Setting_sheet).Range("G17").Value = Me.TextBox17.Value
Worksheets(Setting_sheet).Range("G18").Value = Me.TextBox18.Value
Worksheets(Setting_sheet).Range("G19").Value = Me.TextBox19.Value
Worksheets(Setting_sheet).Range("G20").Value = Me.TextBox20.Value
Worksheets(Setting_sheet).Range("G21").Value = Me.TextBox21.Value
Worksheets(Setting_sheet).Range("G22").Value = Me.TextBox22.Value
Worksheets(Setting_sheet).Range("G23").Value = Me.TextBox23.Value
Worksheets(Setting_sheet).Range("G24").Value = Me.TextBox24.Value
Worksheets(Setting_sheet).Range("G25").Value = Me.TextBox25.Value

Worksheets(Setting_sheet).Range("G26").Value = Me.TextBox26.Value
Worksheets(Setting_sheet).Range("G27").Value = Me.TextBox27.Value
Worksheets(Setting_sheet).Range("G28").Value = Me.TextBox28.Value
Worksheets(Setting_sheet).Range("G29").Value = Me.TextBox29.Value
Worksheets(Setting_sheet).Range("G30").Value = Me.TextBox30.Value
Worksheets(Setting_sheet).Range("G31").Value = Me.TextBox31.Value

Worksheets(Setting_sheet).Range("H2").Value = Me.CheckBox2.Value
Worksheets(Setting_sheet).Range("H3").Value = Me.CheckBox3.Value
Worksheets(Setting_sheet).Range("H4").Value = Me.CheckBox4.Value
Worksheets(Setting_sheet).Range("H5").Value = Me.CheckBox5.Value
Worksheets(Setting_sheet).Range("H6").Value = Me.CheckBox6.Value
Worksheets(Setting_sheet).Range("H7").Value = Me.CheckBox7.Value
Worksheets(Setting_sheet).Range("H8").Value = Me.CheckBox8.Value
Worksheets(Setting_sheet).Range("H9").Value = Me.CheckBox9.Value
Worksheets(Setting_sheet).Range("H10").Value = Me.CheckBox10.Value
Worksheets(Setting_sheet).Range("H11").Value = Me.CheckBox11.Value
Worksheets(Setting_sheet).Range("H12").Value = Me.CheckBox12.Value
Worksheets(Setting_sheet).Range("H13").Value = Me.CheckBox13.Value
Worksheets(Setting_sheet).Range("H14").Value = Me.CheckBox14.Value
Worksheets(Setting_sheet).Range("H15").Value = Me.CheckBox15.Value
Worksheets(Setting_sheet).Range("H16").Value = Me.CheckBox16.Value
Worksheets(Setting_sheet).Range("H17").Value = Me.CheckBox17.Value
Worksheets(Setting_sheet).Range("H18").Value = Me.CheckBox18.Value
Worksheets(Setting_sheet).Range("H19").Value = Me.CheckBox19.Value
Worksheets(Setting_sheet).Range("H20").Value = Me.CheckBox20.Value
Worksheets(Setting_sheet).Range("H21").Value = Me.CheckBox21.Value
Worksheets(Setting_sheet).Range("H22").Value = Me.CheckBox22.Value
Worksheets(Setting_sheet).Range("H23").Value = Me.CheckBox23.Value
Worksheets(Setting_sheet).Range("H24").Value = Me.CheckBox24.Value
Worksheets(Setting_sheet).Range("H25").Value = Me.CheckBox25.Value

Worksheets(Setting_sheet).Range("H26").Value = Me.CheckBox26.Value
Worksheets(Setting_sheet).Range("H27").Value = Me.CheckBox27.Value
Worksheets(Setting_sheet).Range("H28").Value = Me.CheckBox28.Value
Worksheets(Setting_sheet).Range("H29").Value = Me.CheckBox29.Value
Worksheets(Setting_sheet).Range("H30").Value = Me.CheckBox30.Value
Worksheets(Setting_sheet).Range("H31").Value = Me.CheckBox31.Value

With Sheets("settings")
.Visible = xlVeryHidden
End With



Unload Settings_Userform


Call Change_All_Fonts
Sheets(sheet_name).Select
End Sub

___________________________________________________________________________ __________________


Private Sub CommandButton2_Cancel_Click()


With Sheets("settings")
.Visible = xlVeryHidden
End With

Unload Settings_Userform

End Sub


___________________________________________________________________________ __________________


Private Sub UserForm_Initialize()


Setting_sheet = "settings"


With Sheets(Setting_sheet)
.Visible = True
End With

With Settings_Userform
' Naam kolom
.Label2 = Worksheets(Setting_sheet).Range("A2").Value
.Label3 = Worksheets(Setting_sheet).Range("A3").Value
.Label4 = Worksheets(Setting_sheet).Range("A4").Value
.Label5 = Worksheets(Setting_sheet).Range("A5").Value
.Label6 = Worksheets(Setting_sheet).Range("A6").Value
.Label7 = Worksheets(Setting_sheet).Range("A7").Value
.Label8 = Worksheets(Setting_sheet).Range("A8").Value
.Label9 = Worksheets(Setting_sheet).Range("A9").Value
.Label10 = Worksheets(Setting_sheet).Range("A10").Value
.Label11 = Worksheets(Setting_sheet).Range("A11").Value
.Label12 = Worksheets(Setting_sheet).Range("A12").Value
.Label13 = Worksheets(Setting_sheet).Range("A13").Value
.Label14 = Worksheets(Setting_sheet).Range("A14").Value
.Label15 = Worksheets(Setting_sheet).Range("A15").Value
.Label16 = Worksheets(Setting_sheet).Range("A16").Value
.Label17 = Worksheets(Setting_sheet).Range("A17").Value
.Label18 = Worksheets(Setting_sheet).Range("A18").Value
.Label19 = Worksheets(Setting_sheet).Range("A19").Value
.Label20 = Worksheets(Setting_sheet).Range("A20").Value
.Label21 = Worksheets(Setting_sheet).Range("A21").Value
.Label22 = Worksheets(Setting_sheet).Range("A22").Value
.Label23 = Worksheets(Setting_sheet).Range("A23").Value
.Label24 = Worksheets(Setting_sheet).Range("A24").Value
.Label25 = Worksheets(Setting_sheet).Range("A25").Value
.Label26 = Worksheets(Setting_sheet).Range("A26").Value
.Label27 = Worksheets(Setting_sheet).Range("A27").Value
.Label28 = Worksheets(Setting_sheet).Range("A28").Value
.Label29 = Worksheets(Setting_sheet).Range("A29").Value
.Label30 = Worksheets(Setting_sheet).Range("A30").Value
.Label31 = Worksheets(Setting_sheet).Range("A31").Value

' Eerste kolom



.ComboBox2_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox3_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox4_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox5_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox6_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox7_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox8_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox9_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox10_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox11_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox12_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox13_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox14_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox15_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox16_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox17_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox18_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox19_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox20_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox21_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox22_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox23_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox24_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox25_1.List = Worksheets(Setting_sheet).Range("Combobox").Value


.ComboBox2_1.ListIndex = Worksheets(Setting_sheet).Range("B2").Value
.ComboBox3_1.ListIndex = Worksheets(Setting_sheet).Range("B3").Value
.ComboBox4_1.ListIndex = Worksheets(Setting_sheet).Range("B4").Value
.ComboBox5_1.ListIndex = Worksheets(Setting_sheet).Range("B5").Value
.ComboBox6_1.ListIndex = Worksheets(Setting_sheet).Range("B6").Value
.ComboBox7_1.ListIndex = Worksheets(Setting_sheet).Range("B7").Value
.ComboBox8_1.ListIndex = Worksheets(Setting_sheet).Range("B8").Value
.ComboBox9_1.ListIndex = Worksheets(Setting_sheet).Range("B9").Value
.ComboBox10_1.ListIndex = Worksheets(Setting_sheet).Range("B10").Value
.ComboBox11_1.ListIndex = Worksheets(Setting_sheet).Range("B11").Value
.ComboBox12_1.ListIndex = Worksheets(Setting_sheet).Range("B12").Value
.ComboBox13_1.ListIndex = Worksheets(Setting_sheet).Range("B13").Value
.ComboBox14_1.ListIndex = Worksheets(Setting_sheet).Range("B14").Value
.ComboBox15_1.ListIndex = Worksheets(Setting_sheet).Range("B15").Value
.ComboBox16_1.ListIndex = Worksheets(Setting_sheet).Range("B16").Value
.ComboBox17_1.ListIndex = Worksheets(Setting_sheet).Range("B17").Value
.ComboBox18_1.ListIndex = Worksheets(Setting_sheet).Range("B18").Value
.ComboBox19_1.ListIndex = Worksheets(Setting_sheet).Range("B19").Value
.ComboBox20_1.ListIndex = Worksheets(Setting_sheet).Range("B20").Value
.ComboBox21_1.ListIndex = Worksheets(Setting_sheet).Range("B21").Value
.ComboBox22_1.ListIndex = Worksheets(Setting_sheet).Range("B22").Value
.ComboBox23_1.ListIndex = Worksheets(Setting_sheet).Range("B23").Value
.ComboBox24_1.ListIndex = Worksheets(Setting_sheet).Range("B24").Value
.ComboBox25_1.ListIndex = Worksheets(Setting_sheet).Range("B25").Value


' Tweede kolom
.ComboBox2_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox3_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox4_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox5_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox6_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox7_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox8_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox9_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox10_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox11_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox12_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox13_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox14_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox15_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox16_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox17_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox18_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox19_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox20_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox21_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox22_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox23_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox24_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox25_2.List = Worksheets(Setting_sheet).Range("Combobox").Value


.ComboBox2_2.ListIndex = Worksheets(Setting_sheet).Range("C2").Value
.ComboBox3_2.ListIndex = Worksheets(Setting_sheet).Range("C3").Value
.ComboBox4_2.ListIndex = Worksheets(Setting_sheet).Range("C4").Value
.ComboBox5_2.ListIndex = Worksheets(Setting_sheet).Range("C5").Value
.ComboBox6_2.ListIndex = Worksheets(Setting_sheet).Range("C6").Value
.ComboBox7_2.ListIndex = Worksheets(Setting_sheet).Range("C7").Value
.ComboBox8_2.ListIndex = Worksheets(Setting_sheet).Range("C8").Value
.ComboBox9_2.ListIndex = Worksheets(Setting_sheet).Range("C9").Value
.ComboBox10_2.ListIndex = Worksheets(Setting_sheet).Range("C10").Value
.ComboBox11_2.ListIndex = Worksheets(Setting_sheet).Range("C11").Value
.ComboBox12_2.ListIndex = Worksheets(Setting_sheet).Range("C12").Value
.ComboBox13_2.ListIndex = Worksheets(Setting_sheet).Range("C13").Value
.ComboBox14_2.ListIndex = Worksheets(Setting_sheet).Range("C14").Value
.ComboBox15_2.ListIndex = Worksheets(Setting_sheet).Range("C15").Value
.ComboBox16_2.ListIndex = Worksheets(Setting_sheet).Range("C16").Value
.ComboBox17_2.ListIndex = Worksheets(Setting_sheet).Range("C17").Value
.ComboBox18_2.ListIndex = Worksheets(Setting_sheet).Range("C18").Value
.ComboBox19_2.ListIndex = Worksheets(Setting_sheet).Range("C19").Value
.ComboBox20_2.ListIndex = Worksheets(Setting_sheet).Range("C20").Value
.ComboBox21_2.ListIndex = Worksheets(Setting_sheet).Range("C21").Value
.ComboBox22_2.ListIndex = Worksheets(Setting_sheet).Range("C22").Value
.ComboBox23_2.ListIndex = Worksheets(Setting_sheet).Range("C23").Value
.ComboBox24_2.ListIndex = Worksheets(Setting_sheet).Range("C24").Value
.ComboBox25_2.ListIndex = Worksheets(Setting_sheet).Range("C25").Value






' Derde kolom
.ComboBox2_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox3_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox4_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox5_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox6_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox7_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox8_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox9_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox10_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox11_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox12_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox13_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox14_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox15_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox16_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox17_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox18_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox19_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox20_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox21_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox22_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox23_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox24_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox25_3.List = Worksheets(Setting_sheet).Range("Combobox").Value


.ComboBox2_3.ListIndex = Worksheets(Setting_sheet).Range("D2").Value
.ComboBox3_3.ListIndex = Worksheets(Setting_sheet).Range("D3").Value
.ComboBox4_3.ListIndex = Worksheets(Setting_sheet).Range("D4").Value
.ComboBox5_3.ListIndex = Worksheets(Setting_sheet).Range("D5").Value
.ComboBox6_3.ListIndex = Worksheets(Setting_sheet).Range("D6").Value
.ComboBox7_3.ListIndex = Worksheets(Setting_sheet).Range("D7").Value
.ComboBox8_3.ListIndex = Worksheets(Setting_sheet).Range("D8").Value
.ComboBox9_3.ListIndex = Worksheets(Setting_sheet).Range("D9").Value
.ComboBox10_3.ListIndex = Worksheets(Setting_sheet).Range("D10").Value
.ComboBox11_3.ListIndex = Worksheets(Setting_sheet).Range("D11").Value
.ComboBox12_3.ListIndex = Worksheets(Setting_sheet).Range("D12").Value
.ComboBox13_3.ListIndex = Worksheets(Setting_sheet).Range("D13").Value
.ComboBox14_3.ListIndex = Worksheets(Setting_sheet).Range("D14").Value
.ComboBox15_3.ListIndex = Worksheets(Setting_sheet).Range("D15").Value
.ComboBox16_3.ListIndex = Worksheets(Setting_sheet).Range("D16").Value
.ComboBox17_3.ListIndex = Worksheets(Setting_sheet).Range("D17").Value
.ComboBox18_3.ListIndex = Worksheets(Setting_sheet).Range("D18").Value
.ComboBox19_3.ListIndex = Worksheets(Setting_sheet).Range("D19").Value
.ComboBox20_3.ListIndex = Worksheets(Setting_sheet).Range("D20").Value
.ComboBox21_3.ListIndex = Worksheets(Setting_sheet).Range("D21").Value
.ComboBox22_3.ListIndex = Worksheets(Setting_sheet).Range("D22").Value
.ComboBox23_3.ListIndex = Worksheets(Setting_sheet).Range("D23").Value
.ComboBox24_3.ListIndex = Worksheets(Setting_sheet).Range("D24").Value
.ComboBox25_3.ListIndex = Worksheets(Setting_sheet).Range("D25").Value




' Vierde kolom
.ComboBox2_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox3_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox4_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox5_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox6_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox7_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox8_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox9_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox10_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox11_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox12_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox13_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox14_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox15_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox16_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox17_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox18_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox19_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox20_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox21_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox22_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox23_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox24_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
.ComboBox25_4.List = Worksheets(Setting_sheet).Range("Combobox").Value





.ComboBox2_4.ListIndex = Worksheets(Setting_sheet).Range("E2").Value
.ComboBox3_4.ListIndex = Worksheets(Setting_sheet).Range("E3").Value
.ComboBox4_4.ListIndex = Worksheets(Setting_sheet).Range("E4").Value
.ComboBox5_4.ListIndex = Worksheets(Setting_sheet).Range("E5").Value
.ComboBox6_4.ListIndex = Worksheets(Setting_sheet).Range("E6").Value
.ComboBox7_4.ListIndex = Worksheets(Setting_sheet).Range("E7").Value
.ComboBox8_4.ListIndex = Worksheets(Setting_sheet).Range("E8").Value
.ComboBox9_4.ListIndex = Worksheets(Setting_sheet).Range("E9").Value
.ComboBox10_4.ListIndex = Worksheets(Setting_sheet).Range("E10").Value
.ComboBox11_4.ListIndex = Worksheets(Setting_sheet).Range("E11").Value
.ComboBox12_4.ListIndex = Worksheets(Setting_sheet).Range("E12").Value
.ComboBox13_4.ListIndex = Worksheets(Setting_sheet).Range("E13").Value
.ComboBox14_4.ListIndex = Worksheets(Setting_sheet).Range("E14").Value
.ComboBox15_4.ListIndex = Worksheets(Setting_sheet).Range("E15").Value
.ComboBox16_4.ListIndex = Worksheets(Setting_sheet).Range("E16").Value
.ComboBox17_4.ListIndex = Worksheets(Setting_sheet).Range("E17").Value
.ComboBox18_4.ListIndex = Worksheets(Setting_sheet).Range("E18").Value
.ComboBox19_4.ListIndex = Worksheets(Setting_sheet).Range("E19").Value
.ComboBox20_4.ListIndex = Worksheets(Setting_sheet).Range("E20").Value
.ComboBox21_4.ListIndex = Worksheets(Setting_sheet).Range("E21").Value
.ComboBox22_4.ListIndex = Worksheets(Setting_sheet).Range("E22").Value
.ComboBox23_4.ListIndex = Worksheets(Setting_sheet).Range("E23").Value
.ComboBox24_4.ListIndex = Worksheets(Setting_sheet).Range("E24").Value
.ComboBox25_4.ListIndex = Worksheets(Setting_sheet).Range("E25").Value


.TextBox2.Value = Worksheets(Setting_sheet).Range("G2").Value
.TextBox3.Value = Worksheets(Setting_sheet).Range("G3").Value
.TextBox4.Value = Worksheets(Setting_sheet).Range("G4").Value
.TextBox5.Value = Worksheets(Setting_sheet).Range("G5").Value
.TextBox6.Value = Worksheets(Setting_sheet).Range("G6").Value
.TextBox7.Value = Worksheets(Setting_sheet).Range("G7").Value
.TextBox8.Value = Worksheets(Setting_sheet).Range("G8").Value
.TextBox9.Value = Worksheets(Setting_sheet).Range("G9").Value
.TextBox10.Value = Worksheets(Setting_sheet).Range("G10").Value
.TextBox11.Value = Worksheets(Setting_sheet).Range("G11").Value
.TextBox12.Value = Worksheets(Setting_sheet).Range("G12").Value
.TextBox13.Value = Worksheets(Setting_sheet).Range("G13").Value
.TextBox14.Value = Worksheets(Setting_sheet).Range("G14").Value
.TextBox15.Value = Worksheets(Setting_sheet).Range("G15").Value
.TextBox16.Value = Worksheets(Setting_sheet).Range("G16").Value
.TextBox17.Value = Worksheets(Setting_sheet).Range("G17").Value
.TextBox18.Value = Worksheets(Setting_sheet).Range("G18").Value
.TextBox19.Value = Worksheets(Setting_sheet).Range("G19").Value
.TextBox20.Value = Worksheets(Setting_sheet).Range("G20").Value
.TextBox21.Value = Worksheets(Setting_sheet).Range("G21").Value
.TextBox22.Value = Worksheets(Setting_sheet).Range("G22").Value
.TextBox23.Value = Worksheets(Setting_sheet).Range("G23").Value
.TextBox24.Value = Worksheets(Setting_sheet).Range("G24").Value
.TextBox25.Value = Worksheets(Setting_sheet).Range("G25").Value

.CheckBox2.Value = Worksheets(Setting_sheet).Range("H2").Value
.CheckBox3.Value = Worksheets(Setting_sheet).Range("H3").Value
.CheckBox4.Value = Worksheets(Setting_sheet).Range("H4").Value
.CheckBox5.Value = Worksheets(Setting_sheet).Range("H5").Value
.CheckBox6.Value = Worksheets(Setting_sheet).Range("H6").Value
.CheckBox7.Value = Worksheets(Setting_sheet).Range("H7").Value
.CheckBox8.Value = Worksheets(Setting_sheet).Range("H8").Value
.CheckBox9.Value = Worksheets(Setting_sheet).Range("H9").Value
.CheckBox19.Value = Worksheets(Setting_sheet).Range("H10").Value
.CheckBox11.Value = Worksheets(Setting_sheet).Range("H11").Value
.CheckBox12.Value = Worksheets(Setting_sheet).Range("H12").Value
.CheckBox13.Value = Worksheets(Setting_sheet).Range("H13").Value
.CheckBox14.Value = Worksheets(Setting_sheet).Range("H14").Value
.CheckBox15.Value = Worksheets(Setting_sheet).Range("H15").Value
.CheckBox16.Value = Worksheets(Setting_sheet).Range("H16").Value
.CheckBox17.Value = Worksheets(Setting_sheet).Range("H17").Value
.CheckBox18.Value = Worksheets(Setting_sheet).Range("H18").Value
.CheckBox19.Value = Worksheets(Setting_sheet).Range("H19").Value
.CheckBox20.Value = Worksheets(Setting_sheet).Range("H20").Value
.CheckBox21.Value = Worksheets(Setting_sheet).Range("H21").Value
.CheckBox22.Value = Worksheets(Setting_sheet).Range("H22").Value
.CheckBox23.Value = Worksheets(Setting_sheet).Range("H23").Value
.CheckBox24.Value = Worksheets(Setting_sheet).Range("H24").Value
.CheckBox25.Value = Worksheets(Setting_sheet).Range("H25").Value
End With


'For future use

If Settings_Userform.Label26 = Empty Then
Settings_Userform.ComboBox26_1.Visible = False
Settings_Userform.ComboBox26_2.Visible = False
Settings_Userform.ComboBox26_3.Visible = False
Settings_Userform.ComboBox26_4.Visible = False
Settings_Userform.TextBox26.Visible = False
Settings_Userform.CheckBox26.Visible = False
Else
Settings_Userform.ComboBox26_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox26_1.ListIndex = Worksheets(Setting_sheet).Range("B26").Value

Settings_Userform.ComboBox26_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox26_2.ListIndex = Worksheets(Setting_sheet).Range("C26").Value

Settings_Userform.ComboBox26_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox26_3.ListIndex = Worksheets(Setting_sheet).Range("D26").Value

Settings_Userform.ComboBox26_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox26_4.ListIndex = Worksheets(Setting_sheet).Range("E26").Value

Settings_Userform.TextBox26.Value = Worksheets(Setting_sheet).Range("G26").Value
Settings_Userform.CheckBox26.Value = Worksheets(Setting_sheet).Range("H26").Value
End If

If Settings_Userform.Label27 = Empty Then
Settings_Userform.ComboBox27_1.Visible = False
Settings_Userform.ComboBox27_2.Visible = False
Settings_Userform.ComboBox27_3.Visible = False
Settings_Userform.ComboBox27_4.Visible = False
Settings_Userform.TextBox27.Visible = False
Settings_Userform.CheckBox27.Visible = False
Else


Settings_Userform.ComboBox27_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox27_1.ListIndex = Worksheets(Setting_sheet).Range("B27").Value

Settings_Userform.ComboBox27_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox27_2.ListIndex = Worksheets(Setting_sheet).Range("C27").Value

Settings_Userform.ComboBox27_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox27_3.ListIndex = Worksheets(Setting_sheet).Range("D27").Value

Settings_Userform.ComboBox27_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox27_4.ListIndex = Worksheets(Setting_sheet).Range("E27").Value

Settings_Userform.TextBox27.Value = Worksheets(Setting_sheet).Range("G27").Value
Settings_Userform.CheckBox27.Value = Worksheets(Setting_sheet).Range("H27").Value
End If

If Settings_Userform.Label28 = Empty Then
Settings_Userform.ComboBox28_1.Visible = False
Settings_Userform.ComboBox28_2.Visible = False
Settings_Userform.ComboBox28_3.Visible = False
Settings_Userform.ComboBox28_4.Visible = False
Settings_Userform.TextBox28.Visible = False
Settings_Userform.CheckBox28.Visible = False
Else

Settings_Userform.ComboBox28_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox28_1.ListIndex = Worksheets(Setting_sheet).Range("B28").Value

Settings_Userform.ComboBox28_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox28_2.ListIndex = Worksheets(Setting_sheet).Range("C28").Value

Settings_Userform.ComboBox28_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox28_3.ListIndex = Worksheets(Setting_sheet).Range("D28").Value

Settings_Userform.ComboBox28_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox28_4.ListIndex = Worksheets(Setting_sheet).Range("E28").Value

Settings_Userform.TextBox28.Value = Worksheets(Setting_sheet).Range("G28").Value
Settings_Userform.CheckBox28.Value = Worksheets(Setting_sheet).Range("H28").Value
End If

If Settings_Userform.Label29 = Empty Then
Settings_Userform.ComboBox29_1.Visible = False
Settings_Userform.ComboBox29_2.Visible = False
Settings_Userform.ComboBox29_3.Visible = False
Settings_Userform.ComboBox29_4.Visible = False
Settings_Userform.TextBox29.Visible = False
Settings_Userform.CheckBox29.Visible = False
Else
Settings_Userform.ComboBox29_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox28_1.ListIndex = Worksheets(Setting_sheet).Range("B28").Value

Settings_Userform.ComboBox29_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox28_2.ListIndex = Worksheets(Setting_sheet).Range("C28").Value

Settings_Userform.ComboBox29_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox28_3.ListIndex = Worksheets(Setting_sheet).Range("D28").Value

Settings_Userform.ComboBox29_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox28_4.ListIndex = Worksheets(Setting_sheet).Range("E28").Value

Settings_Userform.TextBox29.Value = Worksheets(Setting_sheet).Range("G29").Value
Settings_Userform.CheckBox29.Value = Worksheets(Setting_sheet).Range("H29").Value
End If

If Settings_Userform.Label30 = Empty Then
Settings_Userform.ComboBox30_1.Visible = False
Settings_Userform.ComboBox30_2.Visible = False
Settings_Userform.ComboBox30_3.Visible = False
Settings_Userform.ComboBox30_4.Visible = False
Settings_Userform.TextBox30.Visible = False
Settings_Userform.CheckBox30.Visible = False
Else
Settings_Userform.ComboBox30_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox30_1.ListIndex = Worksheets(Setting_sheet).Range("B30").Value

Settings_Userform.ComboBox30_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox30_2.ListIndex = Worksheets(Setting_sheet).Range("B30").Value

Settings_Userform.ComboBox30_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox30_3.ListIndex = Worksheets(Setting_sheet).Range("B30").Value

Settings_Userform.ComboBox30_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox30_4.ListIndex = Worksheets(Setting_sheet).Range("B30").Value

Settings_Userform.TextBox30.Value = Worksheets(Setting_sheet).Range("G30").Value
Settings_Userform.CheckBox30.Value = Worksheets(Setting_sheet).Range("H30").Value
End If

If Settings_Userform.Label31 = Empty Then
Settings_Userform.ComboBox31_1.Visible = False
Settings_Userform.ComboBox31_2.Visible = False
Settings_Userform.ComboBox31_3.Visible = False
Settings_Userform.ComboBox31_4.Visible = False
Settings_Userform.TextBox31.Visible = False
Settings_Userform.CheckBox31.Visible = False
Else

Settings_Userform.ComboBox31_1.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox31_1.ListIndex = Worksheets(Setting_sheet).Range("B31").Value

Settings_Userform.ComboBox31_2.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox31_2.ListIndex = Worksheets(Setting_sheet).Range("C31").Value

Settings_Userform.ComboBox31_3.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox31_3.ListIndex = Worksheets(Setting_sheet).Range("D31").Value

Settings_Userform.ComboBox31_4.List = Worksheets(Setting_sheet).Range("Combobox").Value
Settings_Userform.ComboBox31_4.ListIndex = Worksheets(Setting_sheet).Range("E31").Value

Settings_Userform.TextBox31.Value = Worksheets(Setting_sheet).Range("G31").Value
Settings_Userform.CheckBox31.Value = Worksheets(Setting_sheet).Range("H31").Value
End If





End Sub

mikerickson
08-19-2016, 06:07 AM
Welcome to the forum. Please remember to use code tags.

You can loop through the names of user form controls with code like



Dim i as Long, j As Long

For i = 2 to 31
For j = 1 to 4
Worksheets(Setting_sheet).Range("B2").Cells(i, j)Value = Me.Controls("ComboBox" & i & "_" & j).ListIndex
Next j
Next i

For i = 2 to 30
Worksheets(Setting_sheet).Range("G2").Cells(i, 1).Value = Me.Controls("TextBox" & i).Value
Next i

mhscholl
08-19-2016, 10:42 AM
Thanks I will try it on Monday

SamT
08-19-2016, 11:34 AM
ComboBoxes don't have a ListIndex. They do have a TopIndex, but it is just a number. I assume you want the Selected Item to be stored in the Cell



With Worksheets("Setting_sheet")
With .Range("B:B")
For r = 2 to 31
.Cells(r) = Me.Controls("ComboBox" & r & "_1").Vlaue
Next r
End With 'B:B

With .Range("C:C")
For r = 2 to 31
.Cells(r) = Me.Controls("ComboBox" & r & "_2").Value
Next r
End With 'C:C

With .Range("D:D")
For r = 2 to 31
.Cells(r) = Me.Controls("ComboBox" & r & "_3").Value
Next r
End With 'D:D

With .Range("E:E")
For r = 2 to 31
.Cells(r) = Me.Controls("ComboBox" & r & "_4").Value
Next r
End With 'E:E

With .Range("G:G")
For r = 2 to 31
.Cells(r) = Me.Controls("TextBox" & r).Value
Next r
End With 'G:G

With .Range("H:H")
For r = 2 to 31
.Cells(r) = Me.Controls("CheckBox" & r).Value
Next r
End With 'H:H
End With' Sheet


Shorter version

With Worksheets("Setting_sheet")
For r = 2 to 31
.Cells(r, "B") = Me.Controls("ComboBox" & r & "_1").Value
.Cells(r, "C") = Me.Controls("ComboBox" & r & "_2").Value
.Cells(r, "D" = Me.Controls("ComboBox" & r & "_3").Value
.Cells(r, "E") = Me.Controls("ComboBox" & r & "_4").Value
' "F" ???
.Cells(r, "G") = Me.Controls("TextBox" & r).Value
.Cells(r, "H") = Me.Controls("CheckBox" & r).Value
Next r
End With

mikerickson
08-19-2016, 11:58 AM
ComboBoxes don't have a ListIndex. They do have a TopIndex, but it is just a number...]
Comboboxes do have a .ListIndex property.
Typically, ComboBox1.Value = ComboBox1.List(ComboBox1.ListIndex)

It is also just a number.

SamT
08-19-2016, 05:12 PM
You're right. My bad. That's what I get for only looking at the Properties Windows.

Oh man, I do miss my Desktop. DuckDuckGo is OK, but it is a pain.