![]() |
#1
|
|||
|
|||
![]()
SHORT REQUEST: im looking for the VBA syntax for an IF function that references a bookmark drop down value in a word document.
IN DEPTH:I have a template I am currently working on and I would like it to automatically calculate the tax rate. First I have drop downs. We first select CA or US, depending on country is selected the Province/State dropdown changes. This is adone in VB. Code:
Sub PopulatedRegion() Select Case ActiveDocument.FormFields("ddCountry").Result Case "CA" With ActiveDocument.FormFields("ddRegion").DropDown.ListEntries .Clear .Add "AB" .Add "BC" .Add "MB" .Add "NB" .Add "NL" .Add "NT" .Add "NS" .Add "NU" .Add "ON" .Add "PE" .Add "QC" .Add "SK" .Add "YT" End With Case "USA" With ActiveDocument.FormFields("ddRegion").DropDown.ListEntries .Clear .Add "AL" .Add "AK" .Add "AZ" .Add "AR" .Add "CA" .Add "CO" etc... Now for Canada, later in the document I have a bookmark [TOTAL_FEES] and the advance payment [TOTAL_FEES/2] which is saved under [HALF_FEES]. I then add the admin fees which are taxable so it gives me [HALF_FEES*1.05] aka [HALF_FEES2]. So far all works great but now the hard part. I would like to find a way in VB (If possible id rather not put a table in there) to create an IF formula that would work with the selected province in the drop down. For exemple if Ontario [ON] (13% tax) is selected my formula would become [HALF_FEES2*1.13]. Does this make any sense? Someone here able to help? |
#2
|
||||
|
||||
![]()
I'm not sure that formfield dropdowns will work in this case, as they only accept a maximum of 25 entries and there are more than twice that US States.
As for the tax calculation, with formfields no VBA code is required - it can all be done with field coding. Such a field might be coded along the lines of: {={IF{REF ddCountr}= "CA" {IF{REF ddRegion}= "ON" 1.13 1.05} 0}*{Half_Fees} \# $,0.00} Regardless, given the limitation of 25 for formfield dropdown entries, you should instead consider the use of content control dropdowns, for which all your US States can be added. In that case, the VBA code might look like: Code:
Dim Admin As Single, Tax As Single With ActiveDocument If .SelectContentControlsByTitle("Country")(1) = "US" Then Tax = 0 Else If .SelectContentControlsByTitle("Region")(1) = "ON" Then Tax = 1.13 Else Tax = 1.05 End If End If Admin = .SelectContentControlsByTitle("Admin")(1).Range.Text With .SelectContentControlsByTitle("Tax")(1) .LockContents = False .Range.Text = Format(Tax * Admin, "#,##0.00") .LockContents = True End With End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
![]()
Youre absolutely right. We divided the USA in 2 for 50 states using USA and U.S.A.
Thank you! ill try this right away and get back to you with feedback! ![]() Quote:
|
![]() |
Tags |
drop down, formula, formulas drop downs |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculating the duration of a task based on work rate | Rida | Project | 0 | 04-26-2018 03:55 AM |
![]() |
tbrozovich | Project | 2 | 08-18-2015 04:00 PM |
![]() |
medavidcook | Excel | 7 | 06-04-2015 09:29 AM |
![]() |
JohnTurner | Project | 1 | 01-27-2014 07:53 AM |
Help: Very low response-rate! | Jeff10 | Office | 6 | 12-17-2013 10:44 AM |