vba - How can I calculate the sum of Legacy Form Fields without werid scrolling? -

vba - How can I calculate the sum of Legacy Form Fields without werid scrolling? -

i have word document table contains cost of item, order amount , item sum, amount , item sum legacy form fields:

there other items available options can chose via checkbox (also legacy form fields):

code

now i've created function calculates item sum

sub order_amount_click() dim amount integer dim valuefromcell string dim list dim value double amount = activedocument.formfields("order_amount").result if amount > 0 valuefromcell = thisdocument.tables(1).cell(5, 3).range.text list = split(valuefromcell, " ") value = list(0) subsum = value * amount activedocument.formfields("order_amount_sum").result = subsum else activedocument.formfields("order_amount_sum").result = 0 end if calculatesum end sub

and function calculates item sum additional items (if checkbox activated) multiplied order amount:

sub optional_item_click() dim checkbox integer dim valuefromcell string dim list dim value double dim amount integer dim subsum double checkbox = activedocument.formfields("optional_item1").result amount = activedocument.formfields("order_amount").result if checkbox = 1 , amount > 0 valuefromcell = thisdocument.tables(1).cell(7, 3).range.text list = split(valuefromcell, " ") value = list(0) subsum = value * amount activedocument.formfields("optional_item1_sum").result = subsum else activedocument.formfields("optional_item1_sum").result = 0 end if calculatesum end sub

(there many click() functions there optional_item fields, 17 in sum - haven't generalized function yet.)

the lastly line within both subs function call, calculates net sum, vat , final total sum

function calculatesum() dim net double dim vat double dim total_sum double net = cdbl(activedocument.formfields("order_amount_sum").result) + cdbl(activedocument.formfields("optional_item1_sum").result) + cdbl(activedocument.formfields("optional_item2_sum").result) + cdbl(activedocument.formfields("optional_item3_sum").result) + cdbl(activedocument.formfields("optional_item4_sum").result) + cdbl(activedocument.formfields("optional_item5_sum").result) + cdbl(activedocument.formfields("optional_item6_sum").result) + cdbl(activedocument.formfields("optional_item7_sum").result) + cdbl(activedocument.formfields("optional_item8_sum").result) + cdbl(activedocument.formfields("optional_item9_sum").result) ' cannot compile when line long, splitting 2 statements net = net + cdbl(activedocument.formfields("optional_item10_sum").result) + cdbl(activedocument.formfields("optional_item11_sum").result) + cdbl(activedocument.formfields("optional_item12_sum").result) + cdbl(activedocument.formfields("optional_item13_sum").result) + cdbl(activedocument.formfields("optional_item14_sum").result) + cdbl(activedocument.formfields("optional_item15_sum").result) + cdbl(activedocument.formfields("optional_item16_sum").result) + cdbl(activedocument.formfields("optional_item17_sum").result) 'msgbox "net " & net activedocument.formfields("net_sum").result = net vat = net * 0.19 'msgbox "vat " & vat activedocument.formfields("vat_sum").result = vat total_sum = net + vat 'msgbox "total " & total_sum activedocument.formfields("total_sum").result = total_sum end function problem

the code works fine, elements calculated correctly. there 2 major problems create whole document unusable (all actions users restricted come in name on top of document, chose amount , de-/activate checkboxes; sum fields aren't accessible):

when perform action (e.g. come in amount) calculatesum() function visibly loops on sum fields (spanned on 3 pages) , weirdly scrolls on document along sum fields. when click on checkbox on 2nd or 3rd page, document scrolls first page place can come in amount of pieces want order.

now how can supress looping on sum fields , weirdly scrolling around? , how can prevent document scrolling 1st page?

any help appreciated! (i'm thankful side comments on code, i'm new vba.)

update #1

i've added screencast showing error.

update #2

the basic problem seems macro scrolling position referring in script, e.g. if i'm using

activedocument.formfields("total_sum").result = total_sum

it's scrolling total_sum field.

too hard comment...

for (b) have utilize vba - can't utilize trick suggested elsewhere formfields checkboxes, content command ones.

for (c), (not tested)

make sure on exit macro set optional_item1 , calculate on exit set. don't think need on entry macro well.

sub optional_item1_click() phone call cb_click("optional_item1") end sub sub cb_click(ffname string) activedocument.variables(ffname) = abs(int(activedocument.formfields(ffname).checkbox.value)) end sub

in summa column, next checkbox, like

{ ={ docvaraiable optional_item1 }*x2 }

where x2 cell contains 3,00 euro value , { } special field code brace pairs can insert using ctrl-f9 on windows word.

but

whether word correctly interpret euro value correctly depends on regional settings of user using form, (decimal separator, grouping (thousands) separator, currency symbol , location of currency symbol may alter word's behaviour. within eurozone think of things can vary. if user not need modify multiplier 3,00 euros in cell, may improve plug value straight { = } field:

{ ={ docvaraiable optional_item1 }*3 }

you can utilize \# numeric formats give format want when positive or 0. again, word's numeric formatting facility not locale-independent).

those locale dependencies reason doing more in vba rather field codes.

vba ms-word scroll