Adding info from a Userform into Excel spreadsheet

pjd0703

New Member
Joined
Apr 18, 2014
Messages
5
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 AccountsLegal Entity Type# of Markets
1ABC12320Option 1USA

<TBODY>
</TBODY>


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

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

<TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

timfritsch

Active Member
Joined
Jun 10, 2004
Messages
343
This should work, but I haven't tested it:

Code:
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

New Member
Joined
Apr 18, 2014
Messages
5
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

New Member
Joined
Apr 18, 2014
Messages
5
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

Active Member
Joined
Jun 10, 2004
Messages
343

ADVERTISEMENT

Sorry for that oversight - change code to:
Code:
         End If
    End If
  Next ctrl
End Sub
 

pjd0703

New Member
Joined
Apr 18, 2014
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,252
Messages
5,595,086
Members
413,967
Latest member
Samlan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top