vba 如何修复无法将最后一个元素滚动到视图中的 Excel 列表框

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

提问by Alain

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

从我记事起，我就在 excel UI 中遇到的一个致命问题是列表框滚动。

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:

当列表框中有更多可以显示的元素时，会出现一个滚动条。但是，在某些情况下，将栏一直滚动到列表底部并释放它，会将栏向上“跳”一个档次，您将无法看到列表中的最后一项。这在此处说明：

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.

这个问题论坛帖子很多，解决方法一直是“将积分高度属性设置为false，然后再设置为true”。这样做是稍微调整列表框的大小，使其高度四舍五入到单行的高度，然后没有任何项目被隐藏。

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. NOTusing simple listbox selection (fmMultiSelectSingle)

1. 以编程方式设置列表框的高度
2. 不使用简单的列表框选择 (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.

然后简单地将积分高度设置为 false 然后在更改高度之后或之间设置为 true 将调整您的列表框的高度，但是当您向下滚动时，问题仍然存在 - 看不到最后一个项目。

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?

这个令人沮丧的问题的关键是，虽然互联网上的其他人都在确认“integralHeight”解决方案对他们有用，但这些非常特殊的情况很沮丧，想知道为什么它对他们不起作用。那么他们如何获得修复呢？

采纳答案by Alain

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.

我必须为自己发现，并且在其他任何地方都找不到的东西（这就是我在这里发布的原因），是这个问题具有依赖于选择方法的附加维度。虽然我无法理解滚动条的工作方式不仅与高度和整体高度属性有关，而且与 .MultiSelect 属性有关，但我发现确实如此。

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

当上面显示的 .IntegralHeight 方法不起作用时，以下方法以某种方式起作用：

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:

通过简单地将 .MultiSelect 属性更改为 fmMultiSelectSingle，然后将其恢复为所需的选择样式，列表框的高度将通过 .IntegralHeight 属性自动调整为与不执行这些操作时略有不同的高度 -差异导致滚动条正常工作：

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.

我希望发现这种特殊情况和更精确的解决方法可以为某人节省我不得不经历的沮丧和实验的时间。

回答by David Pesetsky

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

由于我的 ListBox 在页面上行走，我不得不锚定位置：

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

回答by mayur2j

With lstbox

With lstbox

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

End With

End With

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

这对我有用。无需设置 Integral height 属性

回答by SJJo

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%.

首先，当工作表缩放级别不是 100% 时，integralheight 方法不起作用。

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)

如果您的字体大小为 6-10（arial、regular），则列表框高度与 12.75 的倍数相符（顺便说一句，我的列表框样式为 1 : ListStyle, 1-fmListStyleOption。样式 0 可能不同）

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

只要身高和12.75的倍数一样，就没有问题。

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

如果字体大小为 12（arial、regular），则为 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. :)

因此，如果您的项目中没有关于列表框大小的限制，只需根据您的字体大小稍微调整它的大小即可提供更多舒适度。:)

回答by miyatto

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.

享受。

回答by Kayne

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

找到了解决这个问题的非常简单的方法。稍微向上或向下调整您的高度，以便列表框的底线位于复选框之间，即使 IntegralHeight 设置为 false，您也可以向下滚动到最后一项

回答by Paul Cain

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.

我发现了一个与调整大小时 ListBox 高度相关的后续问题，它根据初始高度以不可预测的方式变化。当在另一台具有 125% 文本缩放比例的机器上显示时，调整后的高度再次不同。例如，如果我将高度设置在 358 和 370 之间，则调整后的高度在我的机器上是 370.65 或 371.4，但在文本缩放比例为 125% 的机器上，它是 360.1、370.25 或 380.45。由于存在如此大的可变性，结果是 ListBox 可能会掩盖其下方的其他控件。

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

回答by Jon49

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

我过去在论坛上看到的只是在列表框中添加了一个额外的空白行。那应该这样做。

回答by sridevi

Just set the Integral Height property to True

只需将 Integral Height 属性设置为 True