VBA: Textboxes Unable Exceed 255 Characters

[TeachtheRook]

Hello. I've been having an issue with Excel 2003 and its limit of 255(?) characters when populating a textbox with a macro. Basically, what I want to do is have Excel read a few cells and write a customized message into a textbox, which can then just be copied into an actual e-mail. It takes a few cells, and customizes the e-mail based on the name and some other information.

Here's what I had:

Please Login or Register  to view this content.

This code works flawlessly for my needs in Excel 2010 (at home), but in Excel 2003 (at work) it will not populate the textbox with more than 255 characters. At 254 it runs fine, but then as soon as you put that extra character in it still runs, but nothing shows up in the box.

I've looked around and I cannot get anything working.


This link shows the problem: http://support.microsoft.com/kb/105416
I tried both Example 1 and 2 and they both stop at "
With DialogSheets(1).TextBoxes(1)" with error “Run-time error ‘9’: Subscript out of range”


I'm not really sure what I'm doing wrong, or maybe I'm completely missing something that I should be putting in with those examples. I'm kind of new to VBA.

Any help would be much appreciated.

Thanks!

[greggpetersen75]

Please Login or Register  to view this content.

[Norie]

Try this.

Please Login or Register  to view this content.

[TeachtheRook]

Thanks. I will see where these get me when I get in to work tomorrow. It works just fine in 2010.

[AlphaFrog]

There are two types of textboxes you could add to a worksheet. The one you are using now is from the Drawing toolbar. The other type is an ActiveX object from the Control Toolbox toolbar. The ActiveX-type Textbox is not limited to 255 characters.

Overview of forms, Form controls, and ActiveX controls on a worksheet

Example code to set text to an ActiveX-type textbox.

Please Login or Register  to view this content.

[TeachtheRook]

Just wanted to give a quick update.

AlphaFrog's solution worked perfectly. Big thanks for that, this had been a major headache!

Norie, your solution did the same as what I had before, it just did nothing at all.

Gregg yours I think just gave some visual feedback but didn't help with increasing the capability.

Thanks again to AlphaFrog!