#1
|
|||
|
|||
Auto fill drops down using VBA code based on selection
Good Day,
I am new to this form and to VBA in general. I've created a document based on code I've found online and on this form and was hoping for some help on having section auto populate based on the original selection. I've got it so that once the size of the generator is selected, the costs associated with it are available in a drop down menu, but you still have to select the costs, I have hoping they could just be auto populated, without being selected. I'm sure i'm not explaining things clearly, but I've attached the document if someone could take a look. I appreciate any help, thanks. P.S. Does anyone have any suggestions on book or tools that I could use to learn further about using VBA. Thanks. |
#2
|
||||
|
||||
If you replace the ddOCost & ddWCost dropdowns with text formfields of the same name, your two macros could be reduced to:
Code:
Sub PopulatedCosts() Dim ddOCost As String, ddWCost As String With ActiveDocument Select Case .FormFields("ddSize").Result Case "20kW": ddOCost = "$993.00": ddWCost = "$1,568.00" Case "56kW": ddOCost = "$1,471.00": ddWCost = "$2,046.00" Case "100kW": ddOCost = "$1,759.00": ddWCost = "$2,334.00" Case "125kW": ddOCost = "$2,220.00": ddWCost = "$2,795.00" Case "150kW": ddOCost = "$2,322.00": ddWCost = "$2,897.00" Case "175kW": ddOCost = "$2,322.00": ddWCost = "$2,897.00" Case "200kW": ddOCost = "$2,890.00": ddWCost = "$3,465.00" Case "250kW": ddOCost = "$3,561.00": ddWCost = "$4,136.00" Case "300kW": ddOCost = "$4,821.00": ddWCost = "$5,396.00" Case "350kW": ddOCost = "$5,760.00": ddWCost = "$6,335.00" Case "400kW": ddOCost = "$5,510.00": ddWCost = "$6,085.00" Case "450kW": ddOCost = "$5,980.00": ddWCost = "$6,555.00" Case "500kW": ddOCost = "$7,201.00": ddWCost = "$7,776.00" Case "650kW": ddOCost = "$7,817.00": ddWCost = "$8,392.00" Case "700kW": ddOCost = "$6,770.00": ddWCost = "$7,345.00" Case "750kW": ddOCost = "$10,370.00": ddWCost = "$10,945.00" Case "800kW": ddOCost = "$10,656.00": ddWCost = "$11,231.00" Case "900kW": ddOCost = "$10,969.00": ddWCost = "$11,544.00" Case "1,000kW": ddOCost = "$13,495.00": ddWCost = "$14,070.00" Case "1,250kW": ddOCost = "$14,951.00": ddWCost = "$15,526.00" Case "1,500kW": ddOCost = "$19,753.00": ddWCost = "$20,328.00" Case "2,000kW": ddOCost = "$24,269.00": ddWCost = "$24,844.00" Case Else: ddOCost = "": ddWCost = "" End Select .FormFields("ddOCost").Result = ddOCost .FormFields("ddWCost").Result = ddWCost End With End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks, so much.
This works exactly like i was trying to do. I'm very new to VBA coding, do you have an suggestions on material or sites I can use to learn more? |
#4
|
||||
|
||||
Is now a good time to tell you that you could also have done this without macros? It could all be done with field coding. See attached.
Most of my Word knowledge & programming skills are self taught, developed whilst helping other users solve their Word productivity issues. A book you might find useful is The Secret Life of Word: A Professional Writer's Guide to Microsoft Word Automation, by R Delwood, published by XML Press in 2011(http://xmlpress.net/publications/word-secrets/). I contributed content for and did much of the technical review of this book. This isn't a programming book as such (though it does have some programming in it) and doesn't profess to teach you how to program. Rather, it shows how to combine Word's various tools to achieve whatever the desired result might be. Another that I contributed to (and has much more programming in it) is Word Hacks, by A Savikas, published by O'Reilly Media in 2005 (http://shop.oreilly.com/product/9780596004934.do). I contributed content for this book also. Although it pre-dates Office 2007, much of the content is still relevant.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
This is amazing, I figured there was a better way to do it.
Not that I have any clue how you did it |
#6
|
||||
|
||||
Unprotect the document and press Alt-F9. All will be revealed.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Form auto fill based on a different field value. | stct | Word | 26 | 06-15-2019 04:08 PM |
VBA Code to Hide columns based on selection on prior sheet | Silver1379 | Excel Programming | 0 | 04-15-2015 08:40 AM |
text based on Combo box selection | rosscortb | Word VBA | 3 | 03-16-2015 06:57 PM |
Inserting a particular image based on a combobox selection | LeonieD | PowerPoint | 2 | 06-27-2014 05:39 PM |
how to populate textbox based on combobox selection in word | IvanGeorgiev | Word VBA | 1 | 02-21-2013 07:28 PM |