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()
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"), _
'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")
'Add a 1 to each iteration to match the next TextBox name.
lngCounter = lngCounter + 1
'Continue and close the columns loop.
'Continue and close the rows loop.