Solved

How do I get round the 255 char limit in a Excel control text box ?

Posted on 2014-03-08
14
2,927 Views
Last Modified: 2014-03-10
Hi,

I have an Excel 2010 User form made up of Active X controls. My VBA procedure transfers the sheet data into an array and then populates the relevant text box controls.

However, I have hit a problem. If a sheet cell contains a string of more than 255 characters then the procedure returns an error when populating the text box.

How can I get round this 'string to array to text box' issue in my VBA ?

Thanks
Toco
0
Comment
Question by:Tocogroup
14 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39914654
I don't have a problem populating a text box with a long string from a cell - I have a test that happily loads a string of length 2133 into the text box.  It's possible you are getting another error - can you post your code, please?  Then we can check whether something else is going wrong.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39914658
The limitation is in the text box control so it depends on where you got it from. the standard excel one can be extended by setting the 'multiline' = true property or you could change it to an RTF text box which is only limited by memory space however the standard excel  textbox  control can handle around more than 255 in a single line so more information, code, error message, origin of the control would help.

Assuming that you are not able to do either of those you would have to decide what to do with the 'extra' text information - either ignore it (.value = Mid(thetext, 1,255) or loop through it chunking 255 chars and adding additional text boxes - how many would depend on how long the text actually gets.
0
 

Author Comment

by:Tocogroup
ID: 39914676
Hi,

The text boxes are Active X controls, not Excel controls.

I'm getting the following error (on the first line of the liContracts_Click procedure below) when I attempt to populate my user form fields from an array which I've loaded from my datasheet.

Run-time error '13':
Type mismatch

I don't get an error if the sheet cell containing the text is less than 256 characters.

Here is the relevant portions of code - loading the array from the worksheet, and populating the various text boxes from the array.

   Set wsContracts = ThisWorkbook.Sheets("Contracts")

' Load Contracts array from Contracts sheet
   glgContractsColumns = wsContracts.Range("ContractsTable").Columns.Count
   ContractsArray() = Range(Cells(3, 1), Cells(1048576, glgContractsColumns).End (xlUp)).Value

   liContracts.List = ContractsArray()
   liContracts.ListIndex = liContracts.ListCount - 1


Private Sub liContracts_Click()

   'On Error Resume Next

   With Application

'Populate Contracts form fields from Contracts array
   teContractID = .VLookup(liContracts.Value, ContractsArray, 1, False)
   teDateCreated = .VLookup(liContracts.Value, ContractsArray, 2, False)
   coStatus = .VLookup(liContracts.Value, ContractsArray, 3, False)
   coPosition = .VLookup(liContracts.Value, ContractsArray, 4, False)
   
   teAgency = .VLookup(liContracts.Value, ContractsArray, 5, False)
   teAgencyPhone = .VLookup(liContracts.Value, ContractsArray, 6, False)
   teAgencyFax = .VLookup(liContracts.Value, ContractsArray, 7, False)
   
   teContact = .VLookup(liContracts.Value, ContractsArray, 8, False)
   teContactTitle = .VLookup(liContracts.Value, ContractsArray, 9, False)
   teContactPhone = .VLookup(liContracts.Value, ContractsArray, 10, False)
   teContactMobile = .VLookup(liContracts.Value, ContractsArray, 11, False)
   teContactEmail = .VLookup(liContracts.Value, ContractsArray, 12, False)
   
   teClient = .VLookup(liContracts.Value, ContractsArray, 13, False)
   teLocation = .VLookup(liContracts.Value, ContractsArray, 14, False)
   teLength = .VLookup(liContracts.Value, ContractsArray, 15, False)
   teStartDate = .VLookup(liContracts.Value, ContractsArray, 16, False)
   teCVSubmitted = .VLookup(liContracts.Value, ContractsArray, 17, False)
   coSubmissionMethod = .VLookup(liContracts.Value, ContractsArray, 18, False)
   coJobSource = .VLookup(liContracts.Value, ContractsArray, 19, False)
   teReference = .VLookup(liContracts.Value, ContractsArray, 20, False)
   teKeywords = .VLookup(liContracts.Value, ContractsArray, 21, False)
   
   teRate = .VLookup(liContracts.Value, ContractsArray, 22, False)
   coPerUnit = .VLookup(liContracts.Value, ContractsArray, 23, False)
   
   teNotes = .VLookup(liContracts.Value, ContractsArray, 24, False)
   teDiary = .VLookup(liContracts.Value, ContractsArray, 25, False)
   End With

End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39914713
Assuming that your problem is with the VLOOKUP not being able to handle more than 255 characters in VBA, please take a look at this thread. The link points to a Microsoft forum question titled "vLookup Character Limit Error - Excel 2010". The suggested workaround was to write a UDF (provided on that webpage) that performed the VLOOKUP and use that instead.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39914993
I would suggest that you use MATCH to find your contract id first in column 1, then you can directly reference the array using the row returned.  This will also save a lot of wasted processing with VLOOKUP having to search for the same thing multiple times.  Match only searches in a single column, so you will have to get the first column into your ContractsArray first and do the match, then load all the data into ContractsArray and reference it using the MATCH value. So your code would look a bit like this (not tested because I don't have your data:
   Set wsContracts = ThisWorkbook.Sheets("Contracts")

' Load Contracts array from Contracts sheet
   glgContractsColumns = wsContracts.Range("ContractsTable").Columns.Count
   
   ' just the first column
   ContractsArray() = Range(Cells(3, 1), Cells(wsContracts.Rows.Count, 1).End(xlUp)).Value

    Dim lContractRow As Long


Private Sub liContracts_Click()

   'On Error Resume Next

   With Application
   
   lContractRow = .WorksheetFunction.Match(liContracts.Value, ContractsArea, 0)
   
   ' now load the whole thing as before
   ContractsArray() = Range(Cells(3, 1), Cells(wsContracts.Rows.Count, glgContractsColumns).End(xlUp)).Value
   liContracts.List = ContractsArray()
   liContracts.ListIndex = liContracts.ListCount - 1

'Populate Contracts form fields from Contracts array
' now you know the row, just reference the array directly
   teContractID = ContractsArray(lContractRow, 1)
   teDateCreated = ContractsArray(lContractRow, 2)
   coStatus = ContractsArray(lContractRow, 3)
   coPosition = ContractsArray(lContractRow, 4)
   .
   .
   .
   .

Open in new window

0
 

Author Comment

by:Tocogroup
ID: 39915464
Thanks ad3. I understand your logic in using MATCH rather than VLOOKUP.

However, I'm getting the same error as before but on your line....

  lContractRow = .WorksheetFunction.Match(liContracts.Value, ContractsArea, 0)
 
Also, I'm not sure why (after the above line), I need to reload the array from the sheet again.

Regards
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Expert Comment

by:andrewssd3
ID: 39915548
Not sure why you're getting that error still - do you have any error (#VALUE!, etc) values in your actual sheet data?  It would really help if you could post the whole sheet if it does not have sensitive data.

As for loading the array again, I changed the code so it loaded just the contract id column, as MATCH can only work on a single column (or row) of data.  Once you have located the row you need like this, you need to load all the columns again, so that you can get the data from the other columns to fill your form. This seems a bit clumsy, especially if you are used to languages where you can slice arrays to get particular rows or columns out, but with VBA I'm afraid there's no easy way to get a whole column from a 2-d array without actually looping through, and in this case it's probably simpler and more efficient just to get the data twice.
0
 

Author Comment

by:Tocogroup
ID: 39915762
Hi,

I've attached a cut-down version of the application and de-sensitised the data. You get a different error message when the 255 character cell rule is exceeded.

Thanks for your time and effort.
Contracts1.xlsm
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39916199
It is still that the array has more than one column from where you load it in the initialise proc, and MATCH insists on only one (I personally think it would be easier to use if it could take a multicolumn array and just look in the first column, like VLOOKUP, but I don't work for Microsoft).  Try adding the single column load just before your match - this works for me:
Private Sub liContracts_Click()

   Dim lContractRow As Long

   'On Error Resume Next

   With Application
   
   ContractsArray() = Range(Cells(3, 1), Cells(1048576, 1).End(xlUp)).Value

   lContractRow = .WorksheetFunction.Match(liContracts.Value, ContractsArray, 0)
   
   'now load the whole thing as before
   ContractsArray() = Range(Cells(3, 1), Cells(wsContracts.Rows.Count, glgContractsColumns).End(xlUp)).Value
   liContracts.List = ContractsArray()

Open in new window

Note that I would recommend using wsContracts.Rows.Count instead of 1048576 - this gives you flexibility and your code will still run on a pre-2007 Excel where the row limit was smaller, or on a future version where the number might go up...
0
 

Author Comment

by:Tocogroup
ID: 39916975
Yes, I see what you mean.

Still a problem though as I'm getting an error when I .......

1. Open the form (the most recent record is displayed)
2. Click on a different record in the Listbox
3. Click on the most recent record again

I get a  - Run time error 28 - Out of stack space
And then Excel crashes. I tried debugging it but it crashes before I can inspect.
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 39917037
I can't replicate that problem.  However it does sound like a completely different thing from the original question.  I think you should probably close this one and award points, and raise a new question for the new problem. That way it's easier for people to get help from this thread later on.

Thanks

Stuart
0
 

Author Closing Comment

by:Tocogroup
ID: 39917055
Thanks for your help on this problem. I'll investigate the memory issue later.
Regards
Toco
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39917078
Hi Toco - thanks.  As a first thought your problem sounds most likely to be due to an event procedure (listbox_change or similar) calling itself recursively - i.e. you change the listbox within the listbox_change, etc...  Have a look for this sort of issue, which you can sometimes fix using Application.EnableEvents=False.  Anyhow, if you can't solve it do raise another question and we can take a proper look.

Cheers

Stuart
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now