Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #16  
Old 11-23-2016, 02:56 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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


As asked previously, kindly attach your documents here instead of on a different site.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #17  
Old 11-23-2016, 04:15 PM
Father Father is offline Windows 7 32bit Office 2013
Novice
 
Join Date: Nov 2016
Posts: 29
Father is on a distinguished road
Default

Sorry about that.

I'm referring to section 4. LOCATION
2016 EOD Form Form Fields.docm
Reply With Quote
  #18  
Old 11-23-2016, 06:51 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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

Quote:
Originally Posted by Father View Post
I'm talking about this:
Dept = { FORMDROPDOWN }
{IF{REF Dropdown1 \* MERGEFORMAT}= "HR" "001"}
When the number, in this case 001, is generated by the IF condition, how do you use that number in a calculation/formula? The "001" has no bookmark to use in another formula. What would be the reference?
The 001 is simply the displayed output. You don't reference that in a calculation; only whatever Dropdown1 returns. You can also lose the ' \* MERGEFORMAT' - it's quite unnecessary. So, if you want to use th, you'd code along the lines of:
{={IF{REF Dropdown1}= "Finance" 2 {IF{REF Dropdown1}= "HR" 1 0}}*5}
where 5 is whatever you want to multiply the selected item's value by. However, that's really no different than:
{IF{REF Dropdown1}= "HR" 5}{IF{REF Dropdown1}= "Finance" 10}
I suggest you take a look at my Microsoft Word Field Maths Tutorial, available at:
http://windowssecrets.com/forums/sho...Maths-Tutorial
and:
http://www.gmayor.com/downloads.htm#Third_party
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #19  
Old 11-23-2016, 06:57 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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

Quote:
Originally Posted by Father View Post
I'm trying to do a similar condition formula where it displays coordinates.
It's not showing them right though.
In the formula it's written "842'59.8"N 16743'07.9"E"
It's displayed as N923'33.7NN
Is there a way to force it to show something exactly how it's written in the code?
The reason you're getting those results is because you have extraneous double quotes (representing seconds) in the coordinates. If you really need to represent seconds that way, use pairs of single quotes for them instead.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #20  
Old 11-29-2016, 03:41 PM
Father Father is offline Windows 7 32bit Office 2013
Novice
 
Join Date: Nov 2016
Posts: 29
Father is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
I suggest you take a look at my Microsoft Word Field Maths Tutorial, available at:
http://windowssecrets.com/forums/sho...Maths-Tutorial
I'm sure a lot of it is due to the fact that I have no idea what I'm actually doing, but...

When I am in ALT-F9 view and putting in the code/formula it doesn't always "activate". Is there something else I should be doing? When I go back to normal view the formula is sometimes written out as it is rather than being "invisible".

Forgive my terminology... I'm still learning to speak code.

I'm still struggling the get numbers from a dropdown condition to multiply with a FORMTEXT field number. I've tried various ways explained in the tutorial. Again, I'm sure I'm either doing it wrong or assuming one thing works with another when it doesn't.
Reply With Quote
  #21  
Old 11-29-2016, 04:14 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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

Have you checked for formfields' 'calculate on exit' property and applied the 'filling in forms' protection? Both are required to automate the calculations.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #22  
Old 11-29-2016, 06:31 PM
Father Father is offline Windows 7 32bit Office 2013
Novice
 
Join Date: Nov 2016
Posts: 29
Father is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
The 001 is simply the displayed output. You don't reference that in a calculation;
Is there a way to use the displayed output in a separate calculation? That's what I've been trying to do.

I tried using the table cell address that contains the output, but I'm guessing that's not an option either.
Reply With Quote
  #23  
Old 11-29-2016, 07:21 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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

Sure. If the displayed output is 001, 002, etc., and you want to multiply that by something, you could use a field code like:
{={REF Dropdown1}*5 \# 0}
Unless your dropdown only ever displays numbers, though, you might want something like:
{IF{REF Dropdown1}<> "Select an item" {={REF Dropdown1}*5 \# 0}}
where 'Select an item' is the default text' or, if some of the selections might not be numeric:
{IF{REF Dropdown1}={=0+{REF Dropdown1} \# 0} {={REF Dropdown1}*5 \# 0}}
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #24  
Old 11-29-2016, 08:34 PM
Father Father is offline Windows 7 32bit Office 2013
Novice
 
Join Date: Nov 2016
Posts: 29
Father is on a distinguished road
Default

So I abandoned any further attempt to use the displayed output in any calculations.

For Table 9a.

The displayed output shows a number in the same cell as the dropdown. I type that number into the ITEM N.E.W. field.
The TOTAL N.E.W. field calculates the total (=UXOQTY1 * UXONEW1). This seems to be working.

For Table 9b.
The displayed output shows a number in the same cell as the dropdown. I type that number into the ITEM N.E.W. field.
The TOTAL N.E.W. field calculates the total (=DEMOQTY1 * DEMONEW1). This seems to be working.

Each ITEM N.E.W. text field has a run on exit macro which is supposed to give the option to add another table row.

Both are standalone tables with no merged cells, so I used the =SUM(ABOVE) to add up the numbers in the cells above it. That seems to be working as long as I don't use merged cells. If I do, it throws everything off.

For the table below those two (TOTAL NET EXPLOSIVE WEIGHT CONSUMED) I used the bookmarks for the previous totals to get a "grand total". That isn't working. I would simply add all of the formulas from the individual cells, but that wouldn't work if the ADD ROW option worked unless I added more formulas than I expected to ever need.
Attached Files
File Type: docm 2016 EOD Form Form Fields.docm (35.2 KB, 2 views)
Reply With Quote
  #25  
Old 11-30-2016, 08:34 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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 Table 9a in the attached.
Attached Files
File Type: docm EXPLOSIVE OPERATIONS DEPARTMENT.docm (47.3 KB, 10 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #26  
Old 12-14-2016, 02:46 PM
Father Father is offline Windows 7 32bit Office 2013
Novice
 
Join Date: Nov 2016
Posts: 29
Father is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Try Table 9a in the attached.
Question: It looks like you added to the original macro I was using from this example. Why did you need to do that?

It works as far as giving the option to add a row... but the calculations no longer work. On Exit from the field that has the add row function the field in the next cell should calculate the product of QTY and N.E.W. What causes this not to work now?

When I copy the new macro code to my form with the calculation properties in the field it no longer gives the option to add a row.

Would doing this as a PDF form allow for more options? It's starting to seem like what I want is too much for Word to handle.
Reply With Quote
  #27  
Old 12-14-2016, 05:00 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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

Quote:
Originally Posted by Father View Post
Question: It looks like you added to the original macro I was using from this example. Why did you need to do that?
To make it work with your document...
Quote:
Originally Posted by Father View Post
It works as far as giving the option to add a row... but the calculations no longer work. On Exit from the field that has the add row function the field in the next cell should calculate the product of QTY and N.E.W. What causes this not to work now??
Somehow a typo crept into the field code for the calculation for the 'TOTAL N.E.W.' column. If you toggle the field code siplay 'on' you'll see {=UXOQTY03*EUXONEW03}; it should read {=UXOQTY03*UXONEW03}. Fix that and the calculations should work OK.
Quote:
Originally Posted by Father View Post
When I copy the new macro code to my form with the calculation properties in the field it no longer gives the option to add a row.
You would need to copy both the table and the code. The table in my attachment has a separate column for the lbs. so the optional additional material definition can more easily be handled. Compare tables 9a & 9b.
Quote:
Originally Posted by Father View Post
Would doing this as a PDF form allow for more options? It's starting to seem like what I want is too much for Word to handle.
I doubt it - especially when it comes to adding new material definitions & table rows. This is well within Word's capability.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #28  
Old 12-14-2016, 06:13 PM
Father Father is offline Windows 7 32bit Office 2013
Novice
 
Join Date: Nov 2016
Posts: 29
Father is on a distinguished road
Default

Awesome!!! Thank you.

A few more questions.... because I wouldn't be a pain in your butt without more questions

(1) Is there a way to reset or clear the fields without going into the forms tools?
(2) Is there a way to remove rows once they've been added without unlocking the form and going into the developer tools?
(3) Is there a way to make the drops downs hold more than 25 items?
Reply With Quote
  #29  
Old 12-14-2016, 06:37 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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

Re:
1. You could select a given formfield and delete the contents or reset the dropdown value to the default. Added dropdown items and the 'lbs' fields for them can't be removed this way, though. In any event, if you save the document as a template, only the default setup will appear in any new document.
2. Not without devising some means of adding some 'remove rows' code that you could trigger somehow.
3. No. That said, I've coded the macro attached to the Nomenclature dropdown so that two more items can be added. The alternative would be to re-write the document using content controls.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #30  
Old 12-14-2016, 09:07 PM
Father Father is offline Windows 7 32bit Office 2013
Novice
 
Join Date: Nov 2016
Posts: 29
Father is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Re:
1. You could select a given formfield and delete the contents or reset the dropdown value to the default. Added dropdown items and the 'lbs' fields for them can't be removed this way, though. In any event, if you save the document as a template, only the default setup will appear in any new document.
So Word doesn't have an option like Acrobat to put a "CLEAR FORM" button?
Not a huge... It's simple enough to do like you said.

Quote:
Originally Posted by macropod View Post
Re:
3. No. That said, I've coded the macro attached to the Nomenclature dropdown so that two more items can be added. The alternative would be to re-write the document using content controls.
I was looking in the wrong spot for that macro. Works good, except in add rows it puts the numbers in the first row's LBS EA column. I'll verify that I didn't screw something up in the cell numbering.

Thanks again!!
Reply With Quote
Reply

Tags
calculating values, expanding table, form fields

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to keep formatted form fields after mail merge or replace text with formatted form fields jer85 Word VBA 2 04-05-2015 10:00 PM
Tables with form fields step10 Word 18 11-12-2013 02:27 PM
Enable editing in expanding fields marweste Word 11 10-21-2013 07:41 AM
How to prevent Tables from expanding HappyFingers Word 1 11-29-2010 01:54 PM
Calculating Form Fields in Microsoft Word wubba80 Word 1 06-25-2010 12:42 AM


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


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