limit character length when copying or typing

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

Toppers, thanks a bunch! This code looks to be exactly what I need. I tried
setting this up but have run into a problem. I am a newbie to vba and I'm
having trouble running this code. First of all, does this code automatically
run each time changes are made to the worksheet, or does the user need to run
the macro after they are done populating it? Second, I opened the Visual
Basic Editor and copied your code (with the change made by Tom) into a new
module in the workbook. When I attempt to run the code by going to
Tools>Macros>Macros, it does not appear in "This Workbook" or "All Open
Workbooks". When I reopen the VB Editor, it shows up in my module. With the
VB Editor open with the module active, I can't even run it. When I press
run, it asks me to create a new macro. This seems really weird, but I
havn't even been able to test the code. Do you have any ideas?

Jeff



"Toppers" wrote:

> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

Right click on the sheet where you want the behavior. Select View Code.
This takes you to the sheet module.

At the top of the resulting module
In the left dropdown, select Worksheet and in the right dropdown select
Change (not selection change)

this will add the declaration for the change event.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

End Sub


As you can see, this is the event that Topper used. It fires whenever a
cell is edited or data is pasted. You can now delete the Change declaration
that was entered by excel and paste in Topper's code.

You don't have to do the dropdowns to paste in the code - I just when
through that description as a form of instruction.

For general information on events, see Chip Pearson's page on them

http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy



"goofy11" <[email protected]> wrote in message
news:[email protected]...
> Toppers, thanks a bunch! This code looks to be exactly what I need. I
tried
> setting this up but have run into a problem. I am a newbie to vba and I'm
> having trouble running this code. First of all, does this code
automatically
> run each time changes are made to the worksheet, or does the user need to
run
> the macro after they are done populating it? Second, I opened the Visual
> Basic Editor and copied your code (with the change made by Tom) into a new
> module in the workbook. When I attempt to run the code by going to
> Tools>Macros>Macros, it does not appear in "This Workbook" or "All Open
> Workbooks". When I reopen the VB Editor, it shows up in my module. With
the
> VB Editor open with the module active, I can't even run it. When I press
> run, it asks me to create a new macro. This seems really weird, but I
> havn't even been able to test the code. Do you have any ideas?
>
> Jeff
>
>
>
> "Toppers" wrote:
>
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >