Userform Textbox - Shrink to fit?

lwarren30

New Member
Joined
Jul 3, 2018
Messages
35
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
This code will adjust the font size of a textbox to fit its given height and width.

Code:
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
 
Upvote 0
You could change this bit to set a minimum acceptable font size (10 in this example)

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

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top