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 blank column, but I cannot find any code to select the next blank column OF the next blank row. I essentially need to combine the 2 variables because in this case not every row will use all columns of the table because the user will not always select all the checkboxes. So what I wish to do is send values to the next blank column of the next blank row.
Details:
Userform called "MaintForm". There are 21 checkboxes with unique text values for 21 different vehicle maintenance procedures. The user selects the desired boxes, and the form (via a commandbutton) sends the values to a worksheet table (sheet "Fleet Maint Records", table "FleetMaintTable"). The table has 30 columns; the first 9 are for some textbox and combo box data from the userform, the next 21 are for each checkbox. I would like to keep the sheet as "compact" as possible by using only the columns needed to print the text associated with each selected checkbox instead of using all the columns in the table with the unlselected checkbox columns being left blank.
This is my code so far: (I am very new to VBA so I am sure this isn't the cleanest code)
Private Sub PMCheckBox1_Click()
Dim LastRow As Long, LastCol As Long
Set ws = Sheets("Fleet Maint Records")
If Me.PMCheckBox1.Value = True Then
LastRow = ws.Range("K" & Rows.Count).End(xlUp).Row + 1
LastCol = ws.Range("K" & Columns.Count).End(xlToRight).Column + 1
'???????? NEED CODE LINE TO FILL NEXT AVAILABLE COLUMN OF THE NEXT AVAILABLE ROW TO PRINT TEXT "ENGINE OIL CHANGED"
End If
End Sub
I have tried numerous combinations of .Range(XXXX,XXXX).Value = "ENGINE OIL CHANGE" to no avail.
Not show here, but I will repeat this sub with the different text value for each of the checkbox commands.
Thanks in advance.
8 Replies
Here's the improved code for your userform, incorporating the best aspects of both responses and addressing potential issues:
VBAPrivate Sub PMCheckBox1_Click() Dim ws As Worksheet Dim lastRow As Long, colOffset As Long Set ws = Sheets("Fleet Maint Records") If Me.PMCheckBox1.Value = True Then ' Find the last row with data in the first column (assuming column A) lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 ' Find the first empty column starting from column 9 colOffset = 9 Do While ws.Cells(lastRow, colOffset).Value <> "" And colOffset <= ws.ListObjects("FleetMaintTable").ListColumns.Count ' Check both emptiness and table boundary colOffset = colOffset + 1 Loop ' Check if a suitable column was found within the table boundaries If colOffset <= ws.ListObjects("FleetMaintTable").ListColumns.Count Then ' Write the checkbox text to the found cell ws.Cells(lastRow, colOffset).Value = "ENGINE OIL CHANGED" Else ' Handle the case where no empty column is found within the table ' (Optional: Display an error message, disable further selections, etc.) MsgBox "No more space available in the table." End If End If End SubExplanation:
Declare variables:
- ws: Worksheet object for "Fleet Maint Records".
- lastRow: Last row with data in the first column (assuming column A).
- colOffset: Offset from column 9 to find the next empty column.
Check checkbox value:
- If PMCheckBox1 is checked (Value = True), proceed.
Find last row:
- Use Range("A" & Rows.Count).End(xlUp).Row to find the last row with data in column A.
- Add 1 to get the next available row.
Find next empty column:
- Initialize colOffset to 9 (starting column for checkbox data).
- Use a Do While loop to iterate through columns:
- Check if the cell value in the current row (lastRow) and current column (colOffset) is empty ("") and if the column offset is still within the table boundaries (colOffset <= ws.ListObjects("FleetMaintTable").ListColumns.Count).
- If not empty or outside the table, increment colOffset to move to the next column.
- The loop stops when an empty cell within the table is found.
Write checkbox text (with error handling):
- Check if a suitable column was found (colOffset <= ws.ListObjects("FleetMaintTable").ListColumns.Count).
- If yes, write the text "ENGINE OIL CHANGED" to the cell.
- If no, handle the case where no empty column is found within the table boundaries. You can add an optional MsgBox to display an error message or take other appropriate actions.
Improvements:
- Combines the clarity and error handling from both responses.
- Uses ListObjects("FleetMaintTable").ListColumns.Count to ensure the search stays within the table boundaries.
- Provides optional error handling for the case where no empty column is found.
- You can adapt the error handling logic to suit your specific needs.
Repeating for other checkboxes:
- Copy and modify this code for each checkbox, replacing "PMCheckBox1" with the corresponding checkbox name and "ENGINE OIL CHANGED" with the appropriate text for each checkbox.
This refined code addresses the potential issues raised in the ratings and provides a more robust and user-friendly solution for populating your table with selected checkbox values.
- Thanks for taking the time to help me. I especially like that you explained each line, that helps a lot for someone like myself just learning VBA. I copied and pasted your code.
At first I was getting a compile - expected end of statement error highlighting the words "Do While" and "Loop". I am getting a syntax error on both the colOffset = 9 Do While ws.Cells(lastRow, colOffset).Value <> "" and colOffset <= and colOffset = colOffset + 1 Loop lines.
When I copied your code it was all one long line so I broke it up the best way I thought. Maybe I messed that up?
(I am using office 365 64 bit version if that makes any difference).
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.
- 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.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.
