Combobox Value does not change

[jallin]

I have created a form template in Word. This form includes two combo
boxes (from the toolbox), one for a product code and the other for
quantity. I have a formfield that uses an entry macro that calculates
the amount based on the two combo box values. The form is password
protected. When the form is opened as a .dot the macro works fine.
However, if it is opened as a .doc and you change the value in the
product code combo box, when you tab to the formfield, the macro runs,
but it does not grab the combo box value. When stepping through the
code it shows the value as the default choice ("Select") and so the
formfield has nothing to calculate. I would like to know why it would
work one way but not the other and how I can fix it. Thank you in
advance for any help.

 

[Doug Robbins - Word MVP]

I would suggest that you include the code from your macro in a message that
you post back here. Then someone may be able to spot something that is
causing the behaviour.

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

 

[jallin]

Here is the code I am using.

Sub Calculation()

Dim oWord As Document
Set oWord = ActiveDocument
On Error Resume Next

oWord.Unprotect Password:="password"

With ActiveDocument.FormFields("Fee")
Select Case cboFees.Value
Case "555A"
.Result = (cboQty.Value * "140.00")
Case "555B"
.Result = (cboQty.Value * "100.00")
Case "555C"
.Result = (cboQty.Value * "36.00")
Case "555D"
.Result = (cboQty.Value * "90.00")
Case Else
End Select
End With

oWord.Protect Type:=wdAllowOnlyFormFields, NoReset:=True,
Password:="password"

End Sub

Thanks again for any help.

 

[Doug Robbins - Word MVP]

Having set oWord to the ActiveDocument, you should continue to refer to
objects on it by that reference.

As you are using one formfield, I would suggest using DropDown formfields
for the Fee schedule and the Quantity rather that to mix the type of
controls on the form. Assuming that you assign the same bookmark names to
the DropDown formfields as you have assigned to the comboboxes, then the
code that you should use would be:

Sub Calculation()

Dim oWord As Document
Dim Multiplier as Long
Set oWord = ActiveDocument
On Error Resume Next

With ActiveDocument
Select Case .FormFields("cboFees").Result
Case "555A"
Multiplier = 140
Case "555B"
Multipier = 100
Case "555C"
Multiplier = 36
Case "555D"
Multiplier = 90
Case Else
Multiplier = 0
End Select
.FormFields("Fees").Result = Multiplier *
Val(.FormFields("cboQty")).Result)
End With

End Sub

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

 

[jallin]

Thanks for your quick response. I had decided on the combo box because
there are actually 137 items in the drop down list. Is there a better
way for me to do this?

 

[Doug Robbins - Word MVP]

If the 137 items are in the cboQty, I would replace that with a Textbox
FormField and just have the user enter the quantity.

Alternatively, I would use a userform with comboboxes then you could load
the Fee classes and their associated multipliers into two columns of the
combobox and use the .BoundColumn property of the combobox to get the
multiplier for the selected Fee class and then you would not need the Select
Case construction.

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

 

[jallin]

Thank you so much for your help. I have decided to use the userform
approach and it seems to be working fine.