Today we learn how to get data from random check-boxes into our Excel worksheet quickly and easily using VBA.
This form can be uploaded to a website where users can enter their details.
The user would like to create such a form:

Now the data needs to be transferred to the Excel worksheet in this manner:

The first step is to create a user-form with appropriate labels and controls:
Next we write the code for the submit button:
1. We find the last row used in our Excel worksheet using the lastrow code as described in this video.
2. The next empty row is: erow=lastrow+1
3. We get the names automatically into our combo-box using the code described here:
4. We loop through our checkbox controls and get the data from the selected checkboxes into our worksheet.

Watch the video and then study the VBA code:

Watch the video on YouTube.

Here’s the complete VBA code to transfer data from random check-boxes:

Private Sub CommandButton1_Click()
Dim erow As Long
Dim lastRow As Long

lastRow = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
MsgBox “The last row used in the worksheet is: ” & lastRow
erow = lastRow + 1
‘erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Dim intCheckBox As Integer
For intCheckBox = 1 To 5
If Controls(“CheckBox” & intCheckBox) Then
Cells(erow, intCheckBox) = ComboBox1.Value
End If
Next intCheckBox

End Sub

Download a sample file:

Add your name and days