VBA to insert rows depending on check boxes in userform

[forest1959]

Good Day

Could anyone please help with me with a simple bit of code required to add the details of a userform to the bottom of a list

The userform looks like this --> https://drive.google.com/file/d/1q762SqtZkq6knLa9uChjxCy-9PxuUcop/view?usp=sharing

The List is located in Column KD.

The bit i'm finding tricky is if a checkbox is selected, i need a new line for each tick.

On the example user form, i would need to input 3 lines, all starting with the solution ID, then the heading of the check box, then the duration

looking like this ---> https://drive.google.com/file/d/1Us92-gVudTX9zjZV6T4GTZE1TzXs5xfi/view?usp=sharing

Thank you

regards
Paul

 

[Yongle]

Is Solution ID in a TextBox?
- what is its name?

Are Duration values in TextBoxes ?
- please list all 8 names like this
Upfront Survey = TextBox12
E2E Survey = TextBox13

Where are values being transferred ?
- name of sheet ?
- Solution ID goes in which column ?

 

[forest1959]

Is Solution ID in a TextBox?
- what is its name?

Are Duration values in TextBoxes ?
- please list all 8 names like this
Upfront Survey = TextBox12
E2E Survey = TextBox13

Where are values being transferred ?
- name of sheet ?
- Solution ID goes in which column ?

Solution ID = TextBox1

The durations are in Text Box Order starting at 2 through to 9

and the checkboxes are in order from 1 through to 8

Solution ID = TextBox1
Upfront Survey = TextBox2
E2E Survey = TextBox3
Service Tracing = TextBox4
Garden Survey = TextBox5
Pressure Survey = TextBox6
Schedule of Conditions = TextBox7
GPS As Built = TextBox8
Other Work = TextBox9

The name of the sheet is "Calender"
and the solution ID will go into the next available row in column "KD"

Thank you for your response

 

[Yongle]

Try something like this

Private Sub CommandButton1_Click()
'variables
    Dim CB, Checks, Durations, Itm As String, Dur As String, x As Integer, Cel As Range, ID As String
'known values and arrays
    Set Cel = Sheets("Calender").Cells(Rows.Count, "KD")
    ID = TextBox1.Value
    Checks = Array(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5, CheckBox6, CheckBox7, CheckBox8)
    Durations = Array(Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, Me.TextBox7, Me.TextBox8, Me.TextBox9)
'validity check
    If ID = "" Then Exit Sub
'loop CheckBoxes
    For Each CB In Checks
        Dur = Durations(x).Value
        Itm = CB.Caption
        x = x + 1
        If CB = True And Dur <> "" Then Cel.End(xlUp).Offset(1).Resize(, 3) = Array(ID, Itm, Dur)
    Next CB
End Sub

 

[forest1959]

Absolutely perfect.. Really appreciate your time and effort on this.

Thank you
Regards
Paul