Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3631
  • Last Modified:

Excel VBA - Code to scroll listbox down

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
jacksonm1234
Asked:
jacksonm1234
  • 6
  • 3
  • 2
  • +1
1 Solution
 
North2AlaskaCommented:
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
 
jacksonm1234Author Commented:
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
 
North2AlaskaCommented:
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!

 
jacksonm1234Author Commented:
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
 
Shanmuga SundaramDirector of Software EngineeringCommented:
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
 
jacksonm1234Author Commented:
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
 
NorieData ProcessorCommented:
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
 
jacksonm1234Author Commented:
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
 
NorieData ProcessorCommented:
How did you create the combobox/dropdown?
0
 
jacksonm1234Author Commented:
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
 
NorieData ProcessorCommented:
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
 
jacksonm1234Author Commented:
That did it. I was using the Forms controls. Switching to ActiveX works! Thank you!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now