Excel Hints

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
    991
    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
    991
    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
    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

  7. #7
    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  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...
    VB:
    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 
    
    
    Formatting tags added by mark007
    and in a separate module...

    VB:
    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 
    
    
    Formatting tags added by mark007

  9. #9
    Dude,

    Do you have a real name?

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

    You might try something like this:

    VB:
    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 
    
    
    Formatting tags added by mark007

  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.

    VB:
    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 
    
    
    Formatting tags added by mark007
    and then used it like so...

    VB:
    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 
    
    
    Formatting tags added by mark007

    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!

    VB:
    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 
    
    
    Formatting tags added by mark007

  12. #12
    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:

    VB:
    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 
    
    
    Formatting tags added by mark007
    But then we are back to using the .Text property vice the .Value property. Unfortunately CCs are as you say "weak."

  13. #13
    Another way is to store related values in document variables:

    VB:
    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 
    
    
    Formatting tags added by mark007

  14. #14

    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  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.

    VB:
    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 
    
    
    Formatting tags added by mark007

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



    VB:
    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 
    
    
    Formatting tags added by mark007
    Since you don't need an column that contains a number then you could just:

    VB:
    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) 
    
    
    Formatting tags added by mark007
    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:

    VB:
    Dim oTbl As Word.Table 
    Set oTbl = ActiveDocument.Bookmarks("HiddenTable").Range.Tables(1) 
    
    
    Formatting tags added by mark007

  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:

    VB:
    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 
    
    
    Formatting tags added by mark007
    VB:
    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 
    
    
    Formatting tags added by mark007

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

  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
  •