Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA - Code to scroll listbox down

Posted on 2010-09-15
12
Medium Priority
?
3,501 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 35

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 35

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 35

Accepted Solution

by:
Norie earned 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

971 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