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?

Vaya con Dios,
Chuck, CABGx3

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! *

On Sat, 31 Jan 2004 13:34:23 +1100, Hank Scorpio
wrote:

No, I have no idea why I typed l - 257 rather than l - 256 in that
procedure either. But I'm sure you get my meaning.

It's lunch time, I'm hungry, that's my excuse, I'm sticking with it.

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! *

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?

Vaya con Dios,
Chuck, CABGx3


"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! *

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! *

Thanks Hank..........

Once again, your code worked perfectly the first time and does exactly what
I asked for.

Only thing is, my bad, for mis-understanding the cell character limits. I
guess I'm still hung over from the old days.......I heard that limit
somewhere and never had occasion to run up against it till now so I just
tucked it away.......but now things is better, so I guess I can raise the
256 to 1024, huh?.........I just don't want the users running up against the
situation of typing in more than they can see........this is a form that
others will use after they complete it.......

Anyway, I truly appreciate your kind responses and enlightening explanation.

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Hank Scorpio" wrote in message
...
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! *