none
MS Word 2013 Content Control VBA Code RRS feed

  • Question

  • Hello,<o:p></o:p>

    I have an MS
    Word Container Control (Word 2013) which populates with an Account Number and a
    Description. I wish for the user to click on this combo box (which initially
    states "Click Here" in the control. When the User clicks there the
    code runs to populate the combo box. After a user selects a specific item
    in the combo box - the other container controls are populated with appropriate
    information according to that selection.<o:p></o:p>

    The population code of the combo box is as follows:

    Sub LoadOpportunityList()
    
    'On Error GoTo LoadOpportunityError
    
        Set opportunityList = ActiveDocument.SelectContentControlsByTitle("OpportunityNumber").Item(1)
        Dim cnnLoadOpportunity As New ADODB.Connection
        Dim rstLoadOpportunity As New ADODB.Recordset
        ' Need to update this with URL HTTP and also with error trapping
        ' If Trusted Connection doesn't work, utilize a specific read only user account with access only to a specific view
        
        cnnLoadOpportunity.Open "Provider=SQLOLEDB;" & _
                "Data Source=(local);" & _
                "Initial Catalog=AdventureWorks2012;" & _
                "Trusted_Connection=yes;"
        rstLoadOpportunity.Open "SELECT Distinct NationalIdNumber, JobTitle FROM [AdventureWorks2012].[HumanResources].[Employee] order by NationalIdNumber;", _
            cnnLoadOpportunity, adOpenStatic
        rstLoadOpportunity.MoveFirst
        
        ' Clear Combo Box List
        
        With ActiveDocument.SelectContentControlsByTitle("OpportunityNumber").Item(1).DropdownListEntries
            .Clear
            
            'Populate Combo Box
            Do
                .Add rstLoadOpportunity![NationalIdNumber] & " - " & rstLoadOpportunity![JobTitle]
                rstLoadOpportunity.MoveNext
            Loop Until rstLoadOpportunity.EOF
            
        End With
        
        'GetOpportunityDetails ("Vice President of Engineering")
        
           ' Find out if the attempt to connect worked.
       If cnnLoadOpportunity.State = adStateOpen Then
          MsgBox "Welcome to Database!"
       Else
          MsgBox "Connection to Database is not available. Please contact IT Help Desk for Support."
       End If
        
        rstLoadOpportunity.Close
        cnnLoadOpportunity.Close
        Set rstLoadOpportunity = Nothing
        Set cnnLoadOpportunity = Nothing
        
    LoadOpportunityExit:
        On Error Resume Next
        cnnLoadOpportunity.Close
        Set rsLoadOpportunity = Nothing
        Set cnnLoadOpportunity = Nothing
        Exit Sub
        
    LoadOpportunityError:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbExclamation, "Error in ConnectionTest" _
        & "Connection to Database is not available. Please contact IT Help Desk for Support."
        Resume LoadOpportunityExit
       
        
    End Sub

    This code works great.

    I'm not certain how to put the code in to execute the update operation for other controls. I'm presuming it needs to be on the Content Control On Exit Event.

    The Content Control Combo Box has the following ID Number: 1399939120

    This code would initialize the combo box with the User Directions (Presumably when creating Only a new document).

    Sub SetInitialComboBoxDirections()
     Dim oCC As ContentControl
     Set oCC = ActiveDocument.ContentControls("1399939120") 'Replace # with actual
     Number Values
     oCC.Range.Text = "Click Here to Select an Account."
     End Sub

    1) How would I extract then just the Account Number of the Content Control. The Description is helpful in combo box with the Account Number, but I don't want to print the combo box out with the Account Number and the Description - just the Id Number. 

    2) what is the syntax for code for updating other document content controls after Combo Box on exit (provided that is the best method).

    3) I'd prefer to use the ControlName itself rather than the IdNumber of the Control, since it is easier to refer to (or utilize code to do a lookup of the Id, if that is best, on the fly).

    Note: I put the Combo box in the Document Header, but also perhaps it should be in the main body of the document.

    Thank you,


    John



    • Edited by vsla Friday, May 1, 2015 6:42 PM
    Friday, May 1, 2015 6:26 PM

All replies