Get Data from Random Check Boxes into Excel Worksheet

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:

User Form with combo-box and check-boxes

User Form with combo-box and check-boxes

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

Get data from random check-boxes

Get data from random check-boxes

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

 

2 thoughts on “Get Data from Random Check Boxes into Excel Worksheet

  1. ramji

    Hi,
    Dinesh

    My name is ramji, I need auto generation email from excel.

    i have series of upcoming expiry dates for documents . How i can set auto generation email from excel.

    Reply
  2. Mohammed Muzammil

    Hello Mr. Dinesh,

    First of all I would like to thank you for the tutorials uploaded on youtube, it has helped me a lot in learning the basics of vba coding. Thanks Again.

    I have been trying to download the file “Get Data from Random Check Boxes into Excel Worksheet” video but for some reason cannot download it. would request you if there is any other place where I could download the file ?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *