Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-27-2015, 08:05 AM
AgilityJS AgilityJS is offline Auto fill drops down using VBA code based on selection Windows 10 Auto fill drops down using VBA code based on selection Office 2013
Novice
Auto fill drops down using VBA code based on selection
 
Join Date: Oct 2015
Location: Charlotte, NC
Posts: 3
AgilityJS is on a distinguished road
Default 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.
Attached Files
File Type: docm Generator Costs at Time of Disaster.docm (33.6 KB, 15 views)
Reply With Quote
  #2  
Old 11-02-2015, 04:29 PM
macropod's Avatar
macropod macropod is offline Auto fill drops down using VBA code based on selection Windows 7 64bit Auto fill drops down using VBA code based on selection Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 11-03-2015, 06:05 AM
AgilityJS AgilityJS is offline Auto fill drops down using VBA code based on selection Windows 10 Auto fill drops down using VBA code based on selection Office 2013
Novice
Auto fill drops down using VBA code based on selection
 
Join Date: Oct 2015
Location: Charlotte, NC
Posts: 3
AgilityJS is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 11-03-2015, 02:31 PM
macropod's Avatar
macropod macropod is offline Auto fill drops down using VBA code based on selection Windows 7 64bit Auto fill drops down using VBA code based on selection Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
Attached Files
File Type: docx Generator Costs at Time of Disaster.docx (27.3 KB, 15 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 11-03-2015, 06:30 PM
AgilityJS AgilityJS is offline Auto fill drops down using VBA code based on selection Windows 10 Auto fill drops down using VBA code based on selection Office 2013
Novice
Auto fill drops down using VBA code based on selection
 
Join Date: Oct 2015
Location: Charlotte, NC
Posts: 3
AgilityJS is on a distinguished road
Default

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

Not that I have any clue how you did it
Reply With Quote
  #6  
Old 11-03-2015, 07:50 PM
macropod's Avatar
macropod macropod is offline Auto fill drops down using VBA code based on selection Windows 7 64bit Auto fill drops down using VBA code based on selection Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by AgilityJS View Post
Not that I have any clue how you did it
Unprotect the document and press Alt-F9. All will be revealed.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto fill drops down using VBA code based on selection 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
Auto fill drops down using VBA code based on selection how to populate textbox based on combobox selection in word IvanGeorgiev Word VBA 1 02-21-2013 07:28 PM

Other Forums: Access Forums

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


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