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

 

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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

  1. 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.

  2. 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 ?

Leave a Reply

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