Forum Discussion
Userform code to send values to next available column of next available row
I have a userform with several checkbox commands used to populate data into a table in a worksheet. There are plenty of examples on the forum showing how to select the next blank row OR the next blan...
Sorry, I will try to explain better. There are other data from textbox controls being populated in the first 8 columns of a row. These will always have values. My question relates to the subsequent columns (9 thru 29) which are for the 21 checkbox values, which will not always have values. Not all 21 of the checkbox controls will be selected every time by the user, so there will be a blank cell in the column cell associated with the false checkbox value. My question is would it be possible fill the columns (of the row being populated by the current record submission) from left to right (beginning with column 9) with only the selected checkbox (True) values? Rather than having a blank cell for each False checkbox value as well. It's not really a big deal to have the empty cells in the row, but when viewing the table it would be a bit more user friendly if the columns were filled from left to right rather than potentially having several empty cells across the row.
Maybe another way to ask my question is it possible to populate the row moving left to right with only the "true' checkbox values, disregarding any/all false checkbox values? And if so what would the coding be?
Sorry for the confusion, I hope this clarifies my question a little better.
Maybe another way to ask my question is it possible to populate the row moving left to right with only the "true' checkbox values, disregarding any/all false checkbox values? And if so what would the coding be?
Sorry for the confusion, I hope this clarifies my question a little better.
The problem is that you only need to go to a new row the first time, not every time you click a check box. Therefore, I'd do it differently. Remove all the code for the individual check boxes.
Instead, add a command button cmdNew, with the following code in its Click event procedure.
Assuming that the text to be inserted is the caption of the check box:
Private Sub cmdNew_Click()
Dim NewRow As Long
Dim Col As Long
Dim i As Long
NewRow = Range("A:H").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Col = 8
For i = 1 To 21
If Me.Controls("CheckBox" & i).Value = True Then
Col = Col + 1
Cells(NewRow, Col).Value = Me.Controls("CheckBox" & i).Caption
End If
Next i
End SubIf the text to be inserted does not correspond to the check box's caption, you can retrieve it from an array or from a cell range. Let me know if you need help with that.
- Thanks. I did as you suggested and I am getting an error "Could not find the specified object" pointing to the " If Me.Controls("CheckBox" & i).Value = True Then " line.
I suspect this may have something to do with my check box names. The first 12 checkbox names are preceded by "PM" (PMCheckBox1, PMCheckBox2, PMCheckBox3,.......). The reason for this is I made a "Select all" button to select only these first 12 boxes, so I needed a unique name that set them apart from the other 9 boxes.
The check box captions were/are already set to the text that I want displayed in the table.
If you have any suggestions of how to correct the error I would appreciate it. ThanksDo you have other check boxes than those 21? If not, you can use
Private Sub cmdNew_Click() Dim NewRow As Long Dim Col As Long Dim ctl As Control NewRow = Range("A:H").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Col = 8 For Each ctl In Me.Controls If TypeName(ctl) = "CheckBox" Then If ctl.Value = True Then Col = Col + 1 Cells(NewRow, Col).Value = ctl.Caption End If End If Next ctl End Sub- No, no other checkboxes. I will give it a try. Thank you