Limiting a line in an Excel Form I have created a form in Excel. On the line I a
ID: 3560628 • Letter: L
Question
Limiting a line in an Excel Form
I have created a form in Excel. On the line I am asking for an input in the cell. I would like the cell to be only a certain length. In other words only allows a certain amount of text to be entered into it so it will not run pass the border.
How do I limit the cell so when someone enters information it does not go outside the space past the border. I do not want to use a word wrap and go onto the next line but only limit it to that one line.
Enter reason for denial: I want text typed here but not go pass the end of the border.Explanation / Answer
*&(*&^ If you used code to automatically truncate the characters you would eliminate the error message from Data Validation.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B10" 'edit to suit
' "A1,A2,B1,C5,C6" for a non-contiguous range example
Dim cell As Range
Dim oval As String
oval = Target.Value
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(oval) > 20 Then 'revise the 20 to suit
.Value = Left(oval, 20)
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Right-click on the worksheet tab and select "View Code"
Paste code into the module, alt + q to return to the worksheet.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.