Chracter Limit Help!!
 

I have 2 issues.

1 - I have a protected form for people to fill out. It will NOT be
unprotected by the users to complete. I have a merged cell (almost a full
page) and when text is entered and is below 1024 characters, all you see is:
########## Why is this?

This leads into my other issue since 1024 is the limit.
2 - Below is a copy of an answer to a question regarding the 1024 character
limit in a cell. The question was from earlier this month (original post
date). Not knowing how often old posts are looked at for questions I have
pasted the answer here.

"Kevin B" wrote:

There is a 32,767 character limit per cell, with only 1,024 displayed. You
can extend the 1,024 limit by forcing line breaks with <Alt + Enter if
typing, or vbNewLine constant by code.


I don't know how to apply the "vbNewLine constant by code". Can someone
please point me in the right direction? I need the ability to show/see as
many characters as possible. Somewhere around 6k.

Thanks

Chracter Limit Help!!
 

Try formatting the cell as General (really anything but Text).

Excel has trouble showing the text when the string length is between 255 and
1024 and the format of the cell is Text.

It doesn't sound like you want to use the suggestion about using code. But if
you wanted to wrap lines within a cell using a macro, you could use:

dim myStr as string
mystr = "hello there" & vbnewline & "how you doing," & vbnewline & "Matt?"
activesheet.range("a1").value = mystr

But if your users are typing more than 1024 characters in that cell, you'll want
to tell them to use alt-enters to force newlines in the cell every 80-100
characters.

If they don't do that, they won't be able to see much beyond that 1000 character
limit.

Matt wrote:

I have 2 issues.

1 - I have a protected form for people to fill out. It will NOT be
unprotected by the users to complete. I have a merged cell (almost a full
page) and when text is entered and is below 1024 characters, all you see is:
########## Why is this?

This leads into my other issue since 1024 is the limit.
2 - Below is a copy of an answer to a question regarding the 1024 character
limit in a cell. The question was from earlier this month (original post
date). Not knowing how often old posts are looked at for questions I have
pasted the answer here.

"Kevin B" wrote:

There is a 32,767 character limit per cell, with only 1,024 displayed. You
can extend the 1,024 limit by forcing line breaks with <Alt + Enter if
typing, or vbNewLine constant by code.


I don't know how to apply the "vbNewLine constant by code". Can someone
please point me in the right direction? I need the ability to show/see as
many characters as possible. Somewhere around 6k.

Thanks

--

Dave Peterson

Chracter Limit Help!!
 

"Dave Peterson" wrote:

Try formatting the cell as General (really anything but Text).

I do have the cell as general.

Excel has trouble showing the text when the string length is between 255 and
1024 and the format of the cell is Text.

It doesn't sound like you want to use the suggestion about using code. But if
you wanted to wrap lines within a cell using a macro, you could use:

It was my understanding from the post I quoted that the "vbNewLine constant"
was supposed to handle the alt-enter for the user.

dim myStr as string
mystr = "hello there" & vbnewline & "how you doing," & vbnewline & "Matt?"
activesheet.range("a1").value = mystr

OK. How can this be used for user entered text instead of the text you
entered in quotes?

But if your users are typing more than 1024 characters in that cell, you'll want
to tell them to use alt-enters to force newlines in the cell every 80-100
characters.

I have to make it easy as possible to use or it won't be used. Trying to
have them remember to alt-enter is the same as having them print the 1st 2
pages out of 7 (5 unused) by going to file/print or alt-p. They still print
5 pages of unused paper.

If they don't do that, they won't be able to see much beyond that 1000 character
limit.

That is what I am after. Some reports get rather long and complicated.

Thanks

Chracter Limit Help!!
 

The only time I've seen excel display #### with long text is when the cell is
formatted as text--as soon as I format it as General, I can see the text (maybe
not all of them). I'd suggest that you test again for this.

I still think that it becomes another training issue. You tell the user that if
he/she can't see the complete text, then adding alt-enters every 80-100
characters can help.

The vbNewLine stuff should actually be vblf (visual basic Line Feed) will only
help if you update the cell via code. It won't handle anything unless you have
a program that tries to insert those characters.

You may be able to use an event that tries to insert those newline characters in
a cell with an event macro like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cCtr As Long
Dim pCtr As Long
Dim myStr As String

On Error GoTo ErrHandler:

With Target
If .Cells.Count 1 Then Exit Sub
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub
If .HasFormula Then Exit Sub
If Len(.Value) < 1000 Then Exit Sub

myStr = .Value
'get rid of existing vblf's, so that we can add our own
myStr = Replace(myStr, vbLf, " ")

cCtr = 80
pCtr = 0
Do
If Mid(myStr, cCtr + pCtr, 1) = " " Then
Mid(myStr, cCtr + pCtr, 1) = vbLf
cCtr = cCtr + pCtr + 80
pCtr = 0
Else
pCtr = pCtr + 1
End If

If cCtr = Len(.Value) Then
Exit Do
End If
Loop
Application.EnableEvents = False
.Value = myStr
End With

ErrHandler:
Application.EnableEvents = True

End Sub



If you want to try this, then right click on the worksheet tab that should have
this behavior. Select View Code and paste it into the code window that just
opened.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=========
If you don't want to go to this trouble (I would make it a training issue), you
could insert a Textbox from the Drawing toolbar or from the Control toolbox
toolbar.




Matt wrote:

"Dave Peterson" wrote:

Try formatting the cell as General (really anything but Text).

I do have the cell as general.

Excel has trouble showing the text when the string length is between 255 and
1024 and the format of the cell is Text.

It doesn't sound like you want to use the suggestion about using code. But if
you wanted to wrap lines within a cell using a macro, you could use:

It was my understanding from the post I quoted that the "vbNewLine constant"
was supposed to handle the alt-enter for the user.

dim myStr as string
mystr = "hello there" & vbnewline & "how you doing," & vbnewline & "Matt?"
activesheet.range("a1").value = mystr

OK. How can this be used for user entered text instead of the text you
entered in quotes?

But if your users are typing more than 1024 characters in that cell, you'll want
to tell them to use alt-enters to force newlines in the cell every 80-100
characters.

I have to make it easy as possible to use or it won't be used. Trying to
have them remember to alt-enter is the same as having them print the 1st 2
pages out of 7 (5 unused) by going to file/print or alt-p. They still print
5 pages of unused paper.

If they don't do that, they won't be able to see much beyond that 1000 character
limit.

That is what I am after. Some reports get rather long and complicated.

Thanks

--

Dave Peterson

Chracter Limit Help!!
 

Thanks very much Dave. I understand now. I will check out the link and get
better acquainted with this. This is all a learning experience for me. This
is the first form I have tried to do in Excel.

Thanks for the holiday response! Hope you had a good Christmas.

Matt

Chracter Limit Help!!
 

Hey Dave,
I did make sure My cell was General. It wasn't. It now is.

I tried doing the alt-enter and nothing changed. I still only see the 1024
characters. I don't understand that one now. I also tried adding in the
code you posted and there was no change to that either.

I am still reading (now on the clock) the info you linked and will pass on
what I find

Thanks
Matt

Chracter Limit Help!!
 

Remember, you have to add those alt-enters every 80 to 100 characters.

So if your text is over 1000 characters long, you'll be adding at least a dozen
of them.

The code fires when you make a change to a cell on the worksheet that owns the
code--the worksheet's tab that you rightclicked on and chose view code and
pasted.

And it would need to be modified to point at the cell that you need fixed. I
used A1.

And you have to change the value in that cell before it will fire.

Matt wrote:

Hey Dave,
I did make sure My cell was General. It wasn't. It now is.

I tried doing the alt-enter and nothing changed. I still only see the 1024
characters. I don't understand that one now. I also tried adding in the
code you posted and there was no change to that either.

I am still reading (now on the clock) the info you linked and will pass on
what I find

Thanks
Matt

--

Dave Peterson

Chracter Limit Help!!
 

Thanks Dave that did it! I appreciate the help. This will make it a lot
easier.

Matt

"Dave Peterson" wrote:

Remember, you have to add those alt-enters every 80 to 100 characters.

So if your text is over 1000 characters long, you'll be adding at least a dozen
of them.

The code fires when you make a change to a cell on the worksheet that owns the
code--the worksheet's tab that you rightclicked on and chose view code and
pasted.

And it would need to be modified to point at the cell that you need fixed. I
used A1.

And you have to change the value in that cell before it will fire.

Matt wrote:

Hey Dave,
I did make sure My cell was General. It wasn't. It now is.

I tried doing the alt-enter and nothing changed. I still only see the 1024
characters. I don't understand that one now. I also tried adding in the
code you posted and there was no change to that either.

I am still reading (now on the clock) the info you linked and will pass on
what I find

Thanks
Matt

--

Dave Peterson