Fill Formfields based on DropDown

[Shred Dude]

I'm wanting to automate the population of several formfields as a result of the selection in one dropdown.

eg. Dropdown contains Banana and Potato

Want to pick one of those and have the "Type" and "Color" formfields populated.

Thus is if Banana is selected in the dropdown, "Type" formfield would be filled in with "Fruit", and "Color" would be filled in with "Yellow".

I primarily use Excel, and this sort of task is common for me there. I'm not figuring out how I'd accomplish this strictly in Word though.

Where would I store the correlation of these elements? In Excel I'd just reference a Range where I keep all these attributes.

The real example is a choice of about 25 Items in the dropdown, and then populating about 20 attributes associated with the choice scattered around the document, not all neatly in one spot.

I have a list in Excel with 25 rows, and 20+ columns with all of that data. I don't want users to see a table of data. Do I need to reference the excel worksheet to perform this task? Or is there way to "store" this information in a "hidden" manner within the Word Template? Anything like a "veryhidden" sheet in Excel where I could stash the table of data?

[Tinbendr]

See this example (http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm) of Greg's site. Look for Excel Example 2 about 1/2 the way down.

I'm sorry I haven't answered any of your questions, but maybe Greg's site will give you a start.

David

[Shred Dude]

Thank you for the link. Lots of good info there.

All of those examples rely on the source data being stored in a separate file, or in the case of simpler data, hard coded within the VBA routines.

I was wondering if there was means by which I could store the data within the Word file itself. For example as a Word Table.

In Excel, you can have a "veryhidden" sheet where you could stash a reference table that users would never see. Is there any way to do a similar thing within a Word Template? A hidden "page" that doesn't print?

I'm just trying to avoid having to distribute two files to users for this solution. This solution will run locally on one's PC, so no ability to reference a static network location for the reference data source. I'm trying to eliminate the need to coordinate the placement of a secondary reference file.

Any thoughts?

[Tinbendr]

I understand about distributing two files.

You can use a table. You can make that table hidden also, but there is no very hidden parameter like Excel. Any user with any experience can 'uncover' the table. You could protect the section the table is in, to prevent modification.

Of course, you can hard code it into the Initialize event of the userform. Will the data change often?

You can use document variables. They are stored with the document and are not normally 'visible' to the user.

BTW, if you have more than 25 items, you'll have to use a userform. (I'm not sure if this pertains to Content Controls though)

David

[Shred Dude]

Thanks again for the reply. I'll explore some of those alternatives.

I'm using 2007, and have been using the DropDown content control. I"m not sure there'd be a constraint at 25, but I'll double check.

[gmaxey]

There is "not" a 25 listentry limit on dropdown content controls. There is unfortunately an annoying bug with the content control OnExit event in Word2007 (fixed in 2010). Unless the Developer Tab is the active Tab the event will fire and loop continoulsy.

I have a tips page on linked formfield drops down (which you could easily adapt to use a textbox). http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm

[gmaxey]

I have attached a document giving an example of Linked CCs. You will note that if the Developer Tab in not the active Tab then the screen will flicker when you enter some of the fields.




Thanks again for the reply. I'll explore some of those alternatives.

I'm using 2007, and have been using the DropDown content control. I"m not sure there'd be a constraint at 25, but I'll double check.

[Shred Dude]

Greg:

Thank you for your time. I enjoyed discovering your site today, it's now duly bookmarked in my Browser's Favorites.

I mocked up an example of my scenario, and it works. I'm just wondering what you Word experts think of the approach. In my real solution, my source data is relatively static, so I can probably get away with hard coding it in this instance. I'm hesitant to deliver a secondary file to which I must link to get the source data, but may do that. I think I'd just use a simple text file in this case.

Turns out in this project I have 28 items I must place in this dropdown, so looks like I'm using Content Controls.

Couple of questions...

How do you access the "Value" of the selected Item in a Content Control Drop Down? I couldn't find that in the object model or msdn help. I used the range.text property in the code below. I'd prefer to populate an "Index" number in the "Value" portion of the dropdownlistentry, and then use that in my code.

I'm guessing from your example file (thanks for that) that looping is the only way to get your selected item? Content Controls not as functionally rich as a Listbox with properties like listindex, selecteditem, etc?

Here's my little mock up...
In the ThisDocument Class...
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)

If ContentControl.Tag = "ddTest" Then
'loop the source data array to find the row of the selected item
For r = 1 To UBound(myData, 1)
If ContentControl.Range.Text = myData(r, 1) Then
'fill other text boxes
ActiveDocument.SelectContentControlsByTitle("Type").Item(1).Range.Text = myData(r, 2)
ActiveDocument.SelectContentControlsByTitle("Color").Item(1).Range.Text = myData(r, 3)
Exit For
End If
Next r
End If

End Sub

Private Sub Document_Open()

loadData

initalizeDropDown


and in a separate module...

Public myData(2, 3) As Variant


Public Sub loadData()
'Initialize the Public mydata Stroage Variable with Static Data
myData(1, 1) = "Banana"
myData(2, 1) = "Potato"

myData(1, 2) = "Fruit"
myData(1, 3) = "Yellow"

myData(2, 2) = "Vegetable"
myData(2, 3) = "Brown"

End Sub


Public Sub initalizeDropDown()

Dim oDD As ContentControl

Set oDD = ActiveDocument.SelectContentControlsByTitle("ddTester").Item(1)
For r = 1 To UBound(myData, 1)
oDD.DropdownListEntries.Add (myData(r, 1))
Next r
Set oDD = Nothing

End Sub

[gmaxey]

Dude,

Do you have a real name?

You are right in your guess. The ContentControl object is lacking :-(

You might try something like this:

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim myData(3, 3) As String
Dim i As Long
myData(0, 0) = "Apples"
myData(0, 1) = "1"
myData(0, 2) = "Fruit"
myData(0, 3) = "Red"
myData(1, 0) = "Carrots"
myData(1, 1) = "2"
myData(1, 2) = "Vegtable"
myData(1, 3) = "Orange"
myData(2, 0) = "Corn"
myData(2, 1) = "3"
myData(2, 2) = "Grain"
myData(2, 3) = "Yellow"
If CC.Tag = "ddTest" Then
Dim Index As Long
For i = 2 To CC.DropdownListEntries.Count
If CC.Range.Text = CC.DropdownListEntries(i).Text Then
Index = CLng(CC.DropdownListEntries(i).Value)
Exit For
End If
Next i
If Index > 0 Then
ActiveDocument.SelectContentControlsByTitle("Type").Item(1).Range.Text = myData(Index - 1, 2)
ActiveDocument.SelectContentControlsByTitle("Color").Item(1).Range.Text = myData(Index - 1, 3)
Else
ActiveDocument.SelectContentControlsByTitle("Type").Item(1).Range.Text = ""
ActiveDocument.SelectContentControlsByTitle("Color").Item(1).Range.Text = ""
End If
End If
End Sub

[Shred Dude]

Thanks. I had gotten there after a more thorough look at the object browser.

It's nice that the content control object gives you a structure for having both a display member and a value member for its dropdown entry item collection, but it seems kind of weak that you have to iterate the collection to match the range.text to a list entry in order to get to the associated value.

I was thinking that all that looping with string comparisons on each iteration might be slow if you had a large list, so I tried a different way, using an Excel function. It works, but is much slower on my 3 item list, most likely due to having to create the Excel object. I didn't test it but I'm guessing that on a large list, if such a thing were ever appropriate for a dropdown control in the first place, that it might be of some value.

This assumes your list contains unique values.

Public Function ddIndex(CC As ContentControl, RangeText As String) As Long
'Returns the location of a given DropDown Content Control's Selection within
'it's list of Dropdown List entries

Dim XL As Object
Dim arrDD() As Variant

ReDim arrDD(1 To CC.DropdownListEntries.Count)

For d = 1 To CC.DropdownListEntries.Count
arrDD(d) = CC.DropdownListEntries.Item(d).Text
Next d

Set XL = CreateObject("Excel.application")

ddIndex = XL.Application.match(RangeText, arrDD, 0)

Set XL = Nothing

End Function

and then used it like so...

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)

If ContentControl.Tag = "ddTest" Then

Dim r As Long

r = ddIndex(ContentControl, ContentControl.Range.Text)

With ActiveDocument
.SelectContentControlsByTitle("Type").Item(1).Range.Text = myData(r, 2)
.SelectContentControlsByTitle("Color").Item(1).Range.Text = myData(r, 3)
End With

End If

' 'loop the source data array to find the row of the selected item
' For r = 1 To UBound(myData, 1)
' If ContentControl.Range.Text = myData(r, 1) Then
' 'fill other text boxes
' ActiveDocument.SelectContentControlsByTitle("Type").Item(1).Range.Text = myData(r, 2)
' ActiveDocument.SelectContentControlsByTitle("Color").Item(1).Range.Text = myData(r, 3)
' Exit For
' End If
' Next r
'End If

End Sub


Does Word have any sort of function to quickly filter a table down to matches? If you had your data in a hidden table, and they were all unique items, is there a way in Word to quickly get to the row in the table that matches a given input? Might the Find object's Execute method be employed to give you the matching row number in a table? I'm not very familiar with the Word Object model, just trying to figure a few things out...!

[Shred Dude]

I just modified it so that if an Instance of Excel is already running to use that. If XL is running already, this executes very quickly!

Public Function ddIndex(CC As ContentControl, RangeText As String) As Long
'Returns the location of a given DropDown Content Control's Selection within
'it's list of Dropdown List entries

Dim XL As Object
'Dim bStartedXL As Boolean
Dim arrDD() As Variant

ReDim arrDD(1 To CC.DropdownListEntries.Count)

For d = 1 To CC.DropdownListEntries.Count
arrDD(d) = CC.DropdownListEntries.Item(d).Text
Next d


On Error Resume Next
Set XL = GetObject(, "Excel.application")
If XL Is Nothing Then
set XL = CreateObject("Excel.application")
'bStartedXL = True
End If
On Error GoTo 0



ddIndex = XL.Application.match(RangeText, arrDD, 0)

Set XL = Nothing

End Function

[gmaxey]

Well yes. If you had a 4 column hidden table in a document where:

Column 1: Index number 1 to whatever
Column 2: Item in dropdown list
Column 3: Type value for item
Column 4: Color value for item

Then you could use something like this:

Option Explicit
Private Type ListData
pType As String
pColor As String
End Type
Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim tData As ListData
If CC.Tag = "ddTest" Then
tData = GetData(CC.Range.Text)
With ActiveDocument
.SelectContentControlsByTitle("Type").Item(1).Range.Text = tData.pType
.SelectContentControlsByTitle("Color").Item(1).Range.Text = tData.pColor
End With
End If
End Sub
Private Function GetData(pText As String) As ListData
Dim oRng As Word.Range
Dim oTbl As Word.Table
Dim lngIndex As Long
Set oTbl = ActiveDocument.Tables(1)
Set oRng = oTbl.Range
With oRng.Find
.ClearFormatting
.Text = pText
.Execute
If .Found = True Then
lngIndex = CLng(Left(oRng.Cells(1).Previous.Range.Text, Len(oRng.Cells(1).Previous.Range.Text) - 2))
End If
End With
If lngIndex > 0 Then
GetData.pType = Left(oTbl.Cell(lngIndex, 3).Range.Text, Len(oTbl.Cell(lngIndex, 3).Range.Text) - 2)
GetData.pColor = Left(oTbl.Cell(lngIndex, 4).Range.Text, Len(oTbl.Cell(lngIndex, 4).Range.Text) - 2)
Else
GetData.pType = ""
GetData.pColor = ""
End If
Set oTbl = Nothing
Set oRng = Nothing
End Function


But then we are back to using the .Text property vice the .Value property. Unfortunately CCs are as you say "weak."

[gmaxey]

Another way is to store related values in document variables:

Option Explicit
Private Type ListData
pType As String
pColor As String
End Type
Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim tData As ListData
Select Case CC.Tag
Case Is = "ddTest"
tData = GetData(CC.Range.Text)
With ActiveDocument
.SelectContentControlsByTitle("Type").Item(1).Range.Text = tData.pType
.SelectContentControlsByTitle("Color").Item(1).Range.Text = tData.pColor
End With
Case Is = "ddTestII"
tData = GetDataII(CC.Range.Text)
With ActiveDocument
.SelectContentControlsByTitle("TypeII").Item(1).Range.Text = tData.pType
.SelectContentControlsByTitle("ColorII").Item(1).Range.Text = tData.pColor
End With
End Select
End Sub
Private Function GetData(pText As String) As ListData
Dim oRng As Word.Range
Dim oTbl As Word.Table
Dim lngIndex As Long
Set oTbl = ActiveDocument.Tables(1)
Set oRng = oTbl.Range
With oRng.Find
.ClearFormatting
.Text = pText
.Execute
If .Found = True Then
lngIndex = CLng(Left(oRng.Cells(1).Previous.Range.Text, Len(oRng.Cells(1).Previous.Range.Text) - 2))
End If
End With
If lngIndex > 0 Then
GetData.pType = Left(oTbl.Cell(lngIndex, 3).Range.Text, Len(oTbl.Cell(lngIndex, 3).Range.Text) - 2)
GetData.pColor = Left(oTbl.Cell(lngIndex, 4).Range.Text, Len(oTbl.Cell(lngIndex, 4).Range.Text) - 2)
Else
GetData.pType = ""
GetData.pColor = ""
End If
Set oTbl = Nothing
Set oRng = Nothing
End Function
Private Function GetDataII(pText As String) As ListData
Dim arrData() As String
arrData() = Split(ActiveDocument.Variables(pText).Value, "|")
GetDataII.pType = arrData(0)
GetDataII.pColor = arrData(1)
Exit Function
Err_NoPick:
GetDataII.pType = ""
GetDataII.pColor = ""
End Function

'Setup the variables in advance
Sub StoreVariables()
Dim oVars As Variables ', oVar As Variable
Set oVars = ActiveDocument.Variables
'For Each oVar In oVars
' oVar.Delete
'Next oVar
With oVars
.Add "Apples", "Fruit|Red"
.Add "Carrots", "Vegetable|Orange"
.Add "Corn", "Grain|Yellow"
.Add "Blueberries", "Fruit|Blue"
.Add "Limes", "Fruit|Green"
End With
Set oVars = Nothing
End Sub

[gmaxey]

Looking over all of this again only proves that MS was a bit slack with the introduction of content controls. While their ability to be bound to an XML data node alone makes up for many shortcomings thoses shortcomings are numerous indeed and make working with them like pushing a bowling ball up hill with a broom straw. :(

The inability to get out the CC dropdownlistentry value property without looping through all of the dropdownlistenties is simply stupid. So is the absence of a ListIndex method. MS's failur to resolve the OnExit bug (in Word 2007) now going on 6 years old is inexcusable.

Another annoying issue is how the Placeholder text for dropdown lists also appears as a list entry. I mean come on "Choose an item" is not really an item of choice. While it can be removed from the list there doesn't appear to be anyway to programatically restore the Placeholder text (at least placeholder text that looks like placeholder text) back to the CC.

I am attaching a document that illustrates four different methods for setting a CC result based on a selection made in a CC dropdown list. It seems to me that the most efficient method is method 3. This method uses data stored in .Value property of the CC.

I also show an example (using method 3) of setting the dropdown list entries of a dropdown CC based on the selection made in another CC dropdown list.

Any comments are welcome. I think I will post this stuff on my website.

[Shred Dude]

Thanks for all your time on this.

I had got my solution going with accessing an external excel file. I dont' like having to have the external data source though. I have a 28 row table with about 20 columns of relatively static data. I think I'd like to go with the hidden table approach within the document template.

I'm not very familiar with Word Tables. Could you maybe point me in the right direction on how to hide such a table in the template in such a way that it it couldn't be inadvertently be compromised by the user. Also, I'm assuming you can "name" a table so that it can be referred to in code by a name?

I played aroud with your routines a little as a learning exercise to explore the Word Object model some more. Noticing your approach of taking off the last two characters in a cell, led me down a path to understand the need for that. I had run into this last night, not understanding that for some reason table entries include a carriage return character. Why is that?

Anyhow, I tweaked up your routine as follows. I was hoping the Range object's textretrievalmode properties might help but they didn't. I don't necessarily need an Index Number in my data, so I found the rowindex property to be useful.

Private Function GetData(pText As String) As ListData
Dim oRng As Word.Range
Dim oTbl As Word.Table
Dim lngIndex As Long
Set oTbl = ActiveDocument.Tables(1)
Set oRng = oTbl.Range

With oRng.Find
.ClearFormatting
.Text = pText
.Execute
If .Found = True Then
Set oRng = oTbl.Rows(oRng.Cells(1).RowIndex).Range

'oRng.TextRetrievalMode.IncludeFieldCodes = False

'oRng.TextRetrievalMode.IncludeHiddenText = False '??? This didn't remove the Return Character

GetData.pType = Trim(Replace(oRng.Cells(3).Range.Text, Chr(13), ""))
GetData.pColor = Trim(Replace(oRng.Cells(4).Range.Text, Chr(13), ""))

End If
End With

Set oTbl = Nothing
Set oRng = Nothing
End Function

[Shred Dude]

Greg:

Do you think I should consider structuring my static data as XML and using those features to map to content controls?

Never done it before in Word. Big learning curve?

[gmaxey]

Dude,

Ok that is your name.

The thing at the end of a cell (end of cell marker) is actually made up of two Asc characters 13 and 7. That is why I chop off -2 in the lazy way. It is lazy as I have a collection of functions that all do the same thing but I can never remember to use them when I am just scrathing out code.



Sub Demo()
Dim oCell As Cell
For Each oCell In Selection.Tables(1).Range.Cells
MsgBox fcnCellText1(oCell.Range.Text)
MsgBox fcnCellText2(oCell.Range)
MsgBox fcnCellText3(oCell.Range)
MsgBox fcnCellText4(oCell.Range.Text)
MsgBox fcnCellText5(oCell.Range)
Next
End Sub
Function fcnCellText1(ByRef pStr As String)
fcnCellText1 = Left(pStr, Len(pStr) - 2)
End Function
Function fcnCellText2(ByRef oRng As Range)
oRng.MoveEnd wdCharacter, -1
fcnCellText2 = oRng.Text
End Function
Function fcnCellText3(ByRef oRng As Range)
oRng.End = oRng.End - 1
fcnCellText3 = oRng.Text
End Function
Function fcnCellText4(ByRef pStr As String)
fcnCellText4 = Replace(pStr, ChrW(13) & ChrW(7), "")
End Function
Function fcnCellText5(ByRef oRng As Range)
'Slightly different as this strips trailing paragraphs.
oRng.MoveEndWhile Cset:=Chr(13) & Chr(7), Count:=wdBackward
fcnCellText5 = oRng.Text
End Function

Since you don't need an column that contains a number then you could just:


lngIndex = oRng.Cells(1).RowIndex
If lngIndex > 0 Then
GetData.pType = fcnCellText(oTbl.Cell(lngIndex, 2).Range.Text)
GetData.pColor = fcnCellText(oTbl.Cell(lngIndex, 3).Range.Text)

As for hidding a table, AFAIK about all you can do is format it as hidden text. This means any user can decide to show hidden text and if they want then monkey with the data. You can't name tables but you can wrap them in a named bookmark:

Dim oTbl As Word.Table
Set oTbl = ActiveDocument.Bookmarks("HiddenTable").Range.Tables(1)

[Shred Dude]

I'm skeptical of the hidden table approach. So, I've gone with populating Document Variables, and using those to create the DropDownEntries' Text and Value properties. My Value properties end up being a long pipe delimited
string.

I anticipate I'll write a helper routine to allow the user to launch a file picker dialog to select the location that the supporting data file is, for the infrequent situations in the future when the data needs to be updated.

In the meantime, the template will contain all the data, and I"ll use a derivative of your GetDataIII routine.

Thanks again for all your input on this. It's been a good learning experience!

Example code:

Sub StoreVariables()
Dim oVars As Variables, oVar As Variable
Set oVars = ActiveDocument.Variables

'clear any existing Doc Variables
For Each oVar In oVars
oVar.Delete
Next oVar

'Load Data from Spreadsheet into "mydata" public array
getPanelData
'build Strings for DropDown Values
Dim strValue As String

For r = 1 To UBound(myData, 1)
For c = 1 To UBound(myData, 2)
strValue = strValue & myData(r, c) & "|"
Next c

'srip off last "|"
strValue = Left(strValue, Len(strValue) - 1)

oVars.Add myData(r, 1), strValue
strValue = ""
Next r

Set oVars = Nothing
End Sub

Public Sub initalizeDropDown()

Dim oDD As ContentControl
Set oDD = ActiveDocument.SelectContentControlsByTag("panel").Item(1)
oDD.DropdownListEntries.Clear

Dim oVars As Variables, oVar As Variable
Set oVars = ActiveDocument.Variables

For Each oVar In oVars
mytext = Split(oVar.Value, "|")(0)
myvalue = Mid(oVar.Value, InStr(1, oVar.Value, "|") + 1)

oDD.DropdownListEntries.Add mytext, myvalue
Next

Set oDD = Nothing

End Sub

[gmaxey]

You're welcome. Good luck. I have done that much with XML so I don't really know what the issues would be.

[Shred Dude]

I said I didn't need an Index number in my data, but turns I do with this approach, as the Value property in the dropdownentry collection must be unique. I had some situations in my data where although I had a unique identifier in the element I'm choosing to use as the Text, it turned out that some off the rows contained an identical set of attributes that I was attempting to use as the value.

So, I added a column in the spreadsheet's range and populated it with a =row() formula to generate a unique identifier. That way my pipe delimited strings end up being unique, and I just never use that last element.

Thanks for the explanation on the codes and your sampling of functions. That helps me understand better.

Most times in these forums people call me Shred, but in other worlds I go by Keith.

[Shred Dude]

You wouldn't know of a way to autosize the content control's drop down list's width based on its contents would you?

It seems by default its a function of the currently selected item's width.

Only thing I've figured out so far isn't pretty. I can append a few spaces on the end of my shorter entries, so that all entries end up being the same length. but then I have to but a trim statement in the code when trying to use one of those selected range.texts. It happens to work with the data I presently have, but I can see some shortcomings with the approach if I had different data.

[Shred Dude]

EDIT:

Found a better way...

It seems the width can be manipulated by changing the width an entry, so I modified the first entry to act as a placeholder.

activedocument.SelectContentControlsByTag("panel").Item(1).DropdownListEntries.Item(1).Text = "Pick a Panel >"

[gmaxey]

Keith,

My middle name. Easy to remember. Pleased I could help. I am sure there is plenty that I could learn from you!!