Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA - Code to scroll listbox down

Posted on 2010-09-15
12
Medium Priority
?
3,393 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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 34

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 34

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 34

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

730 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