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.

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!