Strange font size issue with dynamic created controls

[sijpie]

I use a number of forms where the controls are dynamically created. I often then have a strange effect that the controls on one row are in a smaller font than the others. See row 5 on the image.

Now If I make the frame height large enough this effect will disappear, but then so will my frame (workaround of course is to use an invisible frame within a visible frame, but its a bit of a bear to do it that way).

Has anyone got a better idea?

To replicate this effect (I'm using Excel 2010) create a simple form with one frame and add the following code:

<font face=Calibri><br><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> colTB <SPAN style="color:#00007F">As</SPAN> Collection, colCB <SPAN style="color:#00007F">As</SPAN> Collection<br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Activate()<br>    <SPAN style="color:#00007F">Dim</SPAN> ctlTB <SPAN style="color:#00007F">As</SPAN> MSForms.TextBox<br>    <SPAN style="color:#00007F">Dim</SPAN> ctlCB <SPAN style="color:#00007F">As</SPAN> MSForms.Checkbox<br>    <SPAN style="color:#00007F">Dim</SPAN> j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lTop <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> colTB = <SPAN style="color:#00007F">New</SPAN> Collection: <SPAN style="color:#00007F">Set</SPAN> colCB = <SPAN style="color:#00007F">New</SPAN> Collection<br>    <br>    <br>    <SPAN style="color:#00007F">With</SPAN> Frame1<br>    <SPAN style="color:#007F00">' set frame height very large</SPAN><br>    .Height = 15 * (18 + 3) + 10<br>    .Width = 220<br>    <SPAN style="color:#00007F">For</SPAN> j = 1 <SPAN style="color:#00007F">To</SPAN> 6<br>        <SPAN style="color:#00007F">Set</SPAN> ctlCB = .Controls.Add("Forms.checkBox.1", "cbUse" & j)<br>        <SPAN style="color:#00007F">Set</SPAN> ctlTB = .Controls.Add("Forms.textBox.1", "tbDescr" & j)<br>        <br>        colCB.Add ctlCB<br>        colTB.Add ctlTB<br>        <br>        lTop = (18 + 3) * j<br>        <SPAN style="color:#00007F">With</SPAN> ctlTB<br>            .Top = lTop<br>            .Left = 42<br>            .Height = 18<br>            .Width = 120<br>            .Value = "Sample text " & j<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ctlCB<br>            .Top = lTop<br>            .Left = 12<br>            .Width = 18<br>            .Height = 18<br>            .Value = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> j<br>    <SPAN style="color:#007F00">' set frame height to fit controls. Then one row of controls _<br>      will show smaller font. Change the (j+1) to (j+0) or (j+2) _<br>      to see a different row change font size.</SPAN><br>    .Height = (j + 1) * (18 + 3) + 10<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[mole999]

can this .Height = 15 * (18 + 3) + 10 not be .Height = 325

 

[sijpie]

Yes, but that doesn't help in getting rid of the issue if I have the frame height close to fitting at the end. (in my code I have it as the number of rows times 18+3 plus sum fixed amount. The overhead to do this calculation is zilch, but i then know where the number comes from.)