Displaying rows last 5 values in a listbox

TSoren23

New Member
Joined
Sep 11, 2006
Messages
16
Hi, I have been working on this one all day.

I have a userform to input data into a worksheet. It inserts a new row and copies the forms data to that row. I would like a list box in the userform to display the last 5 rows values in columns A,B,D from the worksheet.

Any suggestions?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello TSoren23,
I'm not sure if this is what you want to do or not but with the following code in
the userform's initialize event it will populate 'ListBox1' with the values of the last
5 rows in column A, then the last 5 rows in column B and finally the last 5 rows
in column D.
Code:
Private Sub UserForm_Initialize()
Dim LstRw As Long, c As Range
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

For Each c In Range(Cells(LstRw - 4, "A"), Cells(LstRw, "A"))
  Me.ListBox1.AddItem (c.Value)
Next c
For Each c In Range(Cells(LstRw - 4, "B"), Cells(LstRw, "B"))
  Me.ListBox1.AddItem (c.Value)
Next c
For Each c In Range(Cells(LstRw - 4, "D"), Cells(LstRw, "D"))
  Me.ListBox1.AddItem (c.Value)
Next c

End Sub
Note it will populate the listbox with these values as they are when the userform
gets called - not after the userform enters new data to the worksheet.
If that's what you want to have happen then you'll want to move the code to
whatever routine you're using to enter the data on the sheet instead of using the
userform's initialize event.
Also, it assumes the last 5 rows in columns B & D will be the same rows as the
last 5 in column A. If that's not the case then you'll need to re-establish the LstRw
variable between each of the loops.
 

TSoren23

New Member
Joined
Sep 11, 2006
Messages
16
HalfAce, Thank you.
It works very well.
I am learning as I go so please bear with me. How would I display the values side by side in the listbox? What I need to do is display the last 5 rows from a worksheet, but only columns a,b,d. Also, the userform opens from a "open" worksheet. all the data worksheets are hidden. It looks like I will need to add a statement to make the needed worksheet active. Is this correct?
 

TSoren23

New Member
Joined
Sep 11, 2006
Messages
16
HalfAce, Thank you.
It works very well.
I am learning as I go so please bear with me. How would I display the values side by side in the listbox? What I need to do is display the last 5 rows from a worksheet, but only columns a,b,d. Also, the userform opens from a "open" worksheet. all the data worksheets are hidden. It looks like I will need to add a statement to make the needed worksheet active. Is this correct?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Hello TSoren23
You're most welcome.
If by 'the userform opens from a "open" worksheet.' you mean it gets called from
the 'active' worksheet then I'm still with you. If you meant something other than
that then I'm afraid I don't follow.
In either case you shouldn't need to unhide/activate the sheet containing the
listbox data. We seldom ever have to select or activate an object to work with it.
Give something like this a try, replacing the code you now have in the userform's
initialize event and see if it's what you're after.
(Assumes the hidden sheet containing the listbox data is named 'Sheet1'. If it's not
then simply replace both instances of Sheet1 in the code with the sheet's real name.)
Code:
Private Sub UserForm_Initialize()

Dim LstRw As Long, ArryRng As Range, ArryData As Variant
LstRw = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Sheet1")
  Set ArryRng = Range(.Cells(LstRw - 4, "A"), .Cells(LstRw, "D"))
End With

ArryData = ArryRng.Value

With Me.ListBox1
  .ColumnCount = 4
  .ColumnWidths = "50;50;0;50"
End With

Me.ListBox1.List = ArryData

End Sub

Does that get you any closer?
 

TSoren23

New Member
Joined
Sep 11, 2006
Messages
16
Hi HalfAce,

Thank you very much! It works great.
That is exactly what I am looking for.

Thank you.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
You're very welcome. Glad it helped.

...And now you should know enough to be dangerous. (Or at least a bit precarious!) :LOL:
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top