Auto fill drops down using VBA code based on selection

AgilityJS · #1 10-27-2015, 08:05 AM

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.

macropod · #2 11-02-2015, 04:29 PM

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

PS: You might want to check whether you have the right costings for the 350kW, 400kW, 650kW & 700kW entries.

AgilityJS · #3 11-03-2015, 06:05 AM

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?

macropod · #4 11-03-2015, 02:31 PM

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.

AgilityJS · #5 11-03-2015, 06:30 PM

This is amazing, I figured there was a better way to do it.

Not that I have any clue how you did it

macropod · #6 11-03-2015, 07:50 PM

Quote:

Originally Posted by AgilityJS

Not that I have any clue how you did it

Unprotect the document and press Alt-F9. All will be revealed.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Form auto fill based on a different field value.	stct	Word	31	05-02-2024 04:48 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