On Sat, 31 Jan 2004 16:38:36 -0500, "CLR"
wrote:

Thank you Hank..........
The solution you offered works perfectly........exactly as I desired.

The only question I have now, is that since my "cell" is actually A63:G69
merged as a single cell, it in fact WILL display over the 256 characters I
thought was the limit..........what's up there?.........do I get 256 for
every cell in the merge?

Sorry, I'm not quite following you. I understood that 256 characters
was a limit that YOU wanted to impose for whatever reason. No cell,
whether individual or merged, has an Excel-imposed 256 character
limit. The actual limits are (from "Excel Specifications and Limits"
in the help file), "32,767 characters. Only 1,024 display in a cell;
all 32,767 display in the formula bar." (Disclaimer 1; I once saw a
post from Harlan which used the Rept function and other wizardry to
create an entry in a cell with more characters than that. While it was
an amusing circus trick and interesting as all get out from a "pushing
the envelope" point of view, I've yet to see a practical use for such
a cell entry. For most practical purposes, you may regard the limits
as stated in the help file as being "real". Disclaimer 2: Versions of
Excel PRIOR TO Excel 97 apparently DID have a 255 character limit for
the cells. This is mentioned in the "What's new with specifications
and performance" section of the Excel 97 Help file.)

A further limit stated in the help file is that a column width maxes
out at 255 characters BUT that doesn't take into account text wrapping
as set through the Format Cells dialog; it's just the actual column
width.

As far as the code in the Worksheet_Change procedure goes, it sees the
merged range Target variable as just a single cell. You can test this
by adding the line MsgBox Target.Address to the procedure; it will
return the address $A$63.

Hope this helps; if not, please post again with more details.

"Hank Scorpio" wrote in message
.. .
On Fri, 30 Jan 2004 21:07:14 -0500, "CLR"
wrote:

Hi All..........

Is it possible to have a Change Event macro count how many characters
have
just been typed in the cell and if over 256, put up a Error Message
specifying how many characters need to be reduced, in order for all to be
displayed?

I don't know whether you meant "just typed" literally (as in, if the
user had edited a pre-existing entry, for example), or whether you
just mean the number of characters entered in the cell. If it's the
latter, the following seemed to work for me (regardless of whether the
entry was text or numeric):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim l As Long

l = Len(Target.Value)

If l 256 Then
Beep

MsgBox "Too many characters! (256 maximum.) " _
& "You need to reduce the cell entry " _
& "by " & l - 257 _
& IIf(l - 257 = 1, " character.", " characters"), _
vbCritical

Target.Select

End If

End Sub

If it's the former, I doubt that you could do it short of storing the
original text in a global variable (perhaps on the Selection Change
event) and then comparing that with the resulting text after the
Change event occurs. Hopefully that's not what you were getting at,
though.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *



---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *