Negative value when deduction is being selected

[ah]

Hi;

I would appreciate if someone could help me with the following:

I've created a form with a field for the user to key in Amount and 1 drop
down list for the user to specify the Action needed.

I want the system to automatically capture the amount as a negative value
whenever I select an action called "deduction" from the Action drop down list.

And, value to remain as positive when I select the action called "Payment"
Please advice.

Thanks in advance.

 

[Doug Robbins - Word MVP]

Assuming that you are using FormFields in a protected document and that the
formfield into which the amount is entered has the bookmark name of "Amount"
and that the DropDown FormField has the bookmark name of "Action" and that
the items in that FormField are in the order

Deduction
Payment

run a macro containing the following code on exit from the DropDown
FormField

With ActiveDocument
If .FormFields("Action").DropDown.Value = 1 Then
If .FormFields("Amount").Result > 0 Then
.FormFields("Amount").Result = 0 - .FormFields("Amount").Result
End If
Else
If .FormFields("Amount").Result < 0 Then
.FormFields("Amount").Result = 0 - .FormFields("Amount").Result
End If
End If
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Greg Maxey]

Assuming the textfield for entering the value is named "Value" and the
dropdown is named ActionDD then something like this should get your started:

Sub ScratchMacro()
Dim oFlds As FormFields
Set oFlds = ActiveDocument.FormFields
Select Case oFlds("ActionDD").DropDown.Value
Case 1
MsgBox "100" + CDbl(oFlds("Value").Result)
Case 2
MsgBox "100" - CDbl(oFlds("Value").Result)
End Select
End Sub

 

[ah]

Hi;

Thanks for your reply.

I tried, but I get the following error message when I select the action
before I key in the Amount:

Run time error 13. Type mismatch.

And the following code is being highlighted:
If .FormFields("Amount").Result < 0 Then

I'm not be able to block the user from selecting the action 1st before they
key in the amount. Please advice what should I do to resolve this error.

Thanks in advance

 

[Doug Robbins - Word MVP]

Use:

With ActiveDocument
If IsNumeric(.FormFields("Amount").Result) Then
If .FormFields("Action").DropDown.Value = 1 Then
If .Result > 0 Then
.Result = 0 - .FormFields("Amount").Result
End If
Else
If .FormFields("Amount").Result < 0 Then
.FormFields("Amount").Result = 0 -
..FormFields("Amount").Result
End If
End If
End If
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[ah]

Hi;

Thanks for your prompt response.

However, I got the following error when I select the action called " Run
time error 438. object doesn't support this property or method"

And the following code is being highlighted:
If .Result > 0 Then

Appreciate your help.

Thanks

 

[Doug Robbins - Word MVP]

The code should have been:

With ActiveDocument
If IsNumeric(.FormFields("Amount").Result) Then
If .FormFields("Action").DropDown.Value = 1 Then
If .FormFields("Amount").Result > 0 Then
.FormFields("Amount").Result = 0 -
..FormFields("Amount").Result
End If
Else
If .FormFields("Amount").Result < 0 Then
.FormFields("Amount").Result = 0 -
..FormFields("Amount").Result
End If
End If
End If
End With

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[ah]

Thanks for your reply. You're so helpful!!!!!!

I tried, and it works. However, it seems like the negative will only be
captured after I exit the field.

Is it possible for the form to capture the negative sign even if I did not
exit the field but save the form directly after I key in the amount / select
the action?

Appreciate your valuable advice.

 

[Doug Robbins - Word MVP]

You could use the technique in the article "A Pseudo DocumentBeforeClose
Event" at:

http://www.word.mvps.org/FAQs/MacrosVBA/PseudoBeforeClose.htm

to run the code and then save the document.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP