When more than 256 characters have been entered into a cell I would like to
be warned of this by means of a message. When coping a workbook to a new
location where cells contain more than 256 characters these extra characters
are lost in the process. If i know in advance of the cells (of which there
will be many) I can at least divide the contents into two cells. Perhaps
conditional formatting could be applied here, what do you think?

Pat

You get the warning if you do it manually--but not via code.

If you think (or you'r not sure) that you have a worksheet that has more than
255 characters, then copy the sheet (just to get all the
formatting/controls/filters/pagesetup...).

Then go back and copy the cells and paste them to the newly created sheet.

Option Explicit
Sub testme()

Dim wksToCopy As Worksheet
Dim NewWks As Worksheet

Set wksToCopy = Worksheets("sheet1")
wksToCopy.Copy _
after:=wksToCopy

Set NewWks = ActiveSheet

wksToCopy.Cells.Copy _
Destination:=NewWks.Range("a1")

End Sub

I would think that this would be much faster than looking through each cell to
find the maximum length of a cell containing text.


Pat wrote:

When more than 256 characters have been entered into a cell I would like to
be warned of this by means of a message. When coping a workbook to a new
location where cells contain more than 256 characters these extra characters
are lost in the process. If i know in advance of the cells (of which there
will be many) I can at least divide the contents into two cells. Perhaps
conditional formatting could be applied here, what do you think?

Pat

--

Dave Peterson

Pat,

CF would indeed work. Paste this into the formula box (assuming the CF is
for A1) and copy the formatting:

=LEN(A1) 256

hth,

Doug

"Pat" wrote in message
...
When more than 256 characters have been entered into a cell I would like
to
be warned of this by means of a message. When coping a workbook to a new
location where cells contain more than 256 characters these extra
characters
are lost in the process. If i know in advance of the cells (of which
there
will be many) I can at least divide the contents into two cells. Perhaps
conditional formatting could be applied here, what do you think?

Pat

How about Data - Validation (text length). Depending what you want exactly
this could be an option...
--
HTH...

Jim Thomlinson


"Pat" wrote:

When more than 256 characters have been entered into a cell I would like to
be warned of this by means of a message. When coping a workbook to a new
location where cells contain more than 256 characters these extra characters
are lost in the process. If i know in advance of the cells (of which there
will be many) I can at least divide the contents into two cells. Perhaps
conditional formatting could be applied here, what do you think?

Pat