Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Formula (Word XP)

    I'm using field codes in a Word form, and I want the result of a particular field to be 0 if a check box is not check, and 100 if it is checked.

    My formula: =IF(ckBox = true, 100.00, 0.00) doesn't work. I don't get a syntax error, but the result is always 0.00. In other words, the formula never see ckBox = true. Am I representing this correctly? (I imagine I am not.) How do I represent in a formula the state of a check box?

    Thanks,
    Richard Barrett

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula (Word XP)

    Hi Richard,

    Your field needs a syntax like:
    {=if(ckBox=true,100,0) # 0.00}
    with no spaces after the commas. This works when testing the state of a bookmark, but I'm not sure about how one tests the state of a check box.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: Formula (Word XP)

    If I use the formula you suggested, a syntax error results: Unexpected end of formula.

    Have I dont something wrong?

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula (Word XP)

    At a guess, I'd say you've left out the closing bracket at the end of the if test, or you've put it in the wrong place. Try copying the field's arguments (ie everything between the braces) from my post and pasting it directly into your field.
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Formula (Word XP)

    I tried this and I think the syntax error is caused by ckBox not returning any value. So the IF field is invalid.

    If ckBox was an ordinary bookmark then you could use something like

    {=if({ REF ckBox }=true,100,0) # 0.00}
    but this doesn't work either, because { REF ckBox } just returns a null string.

    I could not see any obvious way of using the value of a checkbox in an IF field. Maybe a better solution would be to have an OnExit macro for the check box calculate a value and put it in the correct place.

    StuartR

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula (Word XP)

    Thanks Stuart,
    You don't actually need the REF argument to test a bookmark, which is why I didn't use it.

    Given that there seems to be no way for the field to test the checkbox directly, maybe the answer is to have the macro that the check box is attached to update a bookmark that the field can then test. Your thoughts?

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: Formula (Word XP)

    If I paste your code, it always proves false... I've tried using Insert>Field, and choosing Formula, and I've tried using the Forms toolbar to insert a field, and through the properties dialog setting the type to Calculation and adding the formula there. I either get a syntax error, or the value always proves false.

    Stuart seems to be correct when he says there's no apparent way to evaluate the state of a check box.

  8. #8
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Formula (Word XP)

    > Your thoughts?
    That's the way I would do it too. Using the OnExit macro from the checkbox.

    StuartR

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula (Word XP)

    Oh well, at least you don't get sytnax errors any more ...

    Check out the exchange Stuart and I have just been having on ways to get around the inability for a field to test a checkbox directly.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: Formula (Word XP)

    So what would I check for in the exit macro? I'd still need to evaluate the state of the check box, wouldn't I?

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula (Word XP)

    <hr>I'd still need to evaluate the state of the check box, wouldn't I?<hr>
    Exactly. And then you'd use the results of that test to update a bookmark in your document. You could put the 100.00 or 0.00 in explicity, or you could put a 1 or 0 into a bookmark that is then tested by the field. The latter approach might be particularly useful if there are numerous fields that need to test the state of the one checkbox combined with other variables.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: Formula (Word XP)

    I guess I'm not following. If I use a checkbox, and have a macro that runs on exit from that checkbox, how will I check the state of the checkbox? If the user tabs to every field, he may exit without changing the original state of the check box. How will I know?

    It seems that I'm back to where we started.... I need to evaluate the state of the checkbox, but there's no way to do it.

    Or are you suggesting that I not use a check box... but what then?

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula (Word XP)

    Hi Richard,

    OK let's make sure we're both on the same wavelength. Starting with the Checkbox Property Example in Word 2K's help file, we have:

    <pre>Sub CheckboxPropertyExample()
    With Assistant.NewBalloon
    .Heading = "Regional Sales Data"
    .Text = "Select the region(s) you want to print."
    For i = 1 To 3
    .Checkboxes(i).Text = "Region " & i
    Next
    .Button = msoButtonSetOkCancel
    If .Show = msoBalloonButtonOK Then
    dataPrinted = 0
    For i = 1 To 3
    If .Checkboxes(i).Checked = True Then
    dataPrinted = dataPrinted + 1
    MsgBox "Region " & i & " data printed."
    End If
    Next
    End If
    If dataPrinted = 0 Then MsgBox "No data printed."
    End With
    End Sub
    </pre>

    NOTE: I've moved the "If dataPrinted = 0" argument, so that it traps the Cancel key too.

    I assume (I hope correctly!) that your code looks at least vaguely something like this. What you need to have instead of the MsgBox responses is code to update the relevant bookmark(s). There are various posts here at Woody's showing how to do this without destroying the bookmark in the process. Once you've updated all the bookmarks, your code could end with:
    <pre> ActiveDocument.Fields.Update</pre>

    so that any fields that test the updated bookmarks are also updated.

    Hope this helps.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #14
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: Formula (Word XP)

    I see.... I've been using the checkbox from the Forms toolbar, and you're using the checkbox from the Control Toolbox toolbar. From that checkbox, it's possible to get to VBA and write code, etc. Easy enough.

    Thanks very much.

  15. #15
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Formula (Word XP)

    Here is an example of how you can do this with the check box from the forms toolbar and an attached macro.

    This document is protected with a blank password.

    StuartR
    Attached Files Attached Files

  16. The Following User Says Thank You to StuartR For This Useful Post:

    metalfury (2012-06-26)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •