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!

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" wrote:

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!

Hi Mike,

Thanks a lot for the idea!


"Mike H" a écrit dans le message de news:
...
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" wrote:

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!

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

On Fri, 17 Oct 2008 19:19:11 +0200, "Robert" wrote:

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!

Change 20's to 15

Gord

On Fri, 17 Oct 2008 11:32:51 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Fri, 17 Oct 2008 19:19:11 +0200, "Robert" wrote:

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!