jacksonm1234
asked on
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.
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.
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.
ASKER
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"
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
ASKER
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?
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?
check whether this helps you
ActiveSheet.lbstatus.AddIt em "New Item", ActiveSheet.lbstatus.ListC ount
ActiveSheet.lbstatus.Selec ted(Active Sheet.lbst atus.ListC ount - 1) = True
This will insert the item by creating a new index and then select that index.
ActiveSheet.lbstatus.AddIt
ActiveSheet.lbstatus.Selec
This will insert the item by creating a new index and then select that index.
ASKER
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
lbstatus.AddItem "New Item2", lbstatus.ListCount
lbstatus.Selected(lbstatus
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?
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
ASKER
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?
How did you create the combobox/dropdown?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did it. I was using the Forms controls. Switching to ActiveX works! Thank you!