Calculating tax rate per province in VB

macropod · #2 03-19-2020, 02:13 PM

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

Note: You shouldn't use content controls and formfields in the same document, as they weren't designed to be used that way and trying to do so can cause problems.

Similar Threads
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
Average Billing Rate	tbrozovich	Project	2	08-18-2015 04:00 PM
Total Hours * hourly rate	medavidcook	Excel	7	06-04-2015 09:29 AM
Cost Rate Table Macro Help	JohnTurner	Project	1	01-27-2014 07:53 AM
Help: Very low response-rate!	Jeff10	Office	6	12-17-2013 10:44 AM