Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-15-2016, 03:19 AM
music_al music_al is offline Linking WORD Cascading Dropdown Boxes to Excel Windows 10 Linking WORD Cascading Dropdown Boxes to Excel Office 2013
Novice
Linking WORD Cascading Dropdown Boxes to Excel
 
Join Date: Nov 2015
Posts: 10
music_al is on a distinguished road
Default Linking WORD Cascading Dropdown Boxes to Excel

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.
Reply With Quote
  #2  
Old 02-15-2016, 04:45 AM
macropod's Avatar
macropod macropod is offline Linking WORD Cascading Dropdown Boxes to Excel Windows 7 64bit Linking WORD Cascading Dropdown Boxes to Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,793
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

See, for example: https://www.msofficeforums.com/word-...html#post46287
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 02-15-2016, 07:10 AM
music_al music_al is offline Linking WORD Cascading Dropdown Boxes to Excel Windows 10 Linking WORD Cascading Dropdown Boxes to Excel Office 2013
Novice
Linking WORD Cascading Dropdown Boxes to Excel
 
Join Date: Nov 2015
Posts: 10
music_al is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 02-15-2016, 07:50 AM
gmayor's Avatar
gmayor gmayor is offline Linking WORD Cascading Dropdown Boxes to Excel Windows 10 Linking WORD Cascading Dropdown Boxes to Excel Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,067
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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
Reply With Quote
  #5  
Old 02-15-2016, 11:30 AM
music_al music_al is offline Linking WORD Cascading Dropdown Boxes to Excel Windows 10 Linking WORD Cascading Dropdown Boxes to Excel Office 2013
Novice
Linking WORD Cascading Dropdown Boxes to Excel
 
Join Date: Nov 2015
Posts: 10
music_al is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 02-15-2016, 02:39 PM
macropod's Avatar
macropod macropod is offline Linking WORD Cascading Dropdown Boxes to Excel Windows 7 64bit Linking WORD Cascading Dropdown Boxes to Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,793
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

Quote:
Originally Posted by music_al View Post
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
[MS MVP - Word]
Reply With Quote
  #7  
Old 02-15-2016, 03:56 PM
macropod's Avatar
macropod macropod is offline Linking WORD Cascading Dropdown Boxes to Excel Windows 7 64bit Linking WORD Cascading Dropdown Boxes to Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,793
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

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
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking WORD Cascading Dropdown Boxes to Excel Help with cascading dropdown list SaraO Word VBA 25 07-01-2019 07:19 AM
Linking WORD Cascading Dropdown Boxes to Excel Word linking to Excel (hidden Excel) Doug Needham Word 5 11-04-2014 09:20 PM
Linking WORD Cascading Dropdown Boxes to Excel linking to excel from word jillapass Word VBA 5 06-08-2012 01:28 AM
Linking Excel to Word engineer_in_training Word 0 01-06-2010 01:30 PM
Automate Data from Excel into Word DropDown...? sigraves Word VBA 0 08-03-2009 06:54 AM


All times are GMT -7. The time now is 08:06 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft