Excel – How to fix an Excel listbox that can’t scroll the last element into view

excellistboxuser interfacevba

A killer problem I've had in excel UIs since as long as I can remember, is with listbox scrolling.

When you have more elements in a listbox that can be displayed, a scoll bar will appear. In certain conditions, however, scrolling the bar all the way to the bottom of the list and releasing it, will "jump" the bar a notch upwards, and you won't be able to see the last item in the list. This is illustrated here:
Can't scroll all the way down

There are many forum posts presenting this issue, and the solution has always been "Set the integral height property to false, and then set it to true again." What this does is slightly resize the listbox so that it the height is rounded to the height of a single row, and then no items are left hidden.

With lstbox
    .IntegralHeight = False
    .Height = myHeight
    .IntegralHeight = True
End With

There are certain cases, however, where this does not work. If you are:

  1. Programatically setting the height of your listbox
  2. NOT using simple listbox selection (fmMultiSelectSingle)

Then simply setting integral height to false and then true after or between changes to height will make an adjustment to the height of your listbox, but when you go to scroll down, the problem will remain – the last item cannot be seen.

The key to this frustrating question is that while everyone else on the internet is confirming that the 'integralHeight' solution works for them, these very special cases are frustrated wondering why it doesn't work for them. So how do they get their fix?

Best Answer

Something I had to discover for myself, and which cannot be found anywhere else (which is why I'm posting it here), is that this problem had the added dimension of being dependent on the selection method. While I cannot fathom how the way the scroll bar works is related to not only the height and integral height property, but also the .MultiSelect property, I have found that it is.

When the .IntegralHeight method shown above does not work, the following method somehow does:

With lstbox
    .IntegralHeight = False
    .Height = myHeight
    .IntegralHeight = True
    .MultiSelect = fmMultiSelectSingle
    .MultiSelect = fmMultiSelectExtended
End With

By simply changing the .MultiSelect property to fmMultiSelectSingle, and then restoring it back to the selection style desired, the height of the listbox will be automatically adjusted by the .IntegralHeight property to a slightly different height than when these actions aren't performed - the difference results in the scroll bar working correctly:

Workaround worked

I hope the discovery of this special case and more precise workaround saves someone the hours of frustration and experimentation I had to go through.

Related Topic