Excel ActiveX ListBox每次更新都会缩小 [英] Excel ActiveX ListBox Shrinks with each update

问题描述

我有一组链接的sub，它们的工作方式如下:

I have a set of linked subs which work like this:

1. 用户键入ActiveX文本框
2. 该TextBox中的Change Event调用模块中的一个子项
3. 该Module子驱动器更新工作表中的命名范围
4. 范围值会驱动更新基于范围值使用查找功能的Excel单元格表
5. 将表值复制并粘贴到另一个范围(以消除到公式的链接)

6. 使用该粘贴范围将其粘贴到ListBox中(Rory耐心等待此道具):

1. A user types into an ActiveX TextBox
2. A Change Event in that TextBox calls a sub in a Module
3. That Module sub drives updating a named range in a sheet
4. The range value drives updating a table of Excel cells that uses lookup functions based on the range value
5. The table values are copied and pasted to a another range (to eliminate links to formulas)

6. That pasted range is put into a ListBox using this (props to Rory for his patience):

ActiveSheet.ListBox1.List = Sheets(搜索条件控件").Range("G1:G21").值

ActiveSheet.ListBox1.List = Sheets("Search Criteria Control").Range("G1:G21").Value

结果是，对于用户在TextBox中键入的每个字符，都会更新ListBox.

The result is that for every character the user types in the TextBox the ListBox is updated.

我遇到的问题是，上面#1中提到的TextBox中的每个按键，ListBox都会缩小一点.这是正常现象吗?我在滥用ListBoxes，是否做错了事?还是每次用类似这样的方法更新ListBox时都需要重新指定其尺寸?

The problem I have is that the ListBox shrinks a bit with every keystroke in the TextBox referred to in #1 above. Is this normal behavior and I'm misusing ListBoxes, am I doing something wrong or do I need to respecify the dimensions of the ListBox every time it is updated with something like this?

ActiveSheet.OLEObjects("ListBox1").Top = 35
ActiveSheet.OLEObjects("ListBox1").Left = 650
ActiveSheet.OLEObjects("ListBox1").Width = 550
ActiveSheet.OLEObjects("ListBox1").Height = 610

预先感谢您对此的任何思考.

Thanks in advance for any thoughts on this.

推荐答案

我在同一件事上遇到了麻烦.我的ActiveX列表框会在工作表上四处移动并更改大小，这是我无法看到的任何原因.

I was having trouble with the same thing. My ActiveX listbox would move around on the sheet and change size for no reason that I could see.

尽管我确实继续开发一些代码来重置大小和坐标，但这并不令人满意，因为必须要有一种机制来触发该代码-我不想让最终用户感到负担.

While I did go ahead and develop some code to reset size and coordinates, that wasn't satisfactory since there had to be a mechanism to trigger that code - something I didn't want to burden end-users with.

我在另一个用户论坛中找到了一个更好的答案.有一个名为 IntegralHeight 的列表框属性，其默认属性为True-与屏幕分辨率和列表框内容的最佳显示有关.只需将其设置为False.我用一些适合我的ActiveX盒子做到了这一点，并且我能够禁用调整"代码，到目前为止，一切都很好！

I found a better answer in another user forum. There's a Listbox Property called IntegralHeight whose default property is True - something to do with screen resolution and optimal display of listbox contents. Simply set that to False. I did that with some ActiveX boxes that were giving me fits, and I was able to disable the "adjustment" code and, so far, so good!

这篇关于Excel ActiveX ListBox每次更新都会缩小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！