How to populate other fields when a dropdown was changed

Discussion in 'Word' started by AhKing, Aug 22, 2016.

  1. AhKing

    AhKing

    Joined:
    Aug 18, 2016
    Messages:
    10
    Likes Received:
    0
    I know this question might be asked many times. Still, as a newbie on VBA coding, the solution still not clear to me. I look up on many links and most of them provide a solution or code that i can't manage to understand very well. The problem is i want to the 'Course' and the 'Duration' to populate with related data that depends on the 'Course Code' that will has been selected. The data in the dropdown of 'Code' comes from excel table which is also i have included on the attachment. I found some guide says that the dropdown limitations only up to 25 data. I'm worry if my data exceed the limitations. So about that it is still not clear to me. Also, i want to know how to populate the other text field based on dropdown selection in the same row at excel. please help
     

    Attached Files:

    AhKing, Aug 22, 2016
    #1
    1. Advertisements

  2. AhKing

    macropod Microsoft MVP

    Joined:
    Mar 2, 2012
    Messages:
    210
    Likes Received:
    15
    macropod, Aug 22, 2016
    #2
    1. Advertisements

  3. AhKing

    AhKing

    Joined:
    Aug 18, 2016
    Messages:
    10
    Likes Received:
    0
    Thanks for the response. I read that thread many times but im still cant understand certain block of code you provided there.

    i am having trouble on understanding this block of code
    Code:
     ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Clear
            For i = 1 To LRow
              ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _
                Text:=Trim(.Range("A" & i))
              'or, for example, to add the contents of column B to the content control's 'value':
              'ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _
                Text:=Trim(.Range("A" & i)), Value:=Trim(.Range("B" & i))
    Assuming i want to populate 2 input text field. How should i change this code according to my needs? Do i need to clear first and add? Does this for 1 input field only and have to create another same code for another field?
     
    AhKing, Aug 22, 2016
    #3
  4. AhKing

    macropod Microsoft MVP

    Joined:
    Mar 2, 2012
    Messages:
    210
    Likes Received:
    15
    The code in that thread is for populating a single content control. Clearly, if you want to populate another content control, you need extra code. If you're using the same data for both, it's a simple matter of adding another line. For example, after:
    ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _
    Text:=Trim(.Range("A" & i))
    add:
    ActiveDocument.SelectContentControlsByTitle("ID")(2).DropdownListEntries.Add _
    Text:=Trim(.Range("A" & i))
    If you're using the same data for the second dropdown, you need another entire loop pertaining to the relevant parts of the Excel workbook (e.g. column B) and referencing the content control that range is to populate.
     
    macropod, Aug 22, 2016
    #4
  5. AhKing

    AhKing

    Joined:
    Aug 18, 2016
    Messages:
    10
    Likes Received:
    0
    I did exactly the same as this celias did before. Also i got the same error same as his which is "Run-time error '1004': Application-defined or object-defined error". I check the path of excel, and the Sheet1 name. All is correct but i keep getting the same error.
     
    AhKing, Aug 22, 2016
    #5
  6. AhKing

    macropod Microsoft MVP

    Joined:
    Mar 2, 2012
    Messages:
    210
    Likes Received:
    15
    If you're getting that error, you evidently haven't set a reference to Excel, which comments in the code both say you need and tell you how to go about it...
     
    macropod, Aug 23, 2016
    #6
  7. AhKing

    AhKing

    Joined:
    Aug 18, 2016
    Messages:
    10
    Likes Received:
    0
    But i did. I set my vba in word to reference at Microsoft Excel Object 15.0
     
    AhKing, Aug 23, 2016
    #7
  8. AhKing

    macropod Microsoft MVP

    Joined:
    Mar 2, 2012
    Messages:
    210
    Likes Received:
    15
    On what row are you getting the error?
     
    macropod, Aug 23, 2016
    #8
  9. AhKing

    AhKing

    Joined:
    Aug 18, 2016
    Messages:
    10
    Likes Received:
    0
    funny thing is i dont get a debug button when debugging your code. but i got an error after this line executed
    Code:
    LRow = .Cells(.Rows.Count, 1).End(-xlUp).Row
    As i said, my error is the same with celias.......
     
    Last edited: Aug 23, 2016
    AhKing, Aug 23, 2016
    #9
  10. AhKing

    AhKing

    Joined:
    Aug 18, 2016
    Messages:
    10
    Likes Received:
    0
    Let me get this straight, at least i can confirm it. let just say i just want to populate only one content control. Therefore this line here
    Code:
    ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _
    Text:=Trim(.Range("A" & i))
    is for my dropdown right? and not my text input or some sort
     
    AhKing, Aug 23, 2016
    #10
  11. AhKing

    AhKing

    Joined:
    Aug 18, 2016
    Messages:
    10
    Likes Received:
    0
    Never mind i got it working. it seems your code got something that made that error. Anyway, how should i specify the field that i should populate based on my dropdown selection?
     
    AhKing, Aug 23, 2016
    #11
  12. AhKing

    macropod Microsoft MVP

    Joined:
    Mar 2, 2012
    Messages:
    210
    Likes Received:
    15
    You shouldn't have (-xlUp), just (xlUp).
    The line:
    Code:
    ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _
    Text:=Trim(.Range("A" & i))
    is to populate a dropdown content control titled 'ID'. If your content control is titled differently, change the title in the code.
    As I said previously:
    However, unless you use the code that adds whatever you want to populate the dependent content with to the dropdown's value (for which the macro in http://www.msofficeforums.com/word-vba/16330-how-import-list-excel-into-drop-down.html#post46287 already has alternate code), you'll need to use a completely different method. Since I don't know what else you'd want to use for that, I'm really not in a position to advise.
     
    macropod, Aug 24, 2016
    #12
  13. AhKing

    AhKing

    Joined:
    Aug 18, 2016
    Messages:
    10
    Likes Received:
    0
    Well since you put it that way. My problem not solved then. All i did was correcting the code thats it. Its not even help my problem. I didn't even know how to use your code to populate the dependant content control. i already know how to populate my dropdown from excel. The only thing that bring problem to me is based on the selection of the dropdown(Column A), the other 2 of my rich text content control(Column B and C) got updated as well. never mind. i will figure out something. Thanks for your help
     
    AhKing, Aug 24, 2016
    #13
  14. AhKing

    macropod Microsoft MVP

    Joined:
    Mar 2, 2012
    Messages:
    210
    Likes Received:
    15
    The thread I provide a link to already discusses how you might get the content to populate another content control using data from the Excel workbook. All you need do is read it... especially post #4. To use the code that way, though, you'll need to base your data import on the alternate code line in post #2:
    Code:
    ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _
      Text:=Trim(.Range("A" & i)), Value:=Trim(.Range("B" & i))
    If you're not getting the dependent content from Excel, you need to say what that content is and what conditions apply to its use. I'm not a mind-reader and I can't see over your shoulder.
     
    macropod, Aug 24, 2016
    #14
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. jsduiahd

    Autotext within Fields

    jsduiahd, Jul 1, 2011, in forum: Word
    Replies:
    0
    Views:
    516
    jsduiahd
    Jul 1, 2011
  2. Pathagoras
    Replies:
    0
    Views:
    324
    Pathagoras
    Mar 6, 2015
  3. Helen R
    Replies:
    0
    Views:
    389
    Helen R
    Jun 3, 2015
  4. fullcity
    Replies:
    3
    Views:
    238
    macropod
    Aug 26, 2016
Loading...