Sizing labels and form + labels font.name

[Fibo]

Hello,
I am using the code below to dynamicaly create labels in a form.
I have 2 issues:
Sizing of the form containing the labels:
I would like the height of the form to adjust with the number of labels. It seems that a height of 10 for a label is not the same height for a height of 10 for the form that contain the label.
Is there a way to force the same scale. (Blue bellow)

The .Font.Name (in red below) doesn't work although the lines above and after work perfectly.
I can write anything instead of "Courier new" it doesn't pop an error, nor does it change the font in the labels.
If I type msgbox .Font.Name after my line, it shows "Courier New" and my symbols are proportional which is not what I want.

Does any one have a idea?

Option Explicit
Private Sub UserForm_Activate()
Dim MyAmountTbl As Variant, NBCRecord As Variant, NBCRecordValue As Variant, MyDescTbl As Variant
Dim MyLabels() As Object
Dim MyLeft As Long, T As Long, LonguestStr As Long, MyWidth As Long, LabelCounter As Long, MyHeight As Long

MyLeft = 2: MyWidth = 300: MyHeight = 10

MyAmountTbl = Split(MyAmountTbl, ",")
MyDescTbl = Split(MyDescTbl, "|")

ReDim MyLabels(0 To UBound(MyAmountTbl))

With NBCMultipleBills 'Form
.Caption = "Detailed payement for: USD " & Format(NBCRecordValue(1, 6), "# ###.#0")
.Width = MyWidth + 14
.Height = 2 + ((UBound(MyAmountTbl) + 1) * MyHeight)
End With

LonguestStr = 0
For T = 0 To UBound(MyAmountTbl)
If Len(Format(MyAmountTbl(T), "# ###.#0")) > LonguestStr Then LonguestStr = Len(Format(MyAmountTbl(T), "# ###.#0"))
Next

For T = 0 To UBound(MyAmountTbl)

Set MyLabels(T) = NBCMultipleBills.Controls.Add("Forms.Label.1", "Test" & LabelCounter, True)
With MyLabels(T)
.Caption = WorksheetFunction.Rept(">", LonguestStr + 1 - Len(Format(MyAmountTbl(T), "# ###.#0")))
.Caption = .Caption & Trim(MyDescTbl(T))
.Width = MyWidth
.Font.Name = "Courier New"
.Font.Size = 8
.Left = MyLeft
.Height = MyHeight
.BackColor = VeryLightBlue
.Top = 2 + ((.Height + 3) * T)
End With

Next T
End Sub

 

[Anthony47]

Unfortunately the Height of a form include the form header, not only the useable area; I normally add 30 to take into account the header.
Also, you space the labels by 3 points so you need to add this spacing
I eventually set a fair height using

.Height = 30 + ((UBound(MyAmountTbl) + 1) * (MyHeight + 3))

As far as the font, is it possible that the name "Courier new" is not the one used in your installation?
Keep in mind that .Font.Name will return wichever string you set, does not mean it is correctly applied.
For example I set .Font.Name = "CourierCippa New" (that doesn't exist) and then when I asked for mylabels(t).font.name I got "CourierCippa New"

Try the following:
-type anything in a worksheet cell
-start recording a macro, then apply the "Courier new" font to that cell
-stop recording
Now examine the recorded code; you should see something like

    With Selection.Font
        .Name = "Courier New"
'etc etc

Use the string that you see here also for the the label

Try...

 

[Fibo]

Unfortunately the Height of a form include the form header, not only the useable area; I normally add 30 to take into account the header.
Also, you space the labels by 3 points so you need to add this spacing
I eventually set a fair height using

.Height = 30 + ((UBound(MyAmountTbl) + 1) * (MyHeight + 3))

As far as the font, is it possible that the name "Courier new" is not the one used in your installation?
Keep in mind that .Font.Name will return wichever string you set, does not mean it is correctly applied.
For example I set .Font.Name = "CourierCippa New" (that doesn't exist) and then when I asked for mylabels(t).font.name I got "CourierCippa New"

Try the following:
-type anything in a worksheet cell
-start recording a macro, then apply the "Courier new" font to that cell
-stop recording
Now examine the recorded code; you should see something like

    With Selection.Font
        .Name = "Courier New"
'etc etc

Use the string that you see

 

[Fibo]

Thank you for your response.
The height of the form worked very well.
Concernig the "Courier New", I did try many things amongst which recording a macro and copy paste.
I even tried to alternatively cahnge the font based on click count "Arial", "Courier New", "Arial"....
This is the result with the strict same String in 5 labels created with the code above
The last two lines look slightly bigger.
I think I will try with buttons and captions if I have the courage.

 

[Fibo]

Even more bizare when you consider that the picture bellow was created by the loop here below.

For T = 0 To UBound(MyAmountTbl)

Set MyLabels(T) = NBCMultipleBills.Controls.Add("Forms.Label.1", "Test" & LabelCounter, True)
With MyLabels(T)
.Caption = "12.00: no guest found in 'Room Revenues'"
.Width = MyWidth
.Font.Size = 8
.Font.Name = "Courier New"
.Left = MyLeft
.Height = MyHeight
.BackColor = VeryLightBlue
.Top = 3 + ((.Height + 3) * T)
End With

Next T

 

[Anthony47]

Try by reverting the sequence of the commands; ie Caption, Font.Name, Font.Size / Font.Name, Font.Size, Caption

 

[Fibo]

Nothing worked. I tried many permutations.
One thing worked. The Height has to be a multiple of 7.
I don't know why.

 

[Anthony47]

One thing worked. The Height has to be a multiple of 7

Do you mean "the font size?"

Based on your result I tried this code, that increments the font size:

StartSize = 6
For T = 0 To UBound(MyAmountTbl)
    Set MyLabels(T) = Me.Controls.Add("Forms.Label.1", "Test" & LabelCounter, True)
    With MyLabels(T)
        .Caption = "333 Size:" & (StartSize + T)
        'DoEvents
        .Width = MyWidth
        .Font.Size = StartSize + T              'Increasing Font size
        .Font.Name = "Courier New"
        .Left = MyLeft
        .Height = MyHeight + 7
        .BackColor = RGB(200, 200, 255)
        .Top = 2 + ((.Height + 3) * T)
    End With
Next T

Well, if StartSize = 6 then in my PC (Winn 11 & Office 365) the form fails to correctly represent size 9 and 12 (first image)
But if you start from StartSize = 5 then all the size (5 to 13) are correctly represented (second image)



In other words: you have to find which combination works for your situation

 

[Fibo]

Thank you. I will try this.