Hi



I have 3 drop down boxes on a Word form. Lets call them DD1, DD2 and DD3

The selection of DD1 determines the List Items in DD2 and the selection of DD2 determines the List Items of DD3.

At the moment this is hard coded into a Word module but I would prefer to use ranges of cells in Excel that can be maintained by the users.

I was thinking of having a Worksheet for each list and the Worksheet name be the same as the item selected.

So, if someone selects "Fresh Fruit" from DD1, this would populate DD2 with the list that is in the Worksheet called "Fresh Fruit".

If someone then selects "Variety of Apples" from DD2 (the "Fresh Fruit" list), this would would then populate DD3 with the list in the "Variety of Apples" Worksheet.

I know how to use RowCount and loop functions to use the 'ListEntries.Add' function, but I'm just not sure how to reference the Excel Workbook in the first place. I don't want the Workbook to open, just to be referenced.

Any help would be appreciated.

See, for example: https://www.msofficeforums.com/word-...html#post46287

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

Wow. Is this as simple as it can get ? I thought it might have been simpler than that.

Are there any suggestions that I might follow what I described in my original post - just to keep the code really simple.

You could use ADODB to read the worksheet, which would be faster, but it wouldn't be any simpler. What you are asking for is complex.

You might find http://gregmaxey.com/word_tip_pages/...functions.html useful.

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

Thanks

I followed your link but I didnt see the answer on there. Maybe I wasnt looking in the right place.

Is there a simple was that (from the Word VBA module) of...

- Counting the number of rows used on a particular worksheet
- Returning a value from a particular cell

I'm sure I could work out what I'm trying to do from that

Sure, you could simplify the code I posted - by deleting the error-checking, for example - but then you'd risk it falling over. A considerable portion of the code goes into checking whether Excel is already running and whether the workbook exists and whether someone else has it open (which might mean the data are changing).

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

Now cross-posted at: https://social.technet.microsoft.com...ges?forum=word
without having the decency to mention the fact that a comprehensive answer has been given here.

For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

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