Resize Userform TextBox when it losses focus

[julhs]

I’m successfully using the below to increase the size of a "txtCommentBox" on the UserForm so I can see the full TextBox input values.
That’s fine and does what I wanted, however the enlarged TextBox is then obscuring/covering over parts of the UserForm which is causing a hassle.
How can I down size the "txtCommentBox" when “Focus” is lost/shifted from the TextBox to another CommandButton on the UserForm

     'This automatically changes the size of the "txtCommentBox" to fit the text that’s being entered into it.
Private Sub txtCommentBox_Change()
On Error Resume Next
              'Can change the " * 15" to suit your needs
txtCommentBox.Height = txtCommentBox.LineCount * 15
On Error GoTo 0
End Sub

 

[JEC]

Like this?

Private Sub TextBox1_Change()
TextBox1.Height = TextBox1.LineCount * 15
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Height = 10
End Sub

 

[julhs]

Thank you JEC.

Ok maybe I didn’t necessarily need the “Error” handling going by my testing to date, but thought it was just good practice?
Crucial part for me of that you have provided is;

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Height = 10
End Sub

After G** know’s how long, I can eventually put this single TextBox to bed!!!
Thanks again

 

[JEC]

You're welcome!

 

[julhs]

Afraid I’m going to have to come back to you on this one for a further step.

With what you gave me the "txtCommentBox" is nicely reduced.
However I’ve now realised that IF I need to go back and edit the "txtCommentBox" later, it’s going to be a pain to do just using the ScrollBar feature on the TextBox.

How can it be done that WHEN "txtCommentBox" has “Focus” again that it resizes back to its original enlarged size.
I’m stumbling around with a way to do it!!

 

[mikerickson]

You could put sizing code into the Enter event that puts a space at the end of the text (triggering the Change event's height adjustement routine) and then takes it away.

Private Sub TextBox1_Enter()
    With TextBox1
        .Text = .Text & " "
        .Text = Left(.Text, Len(.Text)-1)
    End With
End Sub

 

[julhs]

Thank you Mike
That has done exactly what I wanted.