Excel ActiveX ListBox Shrinks with each update

Question

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

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("Search Criteria Control").Range("G1:G21").Value

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

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.

Gruesome

This is a common and very longstanding bug with ActiveX controls on worksheets. You'll need to reset them explicitly in your code, or use Form controls instead which are generally more stable. – Desirous

Does this help? – Applesauce

Ok, thanks Rory. Are Form controls clickable so you can drive other code to run? – Gruesome

David, thank you also. Setting the IntegralHeight to False stopped the resizing. – Gruesome

Answer 1

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.

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!

Anecdotage

Answer 2

IntegralHeight = True is a good solution. I've got a code in my workbook, that resize Listbox every time the sheet was activated - and this solution not knowing why didn't work either.

Faxen

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Variole