Forum Discussion

GranSupt's avatar
GranSupt
Copper Contributor
Feb 19, 2024

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...
excel
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Feb 19, 2024

GranSupt 

The "next available row" will be empty. so the "first available column of the first available row" will always be the first column in that row. I'm sure that you didn't mean that. So please provide a better explanation of what you want.

GranSupt's avatar
GranSupt
Copper Contributor
Feb 19, 2024
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.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 19, 2024

    GranSupt 

    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 Sub

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

    • GranSupt's avatar
      GranSupt
      Copper Contributor
      Feb 20, 2024
      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
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 20, 2024

        GranSupt 

        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

Resources