Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 23

Thread: Fill Formfields based on DropDown

  1. #1

    Fill Formfields based on DropDown

    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?

  2. #2
    VBAX Expert Tinbendr's Avatar
    Joined
    Jun 2005
    Location
    North Central Mississippi (The Pines)
    Posts
    993
    Location
    See this example 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

    David


  3. #3
    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?

  4. #4
    VBAX Expert Tinbendr's Avatar
    Joined
    Jun 2005
    Location
    North Central Mississippi (The Pines)
    Posts
    993
    Location
    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

    David


  5. #5
    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.

  6. #6
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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.



    Quote Originally Posted by 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.
    Attached Files Attached Files
    Greg

    Visit my website: http://gregmaxey.com

  8. #8
    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...
    [VBA]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
    [/VBA]

    and in a separate module...

    [VBA]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
    [/VBA]

  9. #9
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Dude,

    Do you have a real name?

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

    You might try something like this:

    [VBA]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
    [/VBA]
    Greg

    Visit my website: http://gregmaxey.com

  10. #10
    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.

    [VBA]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[/VBA]

    and then used it like so...

    [VBA]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[/VBA]


    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...!

  11. #11
    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!

    [VBA]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[/VBA]

  12. #12
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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:

    [vba]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
    [/vba]

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

    Visit my website: http://gregmaxey.com

  13. #13
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Another way is to store related values in document variables:

    [vba]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
    [/vba]
    Greg

    Visit my website: http://gregmaxey.com

  14. #14
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location

    Consolidated Examples

    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.
    Attached Files Attached Files
    Greg

    Visit my website: http://gregmaxey.com

  15. #15
    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.

    [VBA]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[/VBA]

  16. #16

    Using XML

    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?

  17. #17
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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.



    [VBA]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[/VBA]

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

    [VBA]
    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)[/VBA]

    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:

    [VBA]Dim oTbl As Word.Table
    Set oTbl = ActiveDocument.Bookmarks("HiddenTable").Range.Tables(1)
    [/VBA]
    Greg

    Visit my website: http://gregmaxey.com

  18. #18

    I'm going with #3

    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:

    [VBA]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[/VBA]

    [VBA]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[/VBA]

  19. #19
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    You're welcome. Good luck. I have done that much with XML so I don't really know what the issues would be.
    Greg

    Visit my website: http://gregmaxey.com

  20. #20
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •