Active x textbox not increasing in size with additional lines of text

[dpaton05]

I have an active x text box that will not increase in size for extra lines of text that are added.

I want it to be a set length, 540 and to increase in size downwards when additional lines of text are added.

This is the code that someone helped me with, can someone tell me what I need to change to get it to work please?

Private Sub TextBox1_Change()
TextBox1.Height = TextBox1.Height
TextBox1.Width = 540
End Sub


Private Sub TextBox1_GotFocus()
  TextBox1.Width = 540
  If TextBox1 = "Please type notes here" Then
    TextBox1 = ""
  End If
End Sub


Private Sub TextBox1_LostFocus()
  TextBox1.Height = TextBox1.Height
  TextBox1.Width = 540
  If TextBox1 = "" Then
    TextBox1 = "Please type notes here"
  End If
End Sub

Thanks guys

 

[dpaton05]

Got it to work, I used code from post 19 from this thread.

https://www.mrexcel.com/forum/excel...s-pushes-other-things-spreadsheet-down-2.html

 

[dpaton05]

Now with the new code, the text "Please types notes here won't appear when the text box is empty. here is the new code.

Private Sub TextBox1_Change()
    Dim hBox As Double, h As Double, h5 As Double, H6 As Double
    h5 = Me.Rows(5).RowHeight
    H6 = Me.Rows(6).RowHeight
    
    With Me.Shapes("TextBox1")
        hBox = .Height
        .Top = Me.Rows(4).Top + 10
    End With
    h = hBox - h5 - H6
    If h > 0 Then
        Me.Rows("7:8").RowHeight = h / 2
    Else
        Me.Rows("7:8").RowHeight = 0
    End If
End Sub

Can someone please help?

 

[My Aswer Is This]

I looked at your question but really do not have a good answer for you.

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

 

[Yongle]

Auto resizing can be accomplished thus:

Private Sub TextBox1_Change()
    Call Size(TextBox1)
End Sub

Private Sub TextBox1_GotFocus()
    Call Size(TextBox1)
End Sub

Private Sub TextBox1_LostFocus()
    Call Size(TextBox1)
End Sub

Private Sub Size(tb As Object)
    With tb
        .AutoSize = True
        .Width = 540
        .EnterKeyBehavior = True
        .MultiLine = True
    End With
End Sub

For clarity, toggling the contents of the textbox is ignored above

 

[My Aswer Is This]

That worked for me.
I learned something today.

Auto resizing can be accomplished thus:

Private Sub TextBox1_Change()
    Call Size(TextBox1)
End Sub

Private Sub TextBox1_GotFocus()
    Call Size(TextBox1)
End Sub

Private Sub TextBox1_LostFocus()
    Call Size(TextBox1)
End Sub

Private Sub Size(tb As Object)
    With tb
        .AutoSize = True
        .Width = 540
        .EnterKeyBehavior = True
        .MultiLine = True
    End With
End Sub

For clarity, toggling the contents of the textbox is ignored above

 

[dpaton05]

That is good to know, I will try it when I am back at work tomorrow.