jeudi 9 août 2018

Excel VBA Userform Entering Data into Multiple rows using checkboxes

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")




Aucun commentaire:

Enregistrer un commentaire