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