I am creating a template with multiple columns that will be populated by
others. Some of these columns have specific character limitations--in these
cases I'd like to prevent them from going over the character limit. I've
considered using validation, but there is an excellent chance that some of
the columns will be populated by copying/pasting, in which case validation
doesn't help. I thought vba might be the best bet. What I'd love to have is
this:

If the person types or copies a value that exceeds the character limit, I
would like a general message to display saying something like, "You have
exceeded the character limit. Entries that exceed the limit will be
highlighted and truncated." Then I would like that to happen--that is, to
have their entry truncated to the max character limit, and that cell
highlighted. I would like to have this work whether they pasted in one cell
or multiple cells at a time. Does anyone have any ideas how I could
accomplish this? Any guidance would be appreciated.

Thanks,

Jeff

Hi,

See if this gets you started. Checks for data entered into column A

HTH

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo wsexit
Application.EnableEvents = False
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Set rng = Target
For Each cell In rng
If Len(cell) 10 Then
MsgBox "You have exceeded the character length. Cell will be
truncated"
cell.Value = Left(cell.Value, 10)
Target.Interior.ColorIndex = 3
End If
Next
End If
wsexit:
Application.EnableEvents = True
End Sub

"goofy11" wrote:

I am creating a template with multiple columns that will be populated by
others. Some of these columns have specific character limitations--in these
cases I'd like to prevent them from going over the character limit. I've
considered using validation, but there is an excellent chance that some of
the columns will be populated by copying/pasting, in which case validation
doesn't help. I thought vba might be the best bet. What I'd love to have is
this:

If the person types or copies a value that exceeds the character limit, I
would like a general message to display saying something like, "You have
exceeded the character limit. Entries that exceed the limit will be
highlighted and truncated." Then I would like that to happen--that is, to
have their entry truncated to the max character limit, and that cell
highlighted. I would like to have this work whether they pasted in one cell
or multiple cells at a time. Does anyone have any ideas how I could
accomplish this? Any guidance would be appreciated.

Thanks,

Jeff

Just a heads up,
Unless it was Toppers intent to color the entire target if any cell in the
target exceeded the length limitation, then the following line

Target.Interior.ColorIndex = 3

should be changed to
Cell.Interior.ColorIndex = 3

to color only the offending cells within the cells changed

But perhaps "Target" was his intent.

also, if the only restriction was to column A, you could use

set rng = Intersect(Target, Range("A:A"))
rather than
set rng = Target

No intention to criticize Topper's Excellent suggestion, just some
additional refinement suggestions.

--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Hi,

See if this gets you started. Checks for data entered into column A

HTH

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo wsexit
Application.EnableEvents = False
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Set rng = Target
For Each cell In rng
If Len(cell) 10 Then
MsgBox "You have exceeded the character length. Cell will be
truncated"
cell.Value = Left(cell.Value, 10)
Target.Interior.ColorIndex = 3
End If
Next
End If
wsexit:
Application.EnableEvents = True
End Sub

"goofy11" wrote:

I am creating a template with multiple columns that will be populated by
others. Some of these columns have specific character limitations--in
these
cases I'd like to prevent them from going over the character limit.
I've
considered using validation, but there is an excellent chance that some
of
the columns will be populated by copying/pasting, in which case
validation
doesn't help. I thought vba might be the best bet. What I'd love to
have is
this:

If the person types or copies a value that exceeds the character limit,
I
would like a general message to display saying something like, "You have
exceeded the character limit. Entries that exceed the limit will be
highlighted and truncated." Then I would like that to happen--that is,
to
have their entry truncated to the max character limit, and that cell
highlighted. I would like to have this work whether they pasted in one
cell
or multiple cells at a time. Does anyone have any ideas how I could
accomplish this? Any guidance would be appreciated.

Thanks,

Jeff

Thanks Tom, it should be Cell not Target. Your comments are always welcome
.... that's how I learn!

"Tom Ogilvy" wrote:

Just a heads up,
Unless it was Toppers intent to color the entire target if any cell in the
target exceeded the length limitation, then the following line

Target.Interior.ColorIndex = 3

should be changed to
Cell.Interior.ColorIndex = 3

to color only the offending cells within the cells changed

But perhaps "Target" was his intent.

also, if the only restriction was to column A, you could use

set rng = Intersect(Target, Range("A:A"))
rather than
set rng = Target

No intention to criticize Topper's Excellent suggestion, just some
additional refinement suggestions.

--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Hi,

See if this gets you started. Checks for data entered into column A

HTH

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo wsexit
Application.EnableEvents = False
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Set rng = Target
For Each cell In rng
If Len(cell) 10 Then
MsgBox "You have exceeded the character length. Cell will be
truncated"
cell.Value = Left(cell.Value, 10)
Target.Interior.ColorIndex = 3
End If
Next
End If
wsexit:
Application.EnableEvents = True
End Sub

"goofy11" wrote:

I am creating a template with multiple columns that will be populated by
others. Some of these columns have specific character limitations--in
these
cases I'd like to prevent them from going over the character limit.
I've
considered using validation, but there is an excellent chance that some
of
the columns will be populated by copying/pasting, in which case
validation
doesn't help. I thought vba might be the best bet. What I'd love to
have is
this:

If the person types or copies a value that exceeds the character limit,
I
would like a general message to display saying something like, "You have
exceeded the character limit. Entries that exceed the limit will be
highlighted and truncated." Then I would like that to happen--that is,
to
have their entry truncated to the max character limit, and that cell
highlighted. I would like to have this work whether they pasted in one
cell
or multiple cells at a time. Does anyone have any ideas how I could
accomplish this? Any guidance would be appreciated.

Thanks,

Jeff