Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help, Content control and array lookup?

    Hey all
    I'm new to vba and I'm trying to get this code to work.

    Task:
    1. User selects a entry in the CC dropdownlist.
    2. If the same value exist in the file c:\test.xls column A, then return the Column B value in the same raw.
    3. Use the returned value in the CC dropdownlist (or in a new CC textbox)

    I hope someone can be kind enough, to come up with a proposal to complete this code.

    thank you


    Sub Validate_ContentControl()
    Dim oCC As ContentControl
    Dim OCCEntry As ContentControlListEntry
    Set oCC = ActiveDocument.ContentControls(1)
    For i = 1 To oCC.DropdownListEntries.Count
    If oCC.DropdownListEntries.Item(i).Text = oCC.Range.Text Then
    Set OCCEntry = oCC.DropdownListEntries.Item(i)
    End If
    Next i
    Dim xlapp As Object
    Dim xlbook As Object
    Dim xlsheet As Object
    Dim xlrange1 As Object
    Dim xlrange2 As Object
    Dim myarray As Variant
    Dim Findarray As Variant
    Dim Replarray As Variant
    On Error Resume Next
    Set xlapp = GetObject(, "Excel.Application")
    If Err Then
    bstartApp = True
    Set xlapp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    With xlapp
    Set xlbook = .Workbooks.Open("C:\test.xls")
    Set xlsheet = xlbook.Worksheets(1)
    With xlsheet
    Set xlrange1 = .Range("A1", .Range("A1").End(4))
    Set xlrange2 = .Range("B1", .Range("B1").End(4))
    Findarray = xlrange1.Value
    Replarray = xlrange2.Value
    End With
    End With
    If bstartApp = True Then
    xlapp.Quit
    End If
    Set xlapp = Nothing
    Set xlbook = Nothing
    Set xlsheet = Nothing
    Set xlrange1 = Nothing
    Set xlrange2 = Nothing
    For i = 2 To UBound(Findarray)
    If OCCEntry.Value = Findarray(i, 1) Then
    MsgBox "Found match"
    oCC.Range.Text = Replarray(i, 1)
    Else: MsgBox "None found"
    End If
    Exit For
    Next i
    End Sub

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Why do you update the same CC that triggers the code? If the Validate code is run every time the value changes then you might be falling into a terminal loop.

    If the resulting text from column B isn't already in the list of possible choices then does the CC allow that text to appear?

    How is the initial list of entries in the CC populated? If it came from the same excel source then you wouldn't need to test for a hit and may even be able to load 'column B' at the same time as building the list entries so you can then just harvest that 'second column of data' without going out to excel a second time.

    Can you post a sample Word and Excel file that saves us the pain of having to create these before being able to test your code?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    New Lounger
    Join Date
    Sep 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I learn while I build the code, I know there are several mistakes that I need to study. But I struggle as much as I can.
    The entries in the drop down are all known and entered manually, so the result in column B will always be representative. It is true that it all comes from excel, but to separate the x.xx in column A between the text and the spaces, was too difficult. Now when I think of it, the result should of course be transferred to a new CC textbox.

    I really appreciate the help I get
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Sep 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I found a similar question in this forum, maybe it's easier to use that script, to get the result of column B with som modifications?
    http://www.msofficeforums.com/word-v...drop-down.html

Posting Permissions

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