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

Question

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:

Programatically setting the height of your listbox
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?

Any feedback on how I've presented the problem and proposed solution, or whether I might have overlooked some other facet are more than welcome. — Alain, May 02 '11 at 16:22

score 16 · Accepted Answer · answered May 02 '11 at 16:17

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.

score 6 · Answer 2 · answered Feb 21 '18 at 07:32

i know this is very old post. but i've been through a lot to fix this problem, so i just wanna share my tip. :)

first of all, integralheight method doesn't work when worksheet zoom level is not 100%.

it will change listbox height and width, location, etc. (even if you set object property 'doesn't move or reseize with cell')

and when you try to take it its original size and location with code to fix this, this time its last item can't be seen

my tip is simple. there's combination between font size and listbox height.

if your font size is 6-10(arial, regular), listbox height goes well with multiples of 12.75 (btw my list box style is 1 : ListStyle, 1-fmListStyleOption. it could be different with style 0)

as long as height is same with these multiples of 12.75, there will be no problem.

in case of font size 12(arial, regular), it's multiples of 13.55

so if there's no restiction about listbox size in your project, just resizing it slightly depending on your font size gives more comfort. :)

Wow... I've been looking for 2 months for an answer.. this was too simple and finally something that worked.. kudos — Simulator, Oct 11 '19 at 04:49
Wow....It works. Thank you so much. You are saving lives...... :) — Charlie, May 18 '21 at 11:14
Changed my LB height to 102 (a multiple of 12.75) instead of 105 and it now works with no lines hidden, not sure if the calculation is exact as 103 also works, but 104 does not. Font size starts at 8pt Times New Roman, but I have a resizer button and it seems to work for all sizes after loading at least. — Justin Doward, Feb 28 '22 at 01:26

score 3 · Answer 3 · answered Nov 25 '12 at 19:24

3

I had to anchor the position since my ListBox was walking across the page:

With ListBox1 
 .IntegralHeight = False
 .IntegralHeight = True
 .Height = 45
 .Width = 69
 .Top = 0
 .Left = 1255.5
End With

answered Nov 25 '12 at 19:24

David Pesetsky

254
5
13

score 3 · Answer 4 · answered Oct 02 '13 at 19:19

3

With lstbox

`.Height = myHeight`
`.MultiSelect = fmMultiSelectExtended`
`.MultiSelect = fmMultiSelectSingle`

End With

This worked for me. No need of setting Integral height property

answered Oct 02 '13 at 19:19

mayur2j

141
2
13

This works ONLY if you have IntegralHeight already set to True. – drwatsoncode Mar 25 '16 at 20:00

score 1 · Answer 5 · answered Feb 22 '12 at 16:00

1

In my case the solution was this method:

with listbox
   .IntegralHeight = False
   .Height = myHeight
   .Width = myWidth
   .IntegralHeight = True
   .Height = myHeight
   .Width = myWidth
end with

Enjoy.

answered Feb 22 '12 at 16:00

miyatto

11
1

score 0 · Answer 6 · answered Jul 10 '15 at 04:10

0

found ridiculously simple way to resolve this issue. adjust your height up or down a little bit so bottom line of list box is between check boxes, then you can scroll down to last item even if IntegralHeight is set to false

answered Jul 10 '15 at 04:10

Kayne

1

In the accepted answer it's written that adjusting the height was not helping. Could you provide more details? – bish Jul 10 '15 at 04:19

score 0 · Answer 7 · answered Dec 21 '15 at 21:20

Thanks Alain. Your fix worked well for me.

I found a subsequent problem related to the height of the ListBox when resized, that it varied in an unpredictable way depending on the initial height. The resized height was different again when displayed on another machine with 125% text scaling. For example, if I set a height between 358 and 370, the resized height is either 370.65 or 371.4 on my machine but on the machine with 125% text scaling, it is 360.1, 370.25 or 380.45. With such large variability, the result was that the ListBox could obscure other controls below it.

The fix was to start with the maximum height I wanted and reduce the initial height until the resized height was less than the maximum height I wanted. I do this whenever I display that ListBox.

    Hmax = 372      'Target Height 
    H1 = Hmax
    With SteelForm.Controls.Item("ListBox1")
        Do
            H1 = H1 - 1
            .IntegralHeight = False
            .Height = H1
            .IntegralHeight = True
            .MultiSelect = fmMultiSelectSingle
            .MultiSelect = fmMultiSelectExtended
            DoEvents
        Loop Until .Height < Hmax
    End With

score 0 · Answer 8 · answered Jun 06 '23 at 08:06

0

My simple solution: append a blank item to the list.

myListBox.AddItem ""

With this the blank item will be out of scroll view (i.e. user will never know it's there) and the actual last item will thus become visible.

answered Jun 06 '23 at 08:06

longvh

141
2
9

score -1 · Answer 9 · answered May 02 '11 at 19:56

-1

What I've seen in the past on forums is just adding an extra blank row to your list box. That should do it.

answered May 02 '11 at 19:56

Jon49

4,444
4
36
73

3

I didn't downvote, but an example of when this wouldn't be acceptable is when your listbox is bound to a named range which is used for more than just the listbox. It wouldn't be reasonable to alter the data source and force other code that uses that data source to have to account for the blank last row. It also means that any routines that perform an action on the selected items in the listbox must me made to check whether a valid item was selected. – Alain May 03 '11 at 15:47

score -1 · Answer 10 · edited May 23 '12 at 11:38

-1

Just set the Integral Height property to True

edited May 23 '12 at 11:38

Community

1
1

answered Feb 15 '12 at 08:27

sridevi

1

2

It's clearly stated in the problem and the solution posted last year that this is not sufficient. – Alain Feb 15 '12 at 15:58

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

10 Answers10

Linked