Solved

Excel VBA - Code to scroll listbox down

Posted on 2010-09-15
12
2,951 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now