Userform Text box info

[windsurfit]

Hi,

I'm using text boxes in a Userform, and I want the text/data being entered into the box setup to wrap within the box, rather than continuing to go to the right after the entry has exceeded the length of the box.

Basically a way to add data of any length to the text box, even written sentences, but will wrap once the right margin/border is "hit". My Text box is being used as a customer contact notes box entry and is large enough to accommodate multiple sentences of data. The large data entry seems to display fine on the actual sheet with the Wordwrap checked under sheet format so that is good.

FYI-Under the specific Text box properties... I already have the Multiline set to "true" and the Wordwrap also set to true but this doesn't accomplish it. My max Length characters is set at 95 for the width of the Userform Text box single line size.


Many Thanks, Jeff

 

[mikerickson]

With .MultiLine = True and .WordWrap = True, I'd expect the behavior that you desire. The .MaxLength property should be set to 0. (i.e. any length allowed)

 

[windsurfit]

Ok, thanks... that corrects it in the Userform as I hoped...

however, I now see that when the data is brought into the sheet.. it runs (out of bounds) of the width of my desired cell. I had formatted the whole sheet but the format condition goes away when the Userform transmits the data. This code was provided to me for the Userform and unfortunately I am not savvy enough to see where the problem is?

Any thoughts?- working code below

Private Sub cmdAdd_Click()
'dimention the variable
Dim ws As Worksheet
Dim addme As Range
'on error statement
On Error GoTo errHandler:
'set the variable
Set ws = Sheet2
'set variable for the destination
Set addme = Sheet2.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)
'hold in memory
Application.ScreenUpdating = False
'check for values
If Me.txtDate.Value = "" Or Me.cboCompany.Value = "" Or Me.txtAmount.Value = "" Then
MsgBox "There is insufficient data. Madatory fields must be added (*)", vbExclamation, "Mandatory fields are incomplete"
Exit Sub
End If
'check for proper date
If Not IsDate(txtDate.Value) Then
MsgBox " The date field must be a proper date", vbExclamation, "Date format error"
Me.txtDate.Value = ""
Me.txtDate.SetFocus
Exit Sub
End If
'send the values to the database
With ws
'set the date format to suit your area
addme.Value = Format(txtDate.Value, "mm/dd/yy")
addme.Offset(0, 1).Value = Me.cboTaxPayer.Value
addme.Offset(0, 2).Value = Me.cboCompany.Value
addme.Offset(0, 3).Value = Me.txtDescription
addme.Offset(0, 4).Value = Me.cboCategory
addme.Offset(0, 5).Value = Format(Me.txtAmount.Value, "$##,###.00")
'add the selected option
If OPT1.Value = True Then
addme.Offset(0, 6).Value = "Paper Copy"
ElseIf OPT2.Value = True Then
addme.Offset(0, 6).Value = "Scanned Copy"
Else
addme.Offset(0, 6).Value = ""
End If
addme.Offset(0, 7).Value = cboLocation
addme.Offset(0, 8).Value = txtRemarks

End With
'sort the data
Sheet2.Select
With Sheet2
Sheet2.Range("D4:L10000").Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlGuess
End With
'return to sheet
Sheet1.Select
'reset the form
Unload Me
frmTax_Time.Show
'update the sheet
Application.ScreenUpdating = True

Exit Sub
'error block
errHandler:
MsgBox "An Error has Occurred " & Chr(10) & _
"The error number is: " & Err.Number & Chr(10) & _
Err.Description & Chr(10) & "Please notify the administrator"

End Sub

 

[My Aswer Is This]

Please explain in words what your wanting to do. If you did not write this script then it may not be doing what you wanted even if you could get it to work.
You said this code was provided to me. So that makes me think you really do not understand what it is trying to do.

Best way to help you is to understand what your wanting to do.

Please provide specific details like sheet names column numbers like 1 or 10
Do not say copy to the other sheet or column Cost.​