# of allowable characters in Excel 2003 cell

[Guest]

I have an Excel workbook that I am using to capture data from remote sites.
They will be using a form to enter their data, but I just encountered a
problem while testing. I pasted about 3400 characters into a textbox and in
the AfterUpdate event attempted to use the following code to write that data
into a cell in my worksheet.

ActiveWorkbook.Sheets("Data").Cells(intRowPointer, 5) =
Me.txt_Soln_Description

But I got the following error: Run-time error '1004' Application-defined or
object-defined error.

When I selected "Debug", the line shown above was highlighted. When I
printed me.txt_Soln_Description in the debug window, it contained all of the
characters, and the row pointer was also valid.

When I pasted the characters directly into the worksheet cell, they were all
stored properly, and were visible in my form when I retrieved them. Is there
a limit to the number of characters I can put in a single cell? If so, what
is it?

 

[Guest]

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.

BTW: The full 32,767 characters do display in the formula bar however.

 

[Dale Fye]

Thanks, Kevin.

I don't know why this works, but I wrapped my control name in cstr( ) and
got the code below to work for the 3000+ characters I was testing. Glad I
did that test because I anticipate some long responses.

Dim sht as worksheet
set sht = activeworkbook.sheets("Data")
sht.Cells(intRowPointer, 5) = cstr(Me.txt_Soln_Description)

Dale

 

[Guest]

Yes, but the formula bar is such an ugly place to view text, and it blocks
all of the cells under it.

That is a huge part of the reason I built the data entry form.

Thanks for your feedback.

 

[Matt]

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.

OK, I am having the issue of needing to see more than the 1024 limit in a
cell. Mine is a merged cell (almost a full page) and I don't know how to
apply the "vbNewLine constant by code". Can someone please point me in the
right direction?

I have a protected form for people to fill out. It will NOT be unprotected
by the users to complete. I need the ability to show as many characters as
possible. Somewhere around 6k.

Thanks