Microsoft Office Forums - View Single Post - [Solved] Calculating tax rate per province in VB

geepxz · 03-19-2020, 01:41 PM

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...

We do not charge tax for the US.

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?