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:=”*”, _
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
Download a sample file: