Excel VBA Userform Entering Data into Multiple rows using checkboxes

Question

Hi I need to enter multiple rows of data at once based on the checkboxes that are selected. Currently this only adds 1 row. I think I have to use a loop but I'm not sure how I should implement it. Can anyone help please ?

Userform

The sample output should look something like this:

TC37    | 1
TC37    | 2
TC37    | 4

Current Code:

Dim LastRow As Long, ws As Worksheet
Private Sub CommandButton1_Click()

  Set ws = Sheets("sheet1")

  LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

  ws.Range("A" & LastRow).Value = ComboBox1.Text

  If CheckBox1.Value = True Then
    ws.Range("B" & LastRow).Value = "1"
  End If

  If CheckBox2.Value = True Then
    ws.Range("B" & LastRow).Value = "2"
  End If

  If CheckBox3.Value = True Then
    ws.Range("B" & LastRow).Value = "3"
  End If

  If CheckBox4.Value = True Then
    ws.Range("B" & LastRow).Value = "4"
  End If

End Sub

Private Sub UserForm_Initialize()
  ComboBox1.List = Array("TC37", "TC38", "TC39", "TC40")
End Sub

L42 · Accepted Answer · 2018-08-10 05:22:37Z

2

Since you are getting the last row 1 time, you should dump the data with reference to that one time. Try something like:

Dim chkCnt As Integer
Dim ctl As MSForms.Control, i As Integer, lr As Long
Dim cb As MSForms.CheckBox

With Me
    '/* check if something is checked */
    chkCnt = .CheckBox1.Value + .CheckBox2.Value + .CheckBox3.Value + .CheckBox4.Value
    chkCnt = Abs(chkCnt)
    '/* check if something is checked and selected */
    If chkCnt <> 0 And .ComboBox1 <> "" Then
        ReDim mval(1 To chkCnt, 1 To 2)
        i = 1
        '/* dump values to array */
        For Each ctl In .Controls
            If TypeOf ctl Is MSForms.CheckBox Then
                Set cb = ctl
                If cb Then
                    mval(i, 1) = .ComboBox1.Value
                    mval(i, 2) = cb.Caption
                    i = i + 1
                End If
            End If
        Next
    End If
End With
'/* dump array to sheet */
With Sheets("Sheet1") 'Sheet1
    lr = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & lr).Resize(UBound(mval, 1), 2) = mval
End With

edited Aug 10, 2018 at 5:22

answered Aug 10, 2018 at 3:42

L42

19.8k11 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cua Over a year ago

This is really useful. solved my problem in an instant. Thanks

Fabien Turcotte · Accepted Answer · 2018-08-10 03:33:10Z

Problem is that your variable LastRow does not change. It is set only once at the beginning. So when you try to write the value, it will always write it to the same cell.

If CheckBox1.Value = True Then
    LastRow = ws.Range("B100").end(xlup).Row + 1  
    ws.Range("B" & LastRow).Value = "1"
End If

If CheckBox2.Value = True Then
    LastRow = ws.Range("B100").end(xlup).Row + 1
    ws.Range("B" & LastRow).Value = "2"
End If

If CheckBox3.Value = True Then
    LastRow = ws.Range("B100").end(xlup).Row + 1
    ws.Range("B" & LastRow).Value = "3"
End If

If CheckBox4.Value = True Then
    LastRow = ws.Range("B100").end(xlup).Row + 1
    ws.Range("B" & LastRow).Value = "4"
End If

You could also use and array to store the values and then paste the result of the array in the range.

there are many ways to do this but this one should work. You should always clean the range prior to paste the values.

hope this helps,

Collectives™ on Stack Overflow

Excel VBA Userform Entering Data into Multiple rows using checkboxes

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related