Excel VBA Textbox font size

Question

I have a userform to display my terms and conditions which is picked up directly from a cell. Upon Activation I call TandC.Text = Worksheets("Master").Range("L41") which works perfectly when I run the form directly -- correct font size, multiple lines, word wrap, etc. However, when I run the form from another userform, the text box text suddenly displays the text in a tiny unreadable font. Even when I put a test button on my form to set the font size to 28, it doesn't work when the form is kicked off from another form; however, it does change it when it's running on its own.

It appears to be an issue when Multiline is turned on, I turn it off and the font is the correct size but the text spans for miles to the right. I turn it back on and again I have a tiny unreadable font.

It's driving me insane, can anyone offer any advice on this?

See below my code, kicking the form off from the originating form is just a simple UF.Show.

Mine is really similar other then in my initialize are i have some code that centralizes the form on the screen if you have multiple monitors.

Private Sub CommandButton2_Click()
TandC.WordWrap = True
TandC.Font.Size = 8
TandC.MultiLine = True

End Sub

Private Sub UserForm_Initialize()

Dim TopOffset As Integer
Dim LeftOffset As Integer
TopOffset = (Application.UsableHeight / 2) - (Me.Height / 2)
LeftOffset = (Application.UsableWidth / 2) - (Me.Width / 2)
Me.Top = Application.Top + TopOffset
Me.Left = Application.Left + LeftOffset

TandC.Text = Worksheets("MasterData").Range("L21")
TandC.WordWrap = True
TandC.MultiLine = True
TandC.Font.Size = 8

End Sub

Answer 1

For anyone coming to this question from Google and wondering how to change the font size of a textbox or label control, since only the Font family appears to be configurable

There is a small button with ... just to the right of the font family name - click it.

Otherwise, you can always use code:

TextBox1.Font.Size = 18
Label1.Font.Size = 32

Answer 2

I've been struggling with this for the last few hours, but finally found a solution. But first, I found that clicking inside the textbox is what was triggering the font size enlargement. To test it, I repeatedly clicked in the textbox, then clicked on a different cell; I did this 4 or 5 times and quickly realized this was the culprit. The font size increases in the standard MS Office increments (8, 9, 10, 11, 12, 14, etc.).

I also found that the only thing that resets the font size to what it should be was resizing the textbox itself. For some reason, that forces it to reload the font size set in the properties menu, regardless of what is in the VBA code. Once I figured that out, all I had to do was write some simple code that changes the height of the textbox by a pixel or two, then the next line of code changed the height back to its original value. Here's my code:

Private Sub Textbox1_Change()
Textbox1.Font.Size = 10
Textbox1.Height = 20
Textbox1.Height = 18
End Sub

Bam. Problem solved. This code should run every time the value in the textbox's linked cell changes. Hope this helps someone else!

Answer 3

I had the same problem. In my case none of the solutions above worked. But when I changed the top position of the textbox (e.g. Top 42 instead of Top 45) the font appeared correctly.

Answer 4

Just an addendum to what was said here for Word users facing the same problem.

I was experiencing the same issue and it seems the property TextBox.WordWrap was the problem.

Here is how you can solve this for Word users:

1. Turn off the WordWrap property of the Text Box in the graphic interface/

2. Add something like this:

   Private Sub TextBox9_Change()
       TextBox9.WordWrap = True
   End Sub
   

It is a workaround, but does not necessarily solve the problem.

Answer 5

This has been plaguing me ever since I upgraded to 2013 earlier this year. After many attempts and utilizing what is in this trail along with other investigations, I found a solution that works. The problem is that "focus" needs to be set as well. So the final coding that worked for me is

TandC.TextBox1.SetFocus
TandC.TextBox1.MultiLine = False
TandC.TextBox1.MultiLine = True
TandC.TextBox1.WordWrap = False
TandC.TextBox1.WordWrap = True

Thanks everyone. :)

Answer 6

I solved the problem in a different way:

I created two TextBoxes and filled them with the same content. While running the code I observed which of the two Textboxes showed the unreadable font. On runtime I turned Visibility of that textbox to False.

It may take some trial and error, because the position of the Textboxes on the userform seems to matter.

Another option is to create a second (identical) textbox and position the one with the wrong fontsize outside the userform.

Be sure to put the same content in both textboxes and make all attributes identical: enterkeybehavior, multiline, wordwrap

Answer 7

Had the same issue and this fixed it.

Private Sub TextBox1_Change()
    Me.txt_Body.WordWrap = False
    Me.txt_Body.WordWrap = True
End Sub

Answer 8

My program, using visual basic within Excel, works out data then presents it in a textbox. It works perfectly in Excel 2010 but shows only unreadable pixel-high output in Excel 2013 regardless of how the text size is set in the textbox properties section.

Using the above comments I found that turning the multiline property off and on immediately before the data is displayed served as a workaround for this infuriating problem.

My data was collected in a string variable called 'answer', so the final lines of my program read as follows:

TextBox3.Multiline = False
TextBos3.Multiline = True
TextBox3.Value = answer

End Sub

Answer 9

Try turning on autosize for your text box. Looks like multi-line is trying to fit all text into your text box by adjusting the font size rather than adjusting the box size. You can also try setting the box height.

TextBox1.AutoSize = True

---

You can try this too (from MSDN ):

textBox1.Height = textBox1.PreferredHeight

---

I tried to duplicate your problem by calling one form from another form and even used a button to dynamically update the text size. I haven't been able to observe your issue. Here is my test code:

Behind userform1

Private Sub CommandButton1_Click()
    TextBox1.Font.Size = 11
End Sub

Private Sub UserForm_Initialize()
    TextBox1.Text = Range("A1")
    TextBox1.WordWrap = True
    TextBox1.MultiLine = True
    TextBox1.Font.Size = 28
End Sub

Behind userform2

Private Sub CommandButton1_Click()
  UserForm1.Show
End Sub

---

EDIT

I found this post which talkes about others having the same issue in Excel 2013. One person wrote that toggling the wordwrap back and forth in the getfocus event solve the issue. I don't have 2013 so I can't test it unfortunately.

You can try it inside the initialize event or try an activate event like this:

Private Sub UserForm_Activate()
    TandC.WordWrap = False
    TandC.WordWrap = True
End Sub