Accessing the Value from a dropdownlist that is in a cell in a Word Table

Question

I have a Word document that is used to provide survey responses by users. They then send the word document back to me. The documents contains a number of 2 column tables. In each row, column 1 contains text (the question) and column 2 contains one dropdown list. My code can easily access the text of the chosen response. But I am trying to get to the value associated with the chosen response that I have coded into the dropdown list item when I created the table.

My problem seems to be that I cannot reference the dropdownlist in the cell as I move through each row of the tables.

Private Sub CommandButton1_Click()
    
    Dim Tb1, Tb2 As word.Table
    Dim irow, icol, i, j, Tabnum As Integer
    Dim Strng As String
    Dim RowCount, Responsevalue As Long
    Dim File1 As Document
    Dim rngDoc As Range
    Dim ccMyContentControl As ContentControl
    '
    '
    'First Open Survey Data File
    
    
    'Display a Dialog Box that allows to select a single file.
    'The path for the file picked will be stored in fullpath variable
    With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Word Files", "*.docx; *.doc; *.docm", 1
        'Show the dialog box
        .Show
        
        'Store in SurveyData variable
        SurveyData = .SelectedItems.Item(1)
        
        Documents.Open SurveyData
        Documents(SurveyData).Activate
        Tabnum = ActiveDocument.Tables.Count
        
        For i = 1 To Tabnum
                   
            ' Skip Header Row

            For irow = 2 To ActiveDocument.Tables(i).Rows.Count
                         
                Strng = ActiveDocument.Tables(i).Cell(irow, 2).Range.Text
             '    Responsevalue = ActiveDocument.Tables(i).Cell(irow, 2).ContentControl.Range.Result
                Let ccMyContentControl = ActiveDocument.Tables(i).Cell(irow, 2).wdContentControlDropdownList
                With ccMyContentConrol
                For j = 1 To .DropdownListEntries.Count
                   If .DropdownListEntries(j).Text = Strng Then _
                       Responsevalue = .DropdownListEntries(j).Value
                   Else
                       Responsevalue = 99
                    MsgBox Responsevalue
                Next j
                End With
                 
            Next irow
            
        Next i
       
    End With
    
'
' Close Survey Response file
'
    
End Sub

Oscar Sun · Accepted Answer · 2023-07-27T12:56:39.793

Your code has a lot of problems, as I mentioned briefly in the comments. Try to see if the code below worked for you.

Private Sub CommandButton1_Click()
    
    Dim SurveyData
    
    
    Dim Tb1, Tb2 As word.Table
    Dim irow, icol, i, j, Tabnum As Integer
    Dim Strng As String
    Dim RowCount, Responsevalue As Long
    Dim File1 As Document
    Dim rngDoc As Range
    Dim ccMyContentControl As ContentControl
    '
    '
    'First Open Survey Data File
    
    
    'Display a Dialog Box that allows to select a single file.
    'The path for the file picked will be stored in fullpath variable
    With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Word Files", "*.docx; *.doc; *.docm", 1
        'Show the dialog box
        .Show
        
        'Store in SurveyData variable
        SurveyData = .SelectedItems.Item(1)
        
        Rem These 3 lines should be revised as follows will be better
        Documents.Open SurveyData
        Documents(SurveyData).Activate
        Tabnum = ActiveDocument.Tables.Count
'        Dim d As Document
'        Set d = Documents.Open(SurveyData)
'        d.Activate
'        Tabnum = d.Tables.Count
        
        
        For i = 1 To Tabnum
                   
            ' Skip Header Row

            For irow = 2 To ActiveDocument.Tables(i).Rows.Count
                         
                Strng = ActiveDocument.Tables(i).Cell(irow, 2).Range.Text
                
                Rem Did you stuck here? You have to use `Set` to refer to an object!
                Rem "Use the Set statement to assign object references to variables." ' https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/let-statement#:~:text=user%2Ddefined%20types.-,Use%20the%20Set%20statement%20to%20assign%20object%20references%20to%20variables.,-Example
             '    Responsevalue = ActiveDocument.Tables(i).Cell(irow, 2).ContentControl.Range.Result
                'Let ccMyContentControl = ActiveDocument.Tables(i).Cell(irow, 2).wdContentControlDropdownList
                
                Set ccMyContentControl = ActiveDocument.Tables(i).Cell(irow, 2).Range.ContentControls(1) ' You have to use Index or Name to invovk the item in a collection like `ContentControls`. Use 1 if there is only one ContentControl in the cell
                
                Rem Wrong spelling.
                'With ccMyContentConrol
                With ccMyContentControl 'Must be! Watch your spelling. You can use `Option Explicit` at the top of this module to prevent any wrong spelling.
                
                For j = 1 To .DropdownListEntries.Count
                
                    Rem You've used `Else`, then can not have the "_" !
                   'If .DropdownListEntries(j).Text = Strng Then _

                   Rem it Should be as the follow
                   'If .DropdownListEntries(j).Text = Strng Then
                   If .DropdownListEntries(j).Text & Chr(7) = Strng Then

                   
                       Responsevalue = .DropdownListEntries(j).Value
                   Else
                       Responsevalue = 99
                       MsgBox Responsevalue
                    
                    Rem You lost this line
                   End If
                Next j
                End With
                 
            Next irow
            
        Next i
       
    End With
    
'
' Close Survey Response file
'
    
End Sub

Thank you so much for this wonderful help. My code is now working, but I did have to change the in the matching of the text from adding the ASCII character 7 to adding both ASCII Characters 13 and 7 in that order. Must be something to do with the way Word stores combo list values. — PLL, Jul 28 '23 at 03:21
@PLL Sure, just like your guess, like `.DropdownListEntries(j).Text & Chr(7)` , I had to watch the `Strng`'s value in debugging to get the exact value too. So if this answer solves your problem, plz accept it to close your question for future reference. Thanks. — Oscar Sun, Jul 28 '23 at 03:34

Accessing the Value from a dropdownlist that is in a cell in a Word Table

1 Answers1