Listbox scroll position - refreshing

[AlexanderBB]

Is it possible to get/set the scroll position in a listbox?
My listbox allows the (same number of) list items to be changed. If the user has scrolled somewhere then invokes the change the list returns to the top item. I'd like the scroll position to remain where it was.
I did try using .selected(listIndex) but while that worked it meant you no longer click on that item, without moving away and back. That didn't feel right.
Using .selected(listIndex)= false sorted that, but loses the scroll position.
Is there any solution ? Thanks.

 

[Micron]

As you probably know by now I dabble in Excel vba and am no expert, and after looking at the properties and methods of a userform listbox there is nothing there regarding scroll position. What I did find is that if I get the listindex value for the selected item first, then move the list, setting the listindex back to that index value moves that row back into view. However, it depends on the list count and list height and the position of the selected item in the list as to what that does. That list row may become the top most list item, or it may be a few rows down, but it will be in view. The only way I can think of to make sure it's always at the top would be to add as many empty rows as the visible row count (maybe minus 1). That way even the last real list row could move to the top as there would be enough empty rows below to allow that to happen.

 

[AlexanderBB]

Thanks for the reply Micron and your thoughts on the listbox. I suspected there'd be no easy way (any way?) but the msyeries of API, way beyond me, can do all sorts and hence the question.
The list box contents change, not the total, so keeping the same position back and forth, and with nothing selected would have been - not sure of the word - nice, convenient. But the poor ole user, should he want back there will just have to scroll down. And knowing it can't be done, I can stop trying.

 

[Micron]

IMO if there's an API for what you want it will introduce unwarranted complexity and the potential for problems down the road - like when we one day get to 128 bit OS and API pointers have to change all over again in your code. Not enough pay back for saving a user a bit of scrolling.
This is as close as I can get using the properties of an Excel listbox with 10 visible rows and 10 empty rows at the end.

If I scroll to near the top then set the listindex to what was selected above

the first selected item ends up back in the visible portion of the list, but probably could end up anywhere.

In case it would prove useful, I'll mention that when using listboxes or combo boxes in Access, it's common to have a FAYT (find as you type) feature. For a listbox it would require you to have a textbox on your form in which the user filters the list to match the value (using LIKE operator). No idea if Excel would support that, even if you thought it to be useful for this thread's issue. Maybe that's just something you file in the back of your mind for the future.

 

[Jaafar Tribak]

My listbox allows the (same number of) list items to be changed

What do mean by that? How are you changing the list item(s)?

 

[AlexanderBB]

The job is build a playlist from vinyl records data. There's an A and a B Side title. Some criteria is pre-selected and populates the listbox. Any you select (click on) go into a queue.
As you scroll through the list you may want to shift between the A & B titles. An option group selects A or B, clears the list then uses add Item to fill it again with that sides data.
As it's designed and tested ideas come up for things to try. Being able to ask in a forum like this if such and such is possible is very handy.
As an alternative maybe I can but both items into the one listbox and see which is clicked. Or use a ListView and HiTtest. Now that the listbox scroll idea is a no good.

 

[Micron]

Why not put the A side in one list and the B side in another?
Not sure what the queue is about, but what comes to mind is the idea of transferring between listboxes. Usually it involves 2, side by side, and you select list item(s) from A and click a transfer button (or click transfer all) and they go into the other listbox and that is your "queue". In your case, you might need 4 lists: left and right A and left and right B. Or just 2 and left listbox has a column that holds the album side identifier (A or B). Maybe other listbox would have the "side" column as well.

 

[AlexanderBB]

That's a good idea...thanks. I'll try it and see how it all fits.