sijpie
Well-known Member
- Joined
- Nov 1, 2008
- Messages
- 4,241
- Office Version
- 365
- Platform
- Windows
- MacOS
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>
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>