If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Formula to calculate sum based on drop down form fields in Word - macropod
Hi pedro,
You could use an 'on exit' macro like the following attached to a formfield: Sub UpdateDoc() Dim ToC As TableOfContents Dim pRange As Word.Range Dim Pwd As String Dim pState As Boolean With ActiveDocument pState = False If .ProtectionType wdNoProtection Then Pwd = InputBox("PleasePassword", "Password") pState = True .Unprotect Pwd End If ' Loop through Story Ranges and update. ' Note that this may trigger interactive fields (eg ASK and FILLIN). For Each pRange In .StoryRanges Do pRange.Fields.Update Set pRange = pRange.NextStoryRange Loop Until pRange Is Nothing Next ' Loop through Tables Of Contents and update For Each ToC In .TablesOfContents ToC.Update Next If pState = True Then .Protect wdAllowOnlyFormFields, Noreset:=True, Password:=Pwd pState = False Pwd = "" End With End Sub The code asks the use to input the password. If you don't want to do that, replace: Pwd = InputBox("PleasePassword", "Password") with: Pwd = "Password" where "Password" is the actual password. Note that the macro includes code to update the ToC also, since it's difficult to get ToCs to update correctly in a form. The same applies to Tables of Authorities and Tables of Figures, but you haven't mentioned the document having any of them. You can delete the code to update the ToC if none of your formfields can change the document's pagination. Cheers -- macropod [MVP - Microsoft Word] ------------------------- wrote in message ... On Apr 17, 11:18 pm, "macropod" wrote: Hi pedro, Formula Correction: {={DropDown1}+{DropDown2}+{DropDown3}+{DropDown4}+ {DropDown5}+{DropDown6}+{*DropDown7}} Cheers -- macropod [MVP - Microsoft Word] ------------------------- "macropod" wrote in . .. Hi pedro, You don't need a macro to do the calculation. All you need is a formula field (not a formfield), coded as: {=DropDown1+DropDown2+DropDown3+DropDown4+DropDown 5+DropDown6+DropDown7} where the field braces (ie '{ }') are created via Ctrl-F9. As for the TOC, the best way to update it in a form is to use a macro to temporarily unprotect the form, update the TOC, then reprotect the form (with noreset = true). Cheers -- macropod [MVP - Microsoft Word] ------------------------- "pedro gracio" wrote in ... Hi, This seems to work pretty well in word 2000 until it is used in a large document that has a table of contents. It seems to need some sort of refresh. I also tried this macro and although it also seems to work, it results in making the pages scroll up & down. This of course would be quite painful for the user. Here's that code : Sub AddDropDownResults() Dim dDown1, dDown2, dDown3, dDown4, dDown5, dDown6, dDown7, As Integer ' Get value of first drop down form field. dDown1 = Val(ActiveDocument.FormFields("DropDown1").Result) ' Get value of second drop down form field. dDown2 = Val(ActiveDocument.FormFields("DropDown2").Result) ' Get value of thrid drop down form field. dDown3 = Val(ActiveDocument.FormFields("DropDown3").Result) ' Get value of fouth drop down form field. dDown4 = Val(ActiveDocument.FormFields("DropDown4").Result) ' Get value of fifth drop down form field. dDown5 = Val(ActiveDocument.FormFields("DropDown5").Result) ' Get value of sixth drop down form field. dDown6 = Val(ActiveDocument.FormFields("DropDown6").Result) ' Get value of seveth drop down form field. dDown7 = Val(ActiveDocument.FormFields("DropDown7").Result) ' Calculate results and place in Text1 form field ActiveDocument.FormFields("Text1").Result = Str(dDown1 + dDown2 + dDown3 + dDown4 + dDown5 + dDown6 + dDown7) End Sub Any help would be greatly appreciated Thanks Pedy- Hide quoted text - - Show quoted text - Hi macropod, Sorry for re-posting, I did not see your reply... How would I create a "macro to temporarily unprotect the form" ? The other thing that I tried and it worked great until I placed the table in a document with a TOC is just using a regular Text Form Field type Number with the calculate on exit, and in the last cell I used the formula "=SUM(d2:d21)". This again woked just fine, it calculated automatically & all but once there is a TOC it didn't do anything... pedy |
#12
|
|||
|
|||
how to rename macro in letter I just found icone Pleasehelp
how to rename macro in letter I just found icone Please help
|
|
Thread Tools | |
Display Modes | |
|
|