VBA to insert rows depending on check boxes in userform

forest1959

New Member
Joined
Mar 16, 2010
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
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


view

view

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
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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 ?
 
Upvote 0
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
 
Upvote 0
Try something like this

Code:
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
 
Upvote 0
Absolutely perfect.. Really appreciate your time and effort on this.

Thank you
Regards
Paul
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

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