Private Sub Workbook_SheetChange

[Robert]

Hello,
I expect to use the Private Sub Workbook_SheetChange event to prevent the
user from entering a text longer than 15 characters in a specific cell.

How can I do this?
Thanks for your help!

 

[Mike H]

Hi,

Why don't you use dat validation instead

Select your cells then
Data|Validation
Select Text length
Select less than equal to
15

Mike

 

[Robert]

Hi Mike,

Thanks a lot for the idea!

Hi,

Why don't you use dat validation instead

Select your cells then
Data|Validation
Select Text length
Select less than equal to
15

Mike

 

[Gord Dibben]

Since you posted in programming................

You could set up event code to truncate anything over a certain number of
characters after user hits ENTER key

No message, no retyping.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
' "A1,A2,B1,C5,C6" for a non-contiguous range example
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
.Value = Left(.Value, 20)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that module, Edit the range to suit. Alt + q to return to
the Excel window.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Change 20's to 15

Gord

Since you posted in programming................

You could set up event code to truncate anything over a certain number of
characters after user hits ENTER key

No message, no retyping.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
' "A1,A2,B1,C5,C6" for a non-contiguous range example
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
.Value = Left(.Value, 20)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that module, Edit the range to suit. Alt + q to return to
the Excel window.


Gord Dibben MS Excel MVP