To programmatically scroll your ListBox, you can use the TopIndex property. This property determines which entry is the first visible entry in the ListBox.
To make the first entry visible at the top of the ListBox, use
Me.ListBox1.TopIndex = 0
Remember that the items in a ListBox start at zero, not 1. You can show the last entry, but it won’t be at the top, i.e. it won’t show blank spaces after the last entry. Using code like this
Me.ListBox1.TopIndex = Me.ListBox1.ListCount - 1
will make the last entry visible, but it will be at the bottom of the ListBox, not the top.
A recent newsgroup poster was using a ListBox as a progress indicator (very clever, I thought). He would use AddItem to add the steps to the ListBox as they were completed, but the ListBox doesn’t scroll automatically with AddItem. Here’s an example of how to use a ListBox as a progress indicator.
Sub RecordProgress()
Dim i As Long
For i = 1 To 15
Me.ListBox1.AddItem "Step " & i & " completed"
Me.ListBox1.TopIndex = Me.ListBox1.ListCount - 1
Application.Wait Now + TimeSerial(0, 0, 1)
Next i
End Sub
This procedure doesn’t do anything substantive, it just illustrates how you can scroll the ListBox to show the most recent entry.
Another use for this is to match ListBox entries with text that a user types into a TextBox. Take a userform like this
You can use the Change event for the TextBox to bring the proper entry into focus with code like this
Private Sub TextBox1_Change()
Dim i As Long
Dim sFind As String
sFind = Me.TextBox1.Text
If Len(sFind) = 0 Then
Me.ListBox1.ListIndex = -1
Me.ListBox1.TopIndex = 0
Else
For i = 0 To Me.ListBox1.ListCount - 1
If UCase(Left(Me.ListBox1.List(i), Len(sFind))) = UCase(sFind) Then
Me.ListBox1.TopIndex = i
Me.ListBox1.ListIndex = i
Exit For
End If
Next i
End If
End Sub
This code will work without the TopIndex property, but TopIndex will put the selected item as near the top as possible. Here’s what it looks like
Morning
Dick, if you wanted to match text to list box like this, could/would you not just use a combo box?
When I started to learn VBA, I read a book by Steve Cummings and he had this to say.
“My advice is to use combo box controls for all options you present in lists, whether or not the user is allowed to type in entries that aren’t in the list. Forget about list boxes.
Here’s why: A VBA list box can’t display items in a drop-down list. Instead it’s just a rectangle area on the form where the choices are listed. This doesn’t solve the space and clutter problem if the list contains any significant number of items, it takes up too much room on you form….”
Now I don’t agree with this, but I do see his point, kinda..
Basically, I think that if you need to view more than 1 thing simultaneously then list boxes are the way to go, but for choices of one thing from a list then combos are the best option… this is right no?
ross: I think most of the time it’s a personal preference/design thing. There are times when one definitely makes more sense that the other. You’ve got me thinking why a choose one over the other, and I can’t come up with a solid theory yet.
One situation where I choose a listbox is when it’s important or useful to see the surrounding entries. I tend to use comboboxes when the listed item isn’t particularly meaningful, e.g. select the customer ID from a combobox and a bunch of textboxes fill with the name, address, etc.
Surely space is going to be a consideration. Also, multiselect capablilities would force your hand. This sounds like a blog post. I better ask the readers what think.
I was interested in this code because I’d like to highlight a record in a listbox based on a value entered by the user. But the code example is from Visual Basic. The TopIndex property is not avaialable in VBA. I’d be interested to know if anybody knows how to do this in VBA.
Thanks!
mlbotma@charter.net
Matt: All the above code is VBA. If you’re using a listbox from the Forms toolbar on a worksheet, then it will not have a TopIndex property and you’ll need to use one from the Control Toolbox.
Well, I see now that this example is for Excel. I’m using Access, and for some reason, the TopIndex property isn’t available. Any other suggestions for me? Thanks!
Hi, when I use topIndex property there is an error message
‘topindex’ is not a member of ‘System.Web.UI.WebControls.ListBox’.
Do you have any idea?
Thanx
Your example on listbox scrolling gave me just the help I needed.
I’ve been creating a user form for data entry and although I was getting the list box displaying the new entries I was totally losing it while trying to get the list box to shift focus to the newly added entry.
I’ve been using VBA for a while now without any formal training or reference material and did not know about ListIndex and had not seen any reference to it in the help files.
hi,
I was trying to come up with something like this in java script. the code for the listbox(select tag) in html tags would have multiple attribute set to allow multiple selections. however, when I use this tag the listbox would not auto scroll till the first visible selected item. If i donot specify multiple attribute it does scroll ,but I cannot have multiple values selected.
Is there a way I can have multiple values selected but have the listbox scroll to the first selected item automatically.
Can somebody throw some light on this.
Thanks
sree
Well, fellas – I’ll never use it in excel, but if you always want the LAST item in a list box visible, then this is the way to go. I just used it in a simple embedded chat application – really nice…
L
Hi,
It works for me! Thanks for the tip and the examples!
Greetz Jr.
Hey Dick thanks a lot for this piece of code i was trying to show the the top most value in a list. Once again thanks a lot.
With VS 2010 the textbox no longer has a Text_Change event, but does however have a TextChanged event. With some trial and error I was able to modify your code to give the results of a text change event. The following code will autoscroll a list box as each character is typed in a text box. Thanks for pointing me in the right direction.
Private Sub txtStrtName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStrtName.TextChanged
Dim i As Integer
Dim sFind As String
sFind = Me.txtStrtName.Text
If Len(sFind) = 0 Then
Me.lstStreets.SelectedIndex = -1
Me.lstStreets.TopIndex = 0
Else
For i = 0 To Me.lstStreets.Items.Count – 1
If lstStreets.Items.Item(i).ToString.StartsWith(sFind) = True Then
Me.lstStreets.TopIndex = i
Me.lstStreets.SelectedIndex = i
Exit For
End If
Next i
End If
End Sub