How to populate other fields when a dropdown was changed

[AhKing]

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

 

[macropod]

The 25-itme limit applies to dropdown formfields. No such limit applies to dropdown content controls. For code to populate such a dropdown from an Excel workbook, see: http://www.msofficeforums.com/word-vba/16330-how-import-list-excel-into-drop-down.html#post46287. Further down in the same thread is a link to another thread explaining how you can use selections from the dropdown to populate other content controls, etc.

 

[AhKing]

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

 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?

 

[macropod]

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.

 

[AhKing]

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.

 

[macropod]

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

 

[AhKing]

But i did. I set my vba in word to reference at Microsoft Excel Object 15.0

 

[macropod]

On what row are you getting the error?

 

[AhKing]

funny thing is i dont get a debug button when debugging your code. but i got an error after this line executed

LRow = .Cells(.Rows.Count, 1).End(-xlUp).Row

As i said, my error is the same with celias.......

 

[AhKing]

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

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]

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?

 

[macropod]

You shouldn't have (-xlUp), just (xlUp).
The line:

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:

Further down in the same thread is a link to another thread explaining how you can use selections from the dropdown to populate other content controls, etc.

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.

 

[AhKing]

I'm really not in a position to advise.

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

 

[macropod]

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:

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.