Listbox Height and Width

[drfergus]

Hello All,

I'm creating a tool at work that takes large amounts of data from our production test cells, sort it, then post processes it. I didn't want to use a User Form for this tool, so I have several ListBox and ComboBoxes on the first sheet which allows the user to select the file and attributes.

The problem I am having is that when I run the macro to populate these ListBoxes and ComboBoxes, they automatically resize, which screws up the overall appearance of the sheet, and is rather annoying. I've tried going into the properties and setting the height, width, top and left, but that has not fixed the issue. How can I set these parameters in my macro?

Thanks!

 

[AlphaFrog]

Do you have the .AutoSize property set to false?

Example code...

Set rng = Range("B2:D2")
With ComboBox1
    .AutoSize = False
    .Top = rng.Top
    .Left = rng.Left
    .Height = rng.Height
    .Width = rng.Width
End With

 

[drfergus]

Yes, with my comboboxes the AutoSize is set to False, and I'm not having problems with those. I'm having problems with my ListBoxes. SOrry that wasn't clear in my original post.

With the code you shared, do I post that into the individual subroutines, or should I post it in the sheet and have it run when the workbook is opened?

 

[AlphaFrog]

For the Listboxes, set the .IntegralHeight property to False.

You may not need my code if you can set the appropriate default settings so they don't re-size.

 

[drfergus]

Well, I tried setting the default settings, saving my workbook, closing it and opening it again, and none of the dimentions stayed the same. I can't figure out what is causing it to do that, but I did end up making a subroutine with your code for each of my list/comboboxes, and then I call that sub with the workbook opens, and again after I've populated the boxes. Seems to work, and doesn't add a significant amount of processing time to my routine.

Thanks again!