Tom’s Tutorials For Excel: Populating TextBoxes From Last Row Upwards
When you have a list and you want to populate userform TextBoxes for, say, the last 10 records of the items in that list, here’s how.
In the above picture, a company lists its employees in ascending oder of their hire date. Suppose you want to look at the last 10 records in that list, as shown in the next picture, realizing that the list changes in size when new employees are hired and existing employees leave the company.
The trick is to name your userform’s TextBoxes in a friendly sequence that will correspond logically to the data you are importing into the userform. In this example, the TextBoxes are arranged in five columns of 10 rows to match the spreadsheet list’s design.
So, the first (topmost leftmost) TextBox is named TextBox1, the TextBox to the right of that is named TextBox2, and so on to TextBox5. The next row of TextBoxes on the userform are named from TextBox6 to TextBox10, and so on in that naming sequence to TextBox50.
The below Initialize event procedure goes into the userform’s module and automatically loads the last 10 records when the userform is called.
Private Sub UserForm_Initialize()
'Declare variables.
Dim LastRow As Long
Dim xRow As Long, xColumn As Long
Dim lngCounter As Long
'Define the last used row among columns A:E.
LastRow = Range("A:E").Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Begin with number 1 that will relate to TextBox1.
lngCounter = 1
'Open a loop for each row amongst the last 10 rows.
For xRow = LastRow - 9 To LastRow
'Open a loop for columns A (1) to E (5).
For xColumn = 1 To 5
'The TextBoxes are named TextBox1, TextBox2, and so on to
'TextBox5 in the first row of TextBoxes on the userform.
'The naming sequence repeats with TextBox6 to TextBox10
'and so on for each row of TextBoxes on the userform.
Controls("TextBox" & lngCounter).Value = Cells(xRow, xColumn).Value
'For the fifth TextBox corresponding to column E,
'format it for a readable hire date.
With Controls("TextBox" & lngCounter)
If xColumn = 5 Then
.Value = Format(.Value, "MMM DD, YYYY")
End If
End With
'Add a 1 to each iteration to match the next TextBox name.
lngCounter = lngCounter + 1
'Continue and close the columns loop.
Next xColumn
'Continue and close the rows loop.
Next xRow
End Sub
I have been searching for days for an answer and yours is the only one that comes close. I have a userform for data entry and there are many textbox & comboboxes and there is one section that has 10 rows to be updated in five columns but it is not guaranteed all 10 rows will have data (depending on what is shipping) With that said, my sequence is as follows:
Row 1: ComboBox1, TextBox14, ComboBox11, TextBox24, TextBox34
Row 2: ComboBox2, TextBox15, ComboBox12, TextBox25, TextBox35
Row 3: ComboBox3, TextBox16, ComboBox13, TextBox26, TextBox36
and so on up to row 10. Can you help me with know how the code would be written to post the various boxes to the next empty cell for each column. Currently, I am overwriting information and that’s not what I want.
I was with you until the last 2 sentences.
Suppose the next empty row is row 8.
Suppose the only populated comboboxes and textboxes are “row 2’s” being ComboBox2, TextBox15, ComboBox12, TextBox25, TextBox35.
Which row should that data go? Row 8? Row 2? What if the cells in row 2 of the sheet are populated, and you don’t want to override data?
It makes sense to me that at any given time, when you (for example) click a commandbutton on your userform that is meant to transfer the data from the comboboxes and textboxes to your worksheet, that all data on the sheet in that 50-cell range be cleared and the data from the comboboxes and textboxes are transferred to those cells. But that would be an easy scenario and probably not what you have in mind.
Sooooo…further details are needed about your workbook design and exactly which controls get their data transferred depending on which cell(s) are or are not occupied.