Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 07-17-2018, 04:58 AM
Edwords Edwords is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jun 2018
Location: London
Posts: 13
Edwords is on a distinguished road
Default Assigning Currency values to checkbox with sum presented as part of paragraph

Im trying to assign a currency value to a check box (using content control), which is dependent on the type of service it is. For example a full report is 9,000, fast report 4,500.... there are 8 different options to select from, some may have the same values. clients are allowed to select more than one report type and so the values will have to be added. the sum of this will not be displayed in the same table but instead within a paragraph on a different page (within the same doc).



That total will then be used to work out another two figures located within the same paragraph. I have highlighted the figures which will need to be calculated within the document attached.

I've tried using a macro but I cant seem to work out the correct coding, as ive never attempted this in Word. the solution that i have been trying is not set in stone and would actually welcome a suggestion which doesnt use macros.

hope someone can help, Thanks!
Attached Files
File Type: docx Application form.docx (18.8 KB, 1 views)
Reply With Quote
  #2  
Old 07-17-2018, 03:36 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Although there is a table at the top of your document with eight checkboxes, there is nothing in your document that appears to correspond with your full report, fast report, etc. description. Assuming those are the checkboxes to which you refer, you might try something like the attached.

To change the output amounts, press Alt-F11 to expose the macro coding. There you will see eight lines containing strings in the form of:
"|9,000|191,000|91,000|9,000"
These lines correspond to the checkboxes in your table. Changing the values here will alter what gets output into the four corresponding content controls at the end of the document.

Do note that using your form will require the users to allow macros to run; it also requires that they use a version of Word (not Open Office, for example), that fully supports content controls (only Word 2010 & later on PCs, 2013 & later on Macs).
Attached Files
File Type: docm Application form.docm (39.6 KB, 4 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 07-18-2018, 02:03 AM
Edwords Edwords is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jun 2018
Location: London
Posts: 13
Edwords is on a distinguished road
Default

This works great! Thank you for taking time out to help me, been stuck on this for some time now.

How could i edit the code so that any number of reportas can be selected and the amounts added and subtracted accordingly at the bottom of the document?

As i wont personally know what version of office will be used to open the document, is there a way to do this without using a macro? I feel that this would make the form a lot more user friendly, as i know some people are reluctant to allow macros to run.

Thanks again for you help!!!
Reply With Quote
  #4  
Old 07-18-2018, 03:08 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Anything using checkboxes will require a macro. To obviate the need for a macro and to enable the form to be used on any version of Word, PC or Mac, you might use formfields. See attached.

I don't understand your question.
Attached Files
File Type: doc Application form.doc (75.5 KB, 3 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 07-18-2018, 03:44 AM
Edwords Edwords is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jun 2018
Location: London
Posts: 13
Edwords is on a distinguished road
Default

thanks again for all the help!

My question was - often more than one type of service will be selected e.g. Green fleet Review, AQ Review and Fleet support in one form- what would i have to add to the code for it to add the value of each report selected together before displaying the total in the bottom paragraph. (this is in reference to the first document send using the macro)

Using the formfields looks good, but the on the example you've sent all i can do is select from the drop down menu (and can only select one report type at a time), the rest of the document is locked and the values are not updating, instead the word "amount" is displayed.
Reply With Quote
  #6  
Old 07-18-2018, 03:49 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

To access the rest of the document you'd have to either:
a) insert Section breaks immediately after the dropdown and immediately before the conditions statement; or
b) insert additional formfields anywhere else you want to allow user input (which makes the overall form more robust).

Adding multiple selections wouldn't be possible without recourse to a macro and would warrant reversion to formfield checkboxes, at least. You'd also have to clarify how allowing multiple selections would affect the conditions statement.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 07-18-2018, 04:27 AM
Edwords Edwords is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jun 2018
Location: London
Posts: 13
Edwords is on a distinguished road
Default

I think using the Macro would be the preferred way to go as multiple selections will always have to be made for the report types.

I've not used formfield check boxes before (if using the macro could I not continue to use the content control check boxes?) , how will they be referred to in the code? and what will i have to change in the code to clarify how multiple selections would affect the conditions statement.

Thanks again for you help, I've been stuck on how to do this for a while now and your help has been fantastic!!!
Reply With Quote
  #8  
Old 07-18-2018, 03:21 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

The reason for using formfield checkboxes is that those are supported on all Word versions, both PC and Mac, whereas content controls are not. The only Word version that doesn't support macros is Mac Word 2008, which is little used these days.

The coding for formfield checkboxes is quite different and, without knowing how you envisage the conditions statement changing according to which options are chosen, it's impossible to do any meaningful coding. Even the code I provided for content controls would have to be completely re-written to accommodate multiple selections (if you try the original solution, you'll see only one selection can be made).
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 07-19-2018, 03:00 AM
Edwords Edwords is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jun 2018
Location: London
Posts: 13
Edwords is on a distinguished road
Default

I used the original solution you provided as this worked great, just saved the document as a normal word document before sending out, which works fine as the services to be provided will for now be predetermined before being sent out.

The only issue I'm now facing is that i couldn't change the code to allow for multiple selections. There are no conditions as to what service can be provided, any mix is permitted, how can I adjust or add to the original solution you provided so it can allow multiple selections and perform the correct addition/subtractions?

thanks again for your help
Reply With Quote
  #10  
Old 07-19-2018, 05:38 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You still haven't said how this would affect the conditions statement. And, as I said:
Quote:
without knowing how you envisage the conditions statement changing according to which options are chosen, it's impossible to do any meaningful coding. Even the code I provided for content controls would have to be completely re-written to accommodate multiple selections
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #11  
Old 07-19-2018, 06:29 AM
Edwords Edwords is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jun 2018
Location: London
Posts: 13
Edwords is on a distinguished road
Default

Sorry i thought i did answer it, by saying that any combination of reports can be made. Im not too clear towards the conditions statement you are referring to in the code.

there is a total of 8 report types to be selected from each represented by a check box. The form should allow for any number of report types to be selected and any combination of reports is also allowed. the only thing that would differ is the price attached to each report. -----hope this explains what ive been trying to achieve.

would it be possible to code something like that for word. Sorry for my been clueless on this topic. you help have been a live saver
Reply With Quote
  #12  
Old 07-19-2018, 07:10 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

So what happens when two or more options are chosen with different pricings?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #13  
Old 07-19-2018, 07:22 AM
Edwords Edwords is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jun 2018
Location: London
Posts: 13
Edwords is on a distinguished road
Default

When two or more reports with different prices are selected then the prices should be summed up. That total sum is the number to be displayed in the paragraphs located lower in the document.
That total sum is also the number to be used to subtract from the 200,000 and 100,00 figures in the paragraph.

for example if three reports are selected: green fleet report (€9,000), a efficient fleet report (€4,500) and a AQ report (€9,000) then the code should sum up the pricing set for each of these (€22,500) and display that figure in the paragraph.

I hope that answers your question, please let me know if im missing anything. Thank you
Reply With Quote
  #14  
Old 07-19-2018, 05:12 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Try the attached. It uses formfields and the .doc format for maximum cross-platform support.
Attached Files
File Type: doc Application form - Formfields.doc (80.5 KB, 2 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #15  
Old 07-20-2018, 04:11 AM
Edwords Edwords is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jun 2018
Location: London
Posts: 13
Edwords is on a distinguished road
Default

Thats works great, thhank you so much wouldnt have been able to get here without your help.

Just one more question: How/where do i edit the values assigned to each report (check box), I've been trying to figure it out form the code but its not clear to me. I've never used formfields so Im unaware of how to edit/change the values assigned to them.

the values im trying to assign to each box are:
- AQ summary, Energy efficient fleet, fleet support = €5,500
- ULEV, green fleet, grey fleet, AQ review = €10,500
- HGV review = €15,500

thanks again for your help
Reply With Quote
Reply

Tags
addition, assign values, subtracting

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Assigning values to form fields and calculating results AustralianKate Word Tables 10 05-15-2018 10:53 PM
Can I add part of a paragraph to the document map? Jennifer Murphy Word 1 05-28-2017 11:23 PM
Assigning values to formfield checkboxes in a table mammiano Word Tables 13 01-12-2015 06:27 AM
Set style for part of paragraph meileetan Word 7 06-07-2013 01:09 AM
Assigning Values to content control checkboxes and calculating results creative cathy Word Tables 13 10-07-2012 08:52 PM


All times are GMT -7. The time now is 04:54 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft