Set Vertical Scrollbar Position In User Form Listbox

**Spencer** · 03-27-2011, 01:57 PM

I have a two column listbox on a user form.

Two of my command buttons change the value in the second column for the item selected. The value can be empty (null, blank, etc.) or a "1".

The method I'm using to populate the listbox makes it so that value change is not automatically seen, so after a change is made I've added code for the listbox to repopulate so the chanage can be seen.

Repopulating it shows the value changed to, or from, a "1", but the listbox would always return to the top of the list after it was repopulated. This effectively makes you lose track of where you were in the list.

My next step was to remember the the ListIndex value for the item selected and then after the listbox was repopulated I set the listbox ListIndex value to that previous value.

That works fine, as far as if I there are 100 items on my list, and I changed item number 75, it will reselect that same item number 75, but it is always the very bottom of the window.

In other words, lets say item number 75 was was in the very middle of the visible listbox window when I selected it. So you could see items number 60 though 90 visible in the window. After I repopulate the ListBox and reset the ListIndex number, you will see item number 75 selected, but it will be at the very bottom of the ListBox window and in the window you will see item numbers 45 through 75, instead of item numbers 60 through 90.

I'd like to find a way to set the listbox to scroll to the very same position it was in before I repopulated it, so the user doesn't even know I've repopulated the ListBox. All I want them to see is that value in the second column to chanage to, or from a "1".

I've searched for hours for a solution and the closest I've come is the Userform Scroll event, but that does not seem to apply to a ListBox on a user form.

Is it possible to remember the scroll position of a ListBox and then reset the ListBox to that same position after it is repopulated?

**Andy Pope** · 03-28-2011, 04:22 AM

Try using the TopIndex property

**Spencer** · 03-28-2011, 11:00 AM

Originally Posted by Andy Pope

Try using the TopIndex property

Andy, last night I had figured out a different way to get the selected item to the top of the window, which was better than having it at the bottom of the window.

I didn't know about the TopIndex feature, and my method was nowhere near as easy as using the TopIndex feature, but it worked. What I did was to find the total number of rows in my list, set the ListIndex to that value - 1, and then set the ListIndex to the value of the selected item.

I messed around with TopIndex this morning and did find a solution which uses it. I first figured out how many visible rows were in my ListBox window, which is 39. Before I repopulate my Listbox in addition to remembering the selected items ListIndex number, I also now remember the TopIndex number. Then after I repopulated my ListBox, I set the ListIndex to equal the the value of that TopIndex number I remembered plus 38. This effectively selects the same item that was in the bottom of the visible window before I repopulated the list. Then I set my ListIndex number to the original ListIndex number that I remembered for the selected item.

Everything seems to work great. The only thing that you see happening on the screen is the value of the second column changing to, or from a "1".

Thanks for your help,
Spence

**Spencer** · 03-28-2011, 11:27 AM

I just thought of a problem that may occur using the logic I talked about in my last post. It wouldn't have caused any issues for me in this scenario, because all my lists of items are quite large, but I thought I should put in a safeguard anyway, and post about it here, in case someone finds this post in the future and tries to use this logic.

If the list in the listbox is not large enough, then an error would be caused when I told it to make the ListIndex equal the TopIndex plus 38. So I put an if statement in to check to see if adding 39 to the TopIndex number would be larger than the total number of items in my list.

If it is larger, then I used my original logic, which was to first set the ListIndex to 1 less the total number of items in my list. If it isn't larger, then I used the TopIndex plus 38.

Spence