Solved

Excel VBA - Code to scroll listbox down

Posted on 2010-09-15
12
3,146 Views
Last Modified: 2013-12-20
Hi all,

After I add items to a listbox using VBA, how can I programmatically make the listbox scroll down to the recently-added item?

I've done some searching, and most of the solutions out there rely on the SetFocus method, or the TopIndex property. According to my Excel (Excel 2007), I don't have either of these options available.  
0
Comment
Question by:jacksonm1234
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 12

Expert Comment

by:North2Alaska
ID: 33686700
Hmmm..  I just check on my 2007 and I have both TopIndex and SetFocus.  These are the two you need to use in combination with ListIndex.
0
 
LVL 2

Author Comment

by:jacksonm1234
ID: 33686813
Hmmm, then maybe I'm doing something wrong.  I think Access 2007 has those functions -- you aren't looking there by chance, are you?  Below is a sample of my code -- see anything I'm doing wrong?
 

Dim lbstatus As ListBox
Set lbstatus = ActiveSheet.ListBoxes.Item("lbStatus")
lbstatus.AddItem ("Record successfully inserted at " & Now)
lbstatus.SetFocus  'ERROR ON THIS LINE -- "Object doesnt support this property or method"

Open in new window

0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 33687013
Try setting the ListIndex first.
Dim lbstatus As ListBox
Set lbstatus = ActiveSheet.ListBoxes.Item("lbStatus")
' Get the current selected item
idx = lbstatus.count
lbstatus.AddItem ("Record successfully inserted at " & Now)
lbstatus.ListIndex = idx + 1
lbstatus.SetFocus  

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:jacksonm1234
ID: 33687035
No dice.  Now I get the error (Object doesn't support blah blah) on this line:
idx = lbstatus.count
So apparently I can't get a count either.  I dont know what the crap is going on.  Are you able to run your code without issue?
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 33690378
check whether this helps you

ActiveSheet.lbstatus.AddItem "New Item", ActiveSheet.lbstatus.ListCount
ActiveSheet.lbstatus.Selected(ActiveSheet.lbstatus.ListCount - 1) = True


This will insert the item by creating a new index and then select that index.
0
 
LVL 2

Author Comment

by:jacksonm1234
ID: 33691615
Thanks for your help shasunder, but it's still not scrolling.  I can select the new item with no problem, but it doesnt scroll to it at the bottom of the listbox.  Are you able to get it working on your own Excel by chance?  Here's the code I tried.
lbstatus.AddItem "New Item2", lbstatus.ListCount
lbstatus.Selected(lbstatus.ListCount - 1) = True
0
 
LVL 33

Expert Comment

by:Norie
ID: 33693744
I just tried this and it worked fine.

Mind you this was using a listbox on a userform, and I didn't change any of it's properties.

What type of listbox are you using?

Private Sub CommandButton1_Click()
    ListBox1.AddItem "New Item"
    
    ListBox1.ListIndex = ListBox1.ListCount - 1
End Sub

Open in new window

0
 
LVL 2

Author Comment

by:jacksonm1234
ID: 33694304
Ugh. That may be where the problem is.  I have the listbox embedded on a spreadsheet -- not on a separate form.  Do you think that could be part of the issue?  Is there a way around that?
0
 
LVL 33

Expert Comment

by:Norie
ID: 33695980
How did you create the combobox/dropdown?
0
 
LVL 2

Author Comment

by:jacksonm1234
ID: 33696006
I created the listbox using the developer tab in the ribbon, choosing insert, and then i clicked on the appropriate icon for a listbox and stuck it on my spreadsheet.
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 33696184
But which type of control is it?

There are 2 options when inserting - Forms or ActiveX.

The code I posted seems to work for the Activex type.

The forms type is probably another matter  - they can be difficult to work with.
0
 
LVL 2

Author Comment

by:jacksonm1234
ID: 33696319
That did it. I was using the Forms controls. Switching to ActiveX works! Thank you!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question