Execute warning 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

Execute warning message
 

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

Execute warning message
 

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

Execute warning message
 

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