ComboBox "ERROR 5854 - String Parameter too long"

[Lucasteve]

Hi VBA Enthusiasts :)

I am a novice with ok instincts, but truthfully, I really am flying by the seat of my pants on this work project. So, the project requires the execution of a macro that populates various content into the body of a letter. In some cases, this content must contain drop downs (ComboBoxes).

The problem I'm having is that I've built the code very basically as follows:

Sub combobox2()
'
' Combobox Macro Example
' The following code yields the "ERROR 5854 - String Parameter too long" message.
'
Selection.Range.ContentControls.Add (wdContentControlComboBox)
Selection.ParentContentControl.DropdownListEntries.Clear
Selection.ParentContentControl.DropdownListEntries.Add Text:= _
"Choose an item.", Value:=""
Selection.ParentContentControl.DropdownListEntries.Add Text:= _
"The $XXX.XX fee listed on the billing statement was a broker's price opinion, which is a market evaluation of the property. They are used to get an approximate value of the property and are acceptable per the terms of the Note and/or Mortgage/Deed of Trust." _
, Value:= _
"The $XXX.XX fee listed on the billing statement was a broker's price opinion, which is a market evaluation of the property. They are used to get an approximate value of the property and are acceptable per the terms of the Note and/or Mortgage/Deed of Trust."
End Sub

This code works well for instances where the desired drop down verbiage is shorter. So, I'm starting to think that the verbiage listed as drop down option is causing the String Parameter to exceed the 255 character limit. (I know that ComboBoxes allow for well over 255 characters when not executed using a macro.)

Please help me find a workaround for this.

NOTE that,

I'm working in Word 2010.
I can't alter the desired verbiage at all.
I must keep all content within the document and the user must not be able to see the drop down options until executing the macro. (Because of how this document will be used, I can't import from a secondary file like an external excel file.)


Ideas I've had include:

If possible, import the drop-down content from an xml file (or schema that's housed within the word document)
Reconstruct the coding about to allow for more than 255 characters (but that's beyond my scope of VBA understanding)
The last resort would be to contact the content creators for the letter and tell them that they have to keep the drop down content below 255 characters. They expressed this concern early on and since I knew that the comboboxes themselves would accommodate, I promised that it shouldn't be a problem. (It didn't occur to me that there could be coding string character limitations.)


Thank you in advance for any input you VBA geniuses could provide :)

Steve

[gmaxey]

Steve,

I don't really understand what you are trying to do. Why are you creating the comboboxes with code?

Regardless, you are not going to get around the string length issue. It is what is and since you text and values are greater than 255 you are SOL there.

You can map the CCs to an XML part and use the CC events to alter the CC value. I've attached a demo document.

I left out a critical line of code in the attached file. Here is the affected/modified procedure:


Private Sub Document_ContentControlBeforeContentUpdate(ByVal ContentControl As ContentControl, Content As String)
Dim oNode As CustomXMLNode
With ContentControl
Select Case .Title
Case "ComboBox_Demo"
Set m_oXMLPart = ActiveDocument.CustomXMLParts.SelectByNamespace("http://gremaxey.mvps.org/demo").Item(1) 'Add this line
Set oNode = m_oXMLPart.SelectSingleNode("/ns0:Mapped_ComboBoxes[1]/ns0:ComboBox[1]")
Select Case .Range.Text
Case "A"
oNode.Text = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" _
& "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" _
& "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
Case "B"
oNode.Text = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB" _
& "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB" _
& "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB"
End Select
End Select
End With
lbl_Exit:
Exit Sub
End Sub