Code to set text length in cell

[Sandy]

I need to be able to control the text length in either a cell or in a group
of merged cells (i.e. E22:G24, but merged).

Data Validation doesn't return an error message until you hit the enter
button. I would like something to return an error message if, for example,
the maximum length is 20, then when the 21st character is hit, the message
box pops up.

Is this possible?

 

[Mike Fogleman]

I don't believe you can do that. VB is suspended while a cell is in edit
mode. Once the contents has been "Entered", then VB can deal with it. Until
then, Excel considers the cell empty.
Mike F

 

[jashburn13]

You can use data validation for this. Go to Data/Validation and under
allow, pick text length.

 

[Tom Ogilvy]

Data Validation doesn't return an error message until you hit the enter

 

[Peter T]

Hi Sandy,

An idea, barely tested. Add an ActiveX Textbox to your sheet, properties
BorderStyle = 1, maybe change the border colour, exit design mode. Don't
worry about position, size and visible. Select E22 and type some long text,
press Enter when done.

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim s As String
s = TextBox1.Text
If Len(s) > 5 Then
TextBox1.Text = Left(s, 10) ' change 10 to 20
ElseIf KeyCode = 13 And Shift = 0 Then
TextBox1.Visible = False
Me.Range("$D$22").Activate
Else
Me.Range("$E$22").Value = s
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target(1).Address = "$E$22" Then
With Target(1).MergeArea
TextBox1.Left = .Left
TextBox1.Width = .Width
TextBox1.Height = .Height
TextBox1.Top = .Top
End With
TextBox1.Text = Target(1).Value
TextBox1.Visible = True
TextBox1.Activate
ElseIf TextBox1.Visible Then
TextBox1.Visible = False
End If
End Sub



Just a thought

Regards,
Peter T