Adding info from a Userform into Excel spreadsheet

[pjd0703]

Disclaimer: I am not new to Excel but very new to VBA. I just read the "VBA for Dummies" book on Monday, and I am referencing another advanced VBA book so if my questions seem a bit basic, I hope you will be patient with me.

Question: In Excel 2010, how do I get checkbox information in a userform to enter into separate line items on a spreadsheet, while copying other identifying information from the form at the same time?

Let me explain:
I am creating a Userform to capture detailed customer information and move it into an Excel spreadsheet. This information will then be used in reports, pivot tables, etc.

Three of the fields in the form are text, and I have a frame containing several option buttons (users can only select one). The info gathered from this section of the form is entering into the spreadsheet as expected (thanks to some code I found either on YouTube or another blog) - sort of (that will be a separate question)

I have two other frames containing several check boxes. The end user should be able to select as many of these items as possible. However, only the first checkbox is entering into the spreadsheet.

What I would like to have happen:
I would like each checkbox item selected to enter in a separate line item in the spreadsheet, while also copying the identifying customer information from the other fields.

Here is the code I am using:

Private Sub btnEnterData_Click()
Dim Nextrow As String
'find the next empty row
Nextrow = Application.WorksheetFunction.CountA(Range("A:F"))
Cells(Nextrow, 1) = txtLegalEntityNumber.Text
Cells(Nextrow, 2) = txtLegalEntityName.Text
Cells(Nextrow, 3) = txtNumAcounts.Text

'Identify the Legal Entity Type

If LEButtonOption1 Then Cells(Nextrow, 4) = "Legal Entity Option 1"
If LEButtonOption2 Then Cells(Nextrow, 4) = "Legal Entity Option 2"

'Identify the Markets for this legal entity -
'These should be separate line items for each market and should copy the legal entity name, etc on each line

If Argentina Then Cells(Nextrow, 5) = "Argentina"
'If Australia Then Cells(Nextrow + 1, 5) = "Australia" << This is remarked out because I know it doesn't work but I am playing around to try an figure this out.


Here is what is appearing in the spreadsheet:

Legal entity #	Legal Entity Name	# of Accounts	Legal Entity Type	# of Markets
1	ABC123	20	Option 1	USA

<TBODY>
</TBODY>

Here is what I would like to appear in the spreadsheet:

Legal Entity #	Legal Entity Name	# of Accounts	Legal Entity Type	# of Markets
1	ABC123	20	Option 1	USA
1 (copied)	ABC123 (copied)	20 (copied)	Option 1 (copied)	CAN

<TBODY>
</TBODY>

 

[timfritsch]

This should work, but I haven't tested it:

Public Sub commandbutton1_Click()
  Dim nextRow As Long, ctrl As Control
  
  'find last used row.  row will increment when checkbox match found
  nextRow = Cells(Rows.Count, "A").End(xlUp).Row
  
  For Each ctrl In Me.Controls
    If TypeName(ctrl) = "CheckBox" Then
      If ctrl = True Then
        nextRow = nextRow + 1
        Cells(nextRow, 1) = txtLegalEntityNumber.Text
        Cells(nextRow, 2) = txtLegalEntityName.Text
        Cells(nextRow, 3) = txtNumAcounts.Text

        If LEButtonOption1 Then Cells(Nextrow, 4) = "Legal Entity Option 1"
        If LEButtonOption2 Then Cells(Nextrow, 4) = "Legal Entity Option 2"

        Cells(nextRow, 5) = ctrl.Name
      End If
    Next ctrl
End Sub

Tim

 

[pjd0703]

Hi Tim,

Thanks very much for your reply. It's not working for me. The first failure point is

Public Sub btnEnterData_Click(), which I edited from your line: Public Sub commandbutton1_Click() to match the name given to that command button.

I get the following compile error: "Ambiguous name deteced: btnEnterData_Click"

 

[pjd0703]

Actually - scratch my comment above. I found that I had another entry for the command button below which has since been commented out

Now I get the following complie error when I click the "Enter Data" command button: "Next without For"

 

[timfritsch]

Sorry for that oversight - change code to:

         End If
    End If
  Next ctrl
End Sub

 

[pjd0703]

You are wonderful! Thank you so much!!!

I have one more frame with check boxes to add - hopefully, I will be able to to this myself.

Thanks again, I really appreciate your help.

 

[timfritsch]

You are certainly welcome. Let me know if I can be of assistance.


Tim