Cells with more than 256 characters are only partially copied

[webster188]

Hi,

I am running an Excel worksheet which contains meeting minutes. Every topic is one line and on each line, one particular cell is used to store the comments for that topic.
These comments can be quite large and very often, there are more than 256 characters of text. The cell is formatted as "Text"(but I already tried "General" with the same result).

Now, I have a userform where, based on the topic selected, the comments are pulled from the worksheet and stored in a textbox in the userform.

This is done like: Me.Textbox.Text = Myworksheet.Cell(1,1).Text

My problem is when I show the userform, the textbox will only show the first 256 characters of the comments. I searched for a solution but I didn't find any yet.

Anybody here can help?

 

[baitmaster]

I'm unable to recreate this behaviour

Which Excel are you using? In earlier versions there were more size restrictions than current (I'm using 2010). There may be restrictions in other certain Excel features, but not how you've described it from what I can see. You might consider passing your text to a variable first, so you can test where the issue is coming from, for example

debug.print Myworksheet.Cells(1,1).Text, len(Myworksheet.Cells(1,1).Text)
dim str as string
str = Myworksheet.Cells(1,1).Text
Me.Textbox.Text = str
debug.print str, len(str)

The debug lines will write the text strings to the immediate window (VBA > View > Immediate Window), as well as their lengths. These should indicate whether the issue relates to getting the text out of the cell, or getting it into the user form

I do note that you wrote cell() instead of cells() in your code

 

[webster188]

Hi,

Thank you for the reply. To be honest, I was expecting this to be a configuration issue or something that could be easily solved. That is why I simplified things in my question.

I am indeed trying to pass data (text) stored in a particular cell to a userform. The worksheet where the data resides is formatted as a table (with row headers) and is used as a database.
I am searching the database using SQL queries.

Now, the good news: I solved the issue and would like to share the info if somebody would ever need it.

The issue of the text being truncated to 256 characters when passing it via SQL to the userform is solved by making sure that in the first data row of your table (Database) the content of the cell has more than 256 characters.
It sound strange but it really works (found this solution somewhere on the internet).
If in the first data row, cells contain less than 256 characters, the data will be truncated in the rest of the table when using SQL queries.