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...
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. Thanks
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. Thanks
Do 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