Limiting text length

[patsy]

I need to know if it is possible to have a cell with a limited text length of
95 automatically go to the next line when the user has reached the limit. In
other words, I want the user to be able to keep typing without hitting enter
and receiving the message "you have exceeded the limit that the user set". I
would appreciate any help.

Thanks

 

[stew]

Data:Validation and From the drop down list chosse text length.

Your messages can be entered at this point as well

Stew

 

[Gord Dibben]

Excel has no way of knowing when you have finished typing in a cell until
you hit Enter or Tab or arrow out of the cell.

VBA code won't run while you are in Edit mode.

About the best you can do is hit Enter and then let Excel count the nunmber
of characters and move any past 95 to another cell.

No message, just move the extra characters to the cell below.

This can be done.......if you want code post back.

Question. Is the limit due to the need for 95 char limit for export to
another application or what?


Gord Dibben MS Excel MVP

 

[Steve Edgar]

Gord,
I would like to know how to move excess text from my cell in the
fist sheet to my second sheet where I already have a cell setup to recieve
it. How is it done?
Thanks

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" 'edit to suit
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) > 20 Then
Sheets("Sheet2").Range("A1").Value = _
Right(.Value, Len(.Value) - 20)
.Value = Left(.Value, 20)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.

Edit to suit. Alt + q to return to the Excel window.


Gord