Excel ActiveX ListBox Shrinks with each update

5
votes

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.

1
vbaexcellistbox
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. - Rory
Does this help? - David Zemens
Ok, thanks Rory. Are Form controls clickable so you can drive other code to run? - mchac
David, thank you also. Setting the IntegralHeight to False stopped the resizing. - mchac

1 Answers

9
votes

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!