Userform Textbox - Shrink to fit?

[lwarren30]

Hello,

Is there a way to set a textbox to shrink to fit text? Like how you do normal cells in Excel. Or a way to change the text font.

Thanks

 

[My Aswer Is This]

Click on the TextBox
In the properties window choose AutoSize= True

And in the Properties window click on Font and you can change the font size and a whole lot more

 

[lwarren30]

This will autosize the textbox. I want to keep the textbox the same size and the font to change per the size of the textbox. The same thing that happens for shrink to fit for cells.

 

[My Aswer Is This]

This will autosize the textbox. I want to keep the textbox the same size and the font to change per the size of the textbox. The same thing that happens for shrink to fit for cells.

You subject title says this:

Userform ​

Textbox​

- Shrink to fit?​

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[mikerickson]

This code will adjust the font size of a textbox to fit its given height and width.

Private Sub TextBox1_Change()
    FontToFit TextBox1
End Sub

Sub FontToFit(txtBoxToFit As MSForms.TextBox)
    Dim oHeight As Single, oWidth As Single
    With TextBox1
        oHeight = .Height: oWidth = .Width
        .AutoSize = True: .AutoSize = False
        Do Until (oHeight <= .Height) And (oWidth <= .Width)
            .Font.Size = .Font.Size + 1
            .AutoSize = True: .AutoSize = False
        Loop
        Do Until (.Height <= oHeight) And (.Width <= oWidth)
            .Font.Size = .Font.Size - 1
            .AutoSize = True: .AutoSize = False
        Loop
        .Height = oHeight: .Width = oWidth
    End With
End Sub

 

[lwarren30]

That worked... maybe too well. The text is so small you can't read it.

 

[mikerickson]

You could change this bit to set a minimum acceptable font size (10 in this example)

Do Until ((.Height <= oHeight) And (.Width <= oWidth)) Or (.Font.Size <= 10)
    .Font.Size = .Font.Size - 1
    .AutoSize = True: .AutoSize = False
Loop

 

[lwarren30]

How would I incorporate that into the code?

 

[mikerickson]

Make that the decreasing loop in place of the existing one.