I apologize in advance for this long/detailed post. I need to create the template or userform experience described below. I did it as a template (actually two templates, one pulling from the other) in Word 2011 for Mac, because that’s what I use. I did it using a combination of bookmarks, AUTOTEXTLIST, IF, and INCLUDETEXT formulas, and had it working almost perfectly (I didn’t have to use any VBA – my experience with VBA is limited). But the end users use Windows, so I’ve migrated it (to Word 2016 for Windows), and have had problems making it work. I think it would be best to start from scratch for several reasons, especially because with Word 2016 for Windows I can now use content controls (which I’ve never used before). I’ve also never used userforms. I’ve searched for and read many forum posts on cascading/dependent drop down lists, data mapping, etc., and I’m beginning to get in over my head – Dims and Strings and what-not. One issue is that, although I’ve been able to follow the instructions of some of the experts here on creating cascading drop down lists, I’ve only been able to do that with the “slave” list items hard coded in the VBA, and I want to have all list items pulled from an external source document.



Anyway, here’s what I envision:

1. A source document containing the following (it’s easiest for me to see it as tables, but I understand that it may not be ideal or possible to do it all that way):
a. Table 1 – Column 1 “Case Types” Column 2 “Special Responses” (Each Case Type in Column 1 has one or more Special Responses in Column 2, separated by commas. I suspect that, given what I need in 2c(ii) below, this may not be the proper/ideal way to structure this – for example, perhaps each Case Type should have its own table, with a row for each Special Response available for that Case Type.)
b. Table 2 – Column 1 “Standard Responses” Column 2 “Standard Response Text”
c. Table 3 – Column 1 “Special Responses” Column 2 “Special Response Text”
d. Table 4 – Column 1 “Injury Item” Column 2 “Injury Item Text”

2. User makes the following selections (on either a user form, or the top portion of a template):
a. Case Type DropDown List – source for list items is Column 1 of Table 1
b. Injury Items – check boxes (a check box for each “Injury Item” contained in Column 1 of Table 4, but presumably hard coded into the form rather than sourced from that table)
c. 40 Response DD Lists (Response 1, Response 2, etc.), – list items for each Response DD List include (i) all of the Standard Responses in Column 1 of Table 2, and (ii) the Special Responses listed in Column 2 of Table 1 for the Case Type selected in the Case Type DD List

3. The output (i.e., the document generated by the user form, or the bottom portion of the template) would be numbered paragraphs corresponding to the Response DD Lists for which the user has made a selection (i.e., if the user has only made selections in the first 15 Response DD Lists, then there would be only 15 numbered paragraphs). The text of each paragraph would be taken from Column 2 of Table 2 (if the user has selected one of the Standard Responses) or Column 2 of Table 3 (if the user has selected one of the Special Responses), with one exception. If the user selects “Injuries” in one of the Response DD Lists (“Injuries” would be one of the Standard Responses), then the text of the corresponding paragraph would be a list, each item of which would be the Injury Item Text in Column 2 of Table 4 for which the corresponding Injury Item check box has been checked.

I know this involves several issues, and again, apologies for the length, but I wanted to be as specific as possible in describing my goal. Many thanks in advance for any help that the experts here can provide!

For an idea of what's possible, see: https://www.msofficeforums.com/word-...tml#post119230
The source workbook for the dropdowns is in post #3 of that thread. Some of the coding complexity in the template is a consequence of the way the data were laid out in the workbook and could be simplified with a more suitable worksheet layout.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks Paul. I like the idea of a single Excel workbook with multiple worksheets as the source. My template does not require the ability to add items like the example in your link does, so that complication could be avoided. But I see a few issues with my template that don't exist in that example:

1. Each slave dropdown would need to include list items not only from the sheet corresponding to the item selected in the master dropdown, but also from a different sheet that applies irrespective of the item selected in the master dropdown.

2. An item being selected in a slave dropdown should result in particular text being shown in the corresponding paragraph in the output portion of the template. I know this is a basic function, but I couldn't get it to work. Can it be done by a series of IF statements for each paragraph that cover each possible list item in the corresponding slave dropdown? (In my Mac version, I put the long series of IF statements in a separate document and bookmarked it, and used INCLUDETEXT to bring it into each paragraph of the template. The IF statements referred to "ParCurrent", and for each paragraph in the template I defined the corresponding combo box as "ParCurrent" before the INCLUDETEXT formula)? Incidentally, to refer to a dropdown list in a formula do you use the title? Seems logical but it didn't work for me.

3. The particular text generated for most of the possible slave dropdown list items would be a fixed string. But a few of the list items would need to generate text that includes information input by the user into text fields in the input portion of the template, and one particular list item (as described in my original post) would need to generate text that depended on which checkboxes are checked by the user in the input portion of the template.

Any ideas how those issues might be addressed?

All of those issues can be addressed without much difficulty. Before proceeding, though, we'd need to see what your desired document should look like and what the data are.

Without actually seeing the document & data, it would be difficult for anyone to devise a solution. Can you attach the document & data to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Of course. I've attached a Word template and an Excel source workbook, which I tried to make self-explanatory. The structure is a bit different than I described in my original post, but not much. (Note that the occurrences of "[ADD OR REMOVE AS NEEDED]" or "[INSERT CUSTOM TEXT]" in some of the cells in the workbook are not meant as placeholders for me, but rather are to literally feed that way into the output portion of the template, to later be edited by the user.)

Thanks again so much for your help!

Attached Files

	BH BP Test Template.dotm (152.7 KB, 36 views)
	BH BP TestData Source.xltx (18.3 KB, 31 views)

There's a function on my web site at http://www.gmayor.com/Userform_ComboBox.html which will assist you to read Excel data into a userform combo or list box.

__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com

Thanks Graham. I'm thinking of going with dropdown lists, but if I end up using combo boxes that looks great. Much appreciated!

The code works with list boxes and combo boxes.

__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com

Paul, did you have a chance to look at the files I posted last week? Your further thoughts would be tremendously appreciated!!

Can anyone help me address the issues that macropod said in his earlier post could be addressed without too much difficulty? I uploaded the documents I am working on in a later post, as he suggested. Any help would be greatly appreciated!! Thanks.

Try the attached. It's not complete, but should give you an idea of what it can do. Note that I've edited and renamed both files, which should be kept in the same folder.

Attached Files

BH BP.zip (75.2 KB, 34 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Oh, I see you're on a Mac - the code assumes a Windows OS. Let me know if that's an issue in your work environment.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Wow, you put a lot of work into this, Paul. Thank you so much! I can't tell you how much I appreciate this.

I'm eager to explore what you've done, but I’m getting an error message from the get-go. First I select MV Pedestrian Knockdown as the Case Type, and when I click on the dropdown list for Response Type, I get “Run-time error ‘5825’: Object has been deleted.” (see attached screenshots of the error message and the highlighted line of code when I click Debug). This happens whenever I select a new Case Type and then try to select a Response Type. Then, after I click End in the error pop-up, “None” is the only item in the Response Type dropdown list.

(By the way I normally work on a Mac, but the end users of this form will be in Windows, so I installed Word for Windows on my Parallels virtual machine and going forward I will be working on these documents in Windows.)

Attached Files

	Error 5825.pdf (45.5 KB, 13 views)
	Debug 5825.pdf (137.6 KB, 11 views)

I suspect the Run-time error '5825' has something to do with your Mac Parallels environment; I'm not getting it on my PC. That said, because the 'MV Pedestrian Knockdown' Case Type contains duplicate entries (as do most others), I expect you'll get a Run-time error '6215' at the line:
If strTmp <> "" Then .DropdownListEntries.Add Text:=strTmp
That can be overcome by using:
On Error Resume Next
If strTmp <> "" Then .DropdownListEntries.Add Text:=strTmp
On Error GoTo 0
for testing only, as your final version shouldn't have duplicates.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks Paul. I was able to get around the 5825 by changing the location of the files.

You said the code is not complete -- I assume you intended to accomplish only the basic functionality (i.e., the Response Type list being dependent on the chosen Case Type, and each chosen Response Type populating the corresponding numbered paragraph), and not the more advanced functionality (i.e., having the "Common" responses appear as list items for all Response Types rather than "Common" itself being a Response Type, and the functionality I described in the highlighted portions of the template).

With that in mind, the basic functionality worked the first time I select a Case Type and then selected one or more Response Types. But when I tried to do anything else -- like change the Case Type, or change an earlier Response Type than the current one (e.g., if I have added a Response Type for #3, then try to change the Response Type for #1 or #2) -- I either got error messages or the functionality stopped. I know it is generally difficult to address code problems unless I describe each action or series of actions that causes a problem, but I ran into a problem doing anything beyond initially selecting a Case Type and then one or more Response Types. On your end, is the basic functionality maintained beyond that?

Thanks again so much for your help, and I don't want to impose or take advantage of your generosity -- please let me know if we reach a point where you feel it would be more appropriate to proceed with a commercial arrangement offline.