UserForm to Calculate Cost and Redefine Variable Values

[docteam135]

Hey guys,

So I've been working on this particular code for a couple days now, and I keep getting an Overflow error. Basically, this is a user form where the user inputs the number of days they will be requiring the services of specific employees, and when the click okay, the code calculates the total cost, and changes the value of certain doc variables. These variables then show up in various places in the document, so the code also updates all fields.

It's a little complicated, but I've tried this same code on a smaller scale and it worked fine. I've been over it a hundred times, so I know I must be missing something. I did some Googling about the Overflow error, and tried switching some of my variables to Long but it made no difference. I'm still getting stuck in this one place. The line in bright red below is where I'm getting the error. I'm pretty new to macros, so I'm not sure what else to try, or even where to look.

I'm attaching the Word doc with the macros, I only copied the sub that had the line. Thanks.




Private Sub cmdOK_Click()
CostCalcForm1.Hide
'Change variables PM
With ActiveDocument
strPMOnsiteOld = ActiveDocument.Variables("vPMOnsiteDays")
strPMOnsiteNew = CostCalcForm1.txtPMOnsiteDays
If strPMOnsiteOld <> strPMOnsiteNew Then _
.Variables("vPMOnsiteDays").Value = strPMOnsiteNew

strPMRemoteOld = ActiveDocument.Variables("vPMRemoteDays")
strPMRemoteOld = CostCalcForm1.txtPMRemoteDays
If strPMRemoteOld <> strPMRemoteNew Then _
.Variables("vPMRemoteDays").Value = strPMRemoteNew
'Calculate
vPMOnsite = vPMOnsiteDays * 1250
vPMRemote = vPMRemoteDays * 1000
End With


'Change variables BA
With ActiveDocument
strBAOnsiteOld = ActiveDocument.Variables("vBAOnsiteDays")
strBAOnsiteNew = CostCalcForm1.txtBAOnsiteDays
If strBAOnsiteOld <> strBAOnsiteNew Then _
.Variables("vBAOnsiteDays").Value = strBAOnsiteNew

strBARemoteOld = ActiveDocument.Variables("vBARemoteDays")
strBARemoteOld = CostCalcForm1.txtBARemoteDays
If strBARemoteOld <> strBARemoteNew Then _
.Variables("vBARemoteDays").Value = strBARemoteNew

'Calculate
vBAOnsite = vBAOnsiteDays * 1500
vBARemote = vBARemoteDays * 1000
End With
'Change variables PQRS
With ActiveDocumnet
strPQRSrOld = ActiveDocument.Variables("vPQRSrProviders")
strPQRSrNew = CostCalcForm1.txtPQRSr
If strPQRSrOld <> strPQRSrNew Then _
.Variables("vPQRSrProviders").Value = strPQRSrNew

strPQRSrRangeOld = ActiveDocument.Variables("vPQRSrRange")
strPQRSrRangeNew = CostCalcForm1.cmbPQRS
If strPQRSrRangeOld <> strPQRSrRangeNew Then _
.Variables("vPQRSrRange").Value = strPQRSrRangeNew

strPQRScOld = ActiveDocument.Variables("vPQRScRemoteDays")
strPQRScNew = CostCalcForm1.txtPQRSc
If strPQRScOld <> strPQRScNew Then _
.Variables("vPQRScRemoteDays").Value = strPQRScNew
'Calculate
vPQRSr = (vPQRSrProviders * 250) + vPQRSFee
vPQRSc = vPQRScRemoteDays * 750

End With
'Change variables MU
With ActiveDocument
strMUOnsiteOld = ActiveDocument.Variables("vMUOnsiteDays")
strMUOnsiteNew = CostCalcForm1.txtMUOnsite
If strMUOnsiteOld <> strMUOnsiteNew Then _
.Variables("vMUOnsiteDays").Value = strMUOnsiteNew

strMURemoteOld = ActiveDocument.Variables("vMURemoteDays")
strMURemoteNew = CostCalcForm1.txtMURemote
If strMURemoteOld <> strMURemoteNew Then _
.Variables("vMURemoteDays").Value = strMURemoteNew
'Calculate
vMUOnsite = vMUOnsiteDays * 1250
vMURemote = vMURemoteDays * 1000
End With
'Change variables HBI
With ActiveDocument
strHBIOnsiteOld = ActiveDocument.Variables("vHBIOnsiteDays")
strHBIOnsiteNew = CostCalcForm1.txtHBIOnsiteDays
If strHBIOnsiteOld <> strHBIOnsiteNew Then _
.Variables("vHBIOnsiteDays").Value = strHBIOnsiteNew

strHBIRemoteOld = ActiveDocument.Variables("vHBIRemoteDays")
strHBIRemoteNew = CostCalcForm1.txtHBIRemoteDays
If strHBIRemoteOld <> strHBIRemoteNew Then _
.Variables("vHBIRemoteDays").Value = strHBIRemoteNew
'Calculate
vHBIOnsite = vHBIOnsiteDays * 1500
vHBIRemote = vHBIRemoteDays * 1000
End With
'Change variables EIS
With ActiveDocument
strEISOld = ActiveDocument.Variables("vEISOnsiteMo")
strEISNew = CostCalcForm1.cmbEISOnsiteMo
If strEISOld <> strEISNew Then _
.Variables("vEISOnsiteMo").Value = strEISNew
End With
'Change variables IIS
With ActiveDocument
strIISOnsiteOld = ActiveDocument.Variables("vIISOnsiteDays")
strIISOnsiteNew = CostCalcForm1.txtIISOnsiteDays
If strIISOnsiteOld <> strIISOnsiteNew Then _
.Variables("vIISOnsiteDays").Value = strIISOnsiteNew

strIISRemoteOld = ActiveDocument.Variables("vIISRemoteDays")
strIISOnsiteNew = CostCalcForm1.txtIISRemoteDays
If strIISRemoteOld <> strIISRemoteNew Then _
.Variables("vIISRemoteDays").Value = strIISRemoteNew
'Calculate
vIISOnsite = vIISOnsiteDays * 1500
vIISRemote = vIISRemoteDays * 1000
End With
'Change variables Training
With ActiveDocument
strTrainingOnsiteOld = ActiveDocument.Variables("vTrainingOnsiteDays")
strTrainingOnsiteNew = CostCalcForm1.txtTrainingOnsiteDays
If strTrainingOnsiteOld <> strTrainingOnsiteNew Then _
.Variables("vTrainingOnsiteDays").Value = strTrainingOnsiteNew

strTrainingRemoteOld = ActiveDocument.Variables("vTrainingRemoteDays")
strTrainingRemoteNew = CostCalcForm1.txtTrainingRemoteDays
If strTrainingRemoteOld <> strTrainingRemoteNew Then _
.Variables("vTrainingRemoteDays").Value = strTrainingRemoteNew
'Calculate
vTrainingOnsite = vTrainingOnsiteDays * 1000
vTrainingRemote = vTrainingRemoteDays * 750
End With
'Change variables ESS
With ActiveDocument
strESSOnsiteOld = ActiveDocument.Variables("vESSOnsiteDays")
strESSOnsiteNew = CostCalcForm1.txtESSOnsiteDays
If strESSOnsiteOld <> strESSOnsiteNew Then _
.Variables("vESSOnsiteDays").Value = strESSOnsiteNew
'Calculate
vESSOnsite = vESSOnsiteDays * 1500
End With
'Change variables ICD
With ActiveDocument
strICDOld = ActiveDocument.Variables("vICDRemoteDays")
strICDNew = CostCalcForm1.txtICD
If strICDOld <> strICDNew Then _
.Variables("vICDRemoteDays").Value = strICDNew
'Calculate
vICDRemote = vICDRemoteDays * 750
End With
'Change variables RCC
With ActiveDocument
strRCCOnsiteOld = ActiveDocument.Variables("vRCCOnsiteDays")
strRCCOnsiteNew = CostCalcForm1.txtRCCOnsite
If strRCCOnsiteOld <> strRCCOnsiteNew Then _
.Variables("vRCCOnsiteDays").Value = strRCCOnsiteNew

strRCCRemoteOld = ActiveDocument.Variables("vRCCRemoteDays")
strRCCRemoteNew = CostCalcForm1.txtRCCRemote
If strRCCRemoteOld <> strRCCRemoteNew Then _
.Variables("vRCCRemoteDays").Value = strRCCRemoteNew
'Calculate
vRCCOnsite = vRCCOnsiteDays * 1500
vRCCRemote = vRCCRemoteDays * 1000
End With

'Calculate total cost and cost per provider
With ActiveDocument
.Variables("vTotalCost").Value = FormatCurrency(vPMOnsite + vPMRemote + vBAOnsite + vBARemote + vPQRSr + vPQRSc _
+ vRCCOnsite + vRCCRemote + vHBIOnsite + MURemote + MUOnsite + vICDRemote _
+ vHBIRemote + vEISOnsite + vIISOnsite + vIISRemote + vTrainingOnsite + vTrainingRemote _
+ vESSOnsite)
End With
With ActiveDocument
.Variables("vCostPerProvider").Value = FormatCurrency(vTotalCost / vNumberOfProviders)
End With
'Calculate cost with discount
With ActiveDocument
strOldPercent = ActiveDocument.Variables("vDiscount")
strNewPercent = CostCalcForm1.txtDiscount
If strOldPercent <> strNewPercent Then _
.Variables("vDiscount").Value = strNewPercent

strOldDiscount = ActiveDocument.Variables("vCostAfterDiscount")
strNewDiscount = FormatCurrency(vTotalCost * (100 - vDiscount))
If strOldDiscount <> strNewDiscount Then _
.Variables("vCostAfterDiscount").Value = strNewDiscount
End With
'Calculate cost monthly installments
With ActiveDocument
strOldMonthly = ActiveDocument.Variables("vCostMonthlyInstall")
strNewMonthly = FormatCurrency(vTotalCost / 12)
If strOldMonthly <> strNewMonthly Then _
.Variables("vCostMonthlyInstall").Value = strNewMonthly
End With


'Update fields
ActiveDocument.Fields.Update


'
End Sub

[gmaxey]

I don't see any red highlighted line of code and your attachment doesn't contain any code.

[docteam135]

Hmm.. it looks like in the Code window color doesn't show up as actually color, it just has the tags for the color. I copied just that one line here. It's in the part where the cost per provider is calculated.

And let me try reattaching... Atm, the doc has coding in it, but I'll just double-check it after I post it to see...

With ActiveDocument
.Variables("vCostPerProvider").Value = FormatCurrency(vTotalCost / vNumberOfProviders)
End With

[docteam135]

Okay yeah that attachment works for me, so let me know if that's any better.

[gmaxey]

Could be a problem on my end as your attachment is coming in the form of "attachment.php" not a Word macro enabled file.

Hard to see why that line would cause an overflow. As you can see here, I've set an unrealistic high value to the variable and it runs with no problem:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim vTotalCost
Dim vNumberOfProviders As Long
vTotalCost = 2.34567123456735E+40
vNumberOfProviders = 1
With ActiveDocument
.Variables("vCostPerProvider").Value = FormatCurrency(vTotalCost / vNumberOfProviders)
Debug.Print .Variables("vCostPerProvider").Value
End With
End Sub

[docteam135]

Hmm.. okay I didn't want to post the whole thing... but here it is.



Option Base 1


'Declarations PM Onsite
Dim vPMOnsiteDays As Integer
Dim strPMOnsiteOld As String
Dim strPMOnsiteNew As String
Dim vPMOnsite As Currency
'Declarations PM Remote
Dim vPMRemoteDays As Integer
Dim strPMRemoteOld As String
Dim strPMRemoteNew As String
Dim vPMRemote As Currency
'Declarations BA Onsite
Dim vBAOnsiteDays As Integer
Dim strBAOnsiteOld As String
Dim strBAOnsiteNew As String
Dim vBAOnsite As Currency
'Declaractions BA Remote
Dim vBARemoteDays As Integer
Dim strBARemoteOld As String
Dim strBARemoteNew As String
Dim vBARemote As Currency
'Declarations PQRS Reporting
Dim vPQRSrRange As String
Dim vPQRSrRangeOld As String
Dim vPQRSrRangeNew As String
Dim vPQRSrProviders As Integer
Dim strPQRSrOld As String
Dim strPQRSrNew As String
Dim vPQRSFee As Currency
Dim strPQRSFeeOld As String
Dim strPQRSFeeNew As String
Dim vPQRSr As Currency
'Declarations for PQRS Consult
Dim vPQRScRemoteDays As Integer
Dim strPQRScOld As String
Dim strPQRScNew As String
Dim vPQRScUnitRemote As Currency
Dim vPQRScRemote As Currency
'Declarations for MU Onsite
Dim vMUOnsiteDays As Integer
Dim strMUOnsiteOld As String
Dim strMUOnsiteNew As String
Dim vMUOnsite As Currency
'Declarations MU Remote
Dim vMURemoteDays As Integer
Dim strMURemoteOld As String
Dim strMURemoteNew As String
Dim vMURemoteUnit As Currency
Dim vMURemote As Currency
'Declarations HBI Onsite
Dim vHBIOnsiteDays As Integer
Dim strHBIOnsiteOld As String
Dim strHBIOnsiteNew As String
Dim vHBIOnsite As Currency
'Declaractions HBI Remote
Dim vHBIRemoteDays As Integer
Dim strHBIRemoteOld As String
Dim strHBIRemoteNew As String
Dim vHBIRemote As Currency
'Declarations EIS
Dim vEISOnsiteMo As String
Dim strEISOld As String
Dim strEISNew As String
Dim vEISOnsite As Currency
'Declarations IIS Onsite
Dim vIISOnsiteDays As Integer
Dim strIISOnsiteOld As String
Dim strIISOnsiteNew As String
Dim vIISOnsite As Currency
'Declaractions IIS Remote
Dim vIISRemoteDays As Integer
Dim strIISRemoteOld As String
Dim strIISRemoteNew As String
Dim vIISRemote As Currency
'Declarations Training Onsite
Dim vTrainingOnsiteDays As Integer
Dim strTrainingOnsiteOld As String
Dim strTrainingOnsiteNew As String
Dim vTrainingOnsite As Currency
'Declaractions Training Remote
Dim vTrainingRemoteDays As Integer
Dim strTrainingRemoteOld As String
Dim strTrainingRemoteNew As String
Dim vTrainingRemote As Currency
'Declarations ESS Onsite
Dim vESSOnsiteDays As Integer
Dim strESSOnsiteOld As String
Dim strESSOnsiteNew As String
Dim vESSOnsite As Currency
'Declarations ICD-10
Dim vICDRemoteDays As Integer
Dim strICDOld As String
Dim strICDNew As String
Dim vICDRemote As Currency
'Declarations RCC Onsite
Dim vRCCOnsiteDays As Integer
Dim strRCCOnsiteOld As String
Dim strRCCOnsiteNew As String
Dim vRCCOnsite As Currency
'Declarations RCC Remote
Dim vRCCRemoteDays As Integer
Dim strRCCRemoteOld As String
Dim strRCCRemoteNew As String
Dim vRCCRemote As Currency
'Total Cost
Dim vTotalCost As Long
Dim strTotalOld As String
Dim strTotalNew As String
Dim vCostPerProvider As Long
Dim strCostPerOld As String
Dim strCostPerNew As String
Dim vNumberOfProviders As Integer
Dim strNumberOld As String
Dim strNumberNew As String
'Cost after discount is applied (%5)
Dim vDiscount As Integer
Dim strOldPercent As String
Dim strNewPercent As String
Dim vCostAfterDiscount As Long
Dim strOldDiscount As String
Dim strNewDiscount As String
'Cost in monthly installments
Dim vCostMonthlyInstall As Long
Dim strOldMonthly As String
Dim strNewMonthly As String


Private Sub UserForm_Initialize()

'Add items
cmbEISOnsiteMo.AddItem ("0 months")
cmbEISOnsiteMo.AddItem ("1 month")
cmbEISOnsiteMo.AddItem ("2 months")
cmbEISOnsiteMo.AddItem ("3 months")
cmbEISOnsiteMo.AddItem ("4 months")
cmbEISOnsiteMo.AddItem ("6 months")
cmbEISOnsiteMo.AddItem ("9 months")
cmbEISOnsiteMo.AddItem ("12 months")

'Add items
cmbPQRS.AddItem ("0")
cmbPQRS.AddItem ("1-5")
cmbPQRS.AddItem ("6-15")
cmbPQRS.AddItem ("16-100")
cmbPQRS.AddItem ("101-499")
cmbPQRS.AddItem ("500+")

End Sub


Private Sub cmdCancel_Click()
End
End Sub


Private Sub cmdOK_Click()
CostCalcForm1.Hide
'Change variables PM
With ActiveDocument
strPMOnsiteOld = ActiveDocument.Variables("vPMOnsiteDays")
strPMOnsiteNew = CostCalcForm1.txtPMOnsiteDays
If strPMOnsiteOld <> strPMOnsiteNew Then _
.Variables("vPMOnsiteDays").Value = strPMOnsiteNew

strPMRemoteOld = ActiveDocument.Variables("vPMRemoteDays")
strPMRemoteOld = CostCalcForm1.txtPMRemoteDays
If strPMRemoteOld <> strPMRemoteNew Then _
.Variables("vPMRemoteDays").Value = strPMRemoteNew
'Calculate
vPMOnsite = vPMOnsiteDays * 1250
vPMRemote = vPMRemoteDays * 1000
End With


'Change variables BA
With ActiveDocument
strBAOnsiteOld = ActiveDocument.Variables("vBAOnsiteDays")
strBAOnsiteNew = CostCalcForm1.txtBAOnsiteDays
If strBAOnsiteOld <> strBAOnsiteNew Then _
.Variables("vBAOnsiteDays").Value = strBAOnsiteNew

strBARemoteOld = ActiveDocument.Variables("vBARemoteDays")
strBARemoteOld = CostCalcForm1.txtBARemoteDays
If strBARemoteOld <> strBARemoteNew Then _
.Variables("vBARemoteDays").Value = strBARemoteNew

'Calculate
vBAOnsite = vBAOnsiteDays * 1500
vBARemote = vBARemoteDays * 1000
End With
'Change variables PQRS
With ActiveDocumnet
strPQRSrOld = ActiveDocument.Variables("vPQRSrProviders")
strPQRSrNew = CostCalcForm1.txtPQRSr
If strPQRSrOld <> strPQRSrNew Then _
.Variables("vPQRSrProviders").Value = strPQRSrNew

strPQRSrRangeOld = ActiveDocument.Variables("vPQRSrRange")
strPQRSrRangeNew = CostCalcForm1.cmbPQRS
If strPQRSrRangeOld <> strPQRSrRangeNew Then _
.Variables("vPQRSrRange").Value = strPQRSrRangeNew

strPQRScOld = ActiveDocument.Variables("vPQRScRemoteDays")
strPQRScNew = CostCalcForm1.txtPQRSc
If strPQRScOld <> strPQRScNew Then _
.Variables("vPQRScRemoteDays").Value = strPQRScNew
'Calculate
vPQRSr = (vPQRSrProviders * 250) + vPQRSFee
vPQRSc = vPQRScRemoteDays * 750

End With
'Change variables MU
With ActiveDocument
strMUOnsiteOld = ActiveDocument.Variables("vMUOnsiteDays")
strMUOnsiteNew = CostCalcForm1.txtMUOnsite
If strMUOnsiteOld <> strMUOnsiteNew Then _
.Variables("vMUOnsiteDays").Value = strMUOnsiteNew

strMURemoteOld = ActiveDocument.Variables("vMURemoteDays")
strMURemoteNew = CostCalcForm1.txtMURemote
If strMURemoteOld <> strMURemoteNew Then _
.Variables("vMURemoteDays").Value = strMURemoteNew
'Calculate
vMUOnsite = vMUOnsiteDays * 1250
vMURemote = vMURemoteDays * 1000
End With
'Change variables HBI
With ActiveDocument
strHBIOnsiteOld = ActiveDocument.Variables("vHBIOnsiteDays")
strHBIOnsiteNew = CostCalcForm1.txtHBIOnsiteDays
If strHBIOnsiteOld <> strHBIOnsiteNew Then _
.Variables("vHBIOnsiteDays").Value = strHBIOnsiteNew

strHBIRemoteOld = ActiveDocument.Variables("vHBIRemoteDays")
strHBIRemoteNew = CostCalcForm1.txtHBIRemoteDays
If strHBIRemoteOld <> strHBIRemoteNew Then _
.Variables("vHBIRemoteDays").Value = strHBIRemoteNew
'Calculate
vHBIOnsite = vHBIOnsiteDays * 1500
vHBIRemote = vHBIRemoteDays * 1000
End With
'Change variables EIS
With ActiveDocument
strEISOld = ActiveDocument.Variables("vEISOnsiteMo")
strEISNew = CostCalcForm1.cmbEISOnsiteMo
If strEISOld <> strEISNew Then _
.Variables("vEISOnsiteMo").Value = strEISNew
End With
'Change variables IIS
With ActiveDocument
strIISOnsiteOld = ActiveDocument.Variables("vIISOnsiteDays")
strIISOnsiteNew = CostCalcForm1.txtIISOnsiteDays
If strIISOnsiteOld <> strIISOnsiteNew Then _
.Variables("vIISOnsiteDays").Value = strIISOnsiteNew

strIISRemoteOld = ActiveDocument.Variables("vIISRemoteDays")
strIISOnsiteNew = CostCalcForm1.txtIISRemoteDays
If strIISRemoteOld <> strIISRemoteNew Then _
.Variables("vIISRemoteDays").Value = strIISRemoteNew
'Calculate
vIISOnsite = vIISOnsiteDays * 1500
vIISRemote = vIISRemoteDays * 1000
End With
'Change variables Training
With ActiveDocument
strTrainingOnsiteOld = ActiveDocument.Variables("vTrainingOnsiteDays")
strTrainingOnsiteNew = CostCalcForm1.txtTrainingOnsiteDays
If strTrainingOnsiteOld <> strTrainingOnsiteNew Then _
.Variables("vTrainingOnsiteDays").Value = strTrainingOnsiteNew

strTrainingRemoteOld = ActiveDocument.Variables("vTrainingRemoteDays")
strTrainingRemoteNew = CostCalcForm1.txtTrainingRemoteDays
If strTrainingRemoteOld <> strTrainingRemoteNew Then _
.Variables("vTrainingRemoteDays").Value = strTrainingRemoteNew
'Calculate
vTrainingOnsite = vTrainingOnsiteDays * 1000
vTrainingRemote = vTrainingRemoteDays * 750
End With
'Change variables ESS
With ActiveDocument
strESSOnsiteOld = ActiveDocument.Variables("vESSOnsiteDays")
strESSOnsiteNew = CostCalcForm1.txtESSOnsiteDays
If strESSOnsiteOld <> strESSOnsiteNew Then _
.Variables("vESSOnsiteDays").Value = strESSOnsiteNew
'Calculate
vESSOnsite = vESSOnsiteDays * 1500
End With
'Change variables ICD
With ActiveDocument
strICDOld = ActiveDocument.Variables("vICDRemoteDays")
strICDNew = CostCalcForm1.txtICD
If strICDOld <> strICDNew Then _
.Variables("vICDRemoteDays").Value = strICDNew
'Calculate
vICDRemote = vICDRemoteDays * 750
End With
'Change variables RCC
With ActiveDocument
strRCCOnsiteOld = ActiveDocument.Variables("vRCCOnsiteDays")
strRCCOnsiteNew = CostCalcForm1.txtRCCOnsite
If strRCCOnsiteOld <> strRCCOnsiteNew Then _
.Variables("vRCCOnsiteDays").Value = strRCCOnsiteNew

strRCCRemoteOld = ActiveDocument.Variables("vRCCRemoteDays")
strRCCRemoteNew = CostCalcForm1.txtRCCRemote
If strRCCRemoteOld <> strRCCRemoteNew Then _
.Variables("vRCCRemoteDays").Value = strRCCRemoteNew
'Calculate
vRCCOnsite = vRCCOnsiteDays * 1500
vRCCRemote = vRCCRemoteDays * 1000
End With

'Calculate total cost and cost per provider
With ActiveDocument
.Variables("vTotalCost").Value = FormatCurrency(vPMOnsite + vPMRemote + vBAOnsite + vBARemote + vPQRSr + vPQRSc _
+ vRCCOnsite + vRCCRemote + vHBIOnsite + MURemote + MUOnsite + vICDRemote _
+ vHBIRemote + vEISOnsite + vIISOnsite + vIISRemote + vTrainingOnsite + vTrainingRemote _
+ vESSOnsite)
End With
With ActiveDocument
.Variables("vCostPerProvider").Value = FormatCurrency(vTotalCost / vNumberOfProviders)
End With
'Calculate cost with discount
With ActiveDocument
strOldPercent = ActiveDocument.Variables("vDiscount")
strNewPercent = CostCalcForm1.txtDiscount
If strOldPercent <> strNewPercent Then _
.Variables("vDiscount").Value = strNewPercent

strOldDiscount = ActiveDocument.Variables("vCostAfterDiscount")
strNewDiscount = FormatCurrency(vTotalCost * (100 - vDiscount))
If strOldDiscount <> strNewDiscount Then _
.Variables("vCostAfterDiscount").Value = strNewDiscount
End With
'Calculate cost monthly installments
With ActiveDocument
strOldMonthly = ActiveDocument.Variables("vCostMonthlyInstall")
strNewMonthly = FormatCurrency(vTotalCost / 12)
If strOldMonthly <> strNewMonthly Then _
.Variables("vCostMonthlyInstall").Value = strNewMonthly
End With


'Update fields
ActiveDocument.Fields.Update


'
End Sub


Private Sub txtPMOnsiteDays_Change()
'Define
ActiveDocument.Variables("vPMOnsiteDays").Value = CostCalcForm1.txtPMOnsiteDays
End Sub


Private Sub txtPMRemoteDays_Change()
'Define
ActiveDocument.Variables("vPMRemoteDays").Value = CostCalcForm1.txtPMRemoteDays
End Sub


Private Sub txtBAOnsiteDays_Change()
'Define
ActiveDocument.Variables("vBAOnsiteDays").Value = CostCalcForm1.txtBAOnsiteDays


End Sub
Private Sub txtBARemoteDays_Change()
'Define
ActiveDocument.Variables("vBARemoteDays").Value = CostCalcForm1.txtBARemoteDays


End Sub
Private Sub cmbPQRS_Change()
'Define
ActiveDocument.Variables("vPQRSrRange").Value = CostCalcForm1.cmbPQRS.Text
'Calculate
Select Case cmbPQRS.Value
Case Is = "0"
vPQRSFee = "0"
ActiveDocument.Variables("vPQRSrRange").Value = "0"
Case Is = "1-5"
vPQRSFee = "500"
ActiveDocument.Variables("vPQRSRange").Value = "1-5"
Case Is = "6-15"
vPQRSFee = "1500"
ActiveDocument.Variables("vPQRSRange").Value = "6-15"
Case Is = "16-100"
vPQRSFee = "2000"
ActiveDocument.Variables("vPQRSRange").Value = "16-100"
Case Is = "101-499"
vPQRSFee = "4000"
ActiveDocument.Variables("vPQRSRange").Value = "101-499"
Case Is = "500+"
vPQRSFee = "5000"
ActiveDocument.Variables("vPQRSRange").Value = "500+"
End Select
End Sub
Private Sub txtPQRSr_Change()
'Define
ActiveDocument.Variables("vPQRSrProviders").Value = _
CostCalcForm1.txtPQRSr

End Sub


Private Sub txtPQRSc_Change()
'Define
ActiveDocument.Variables("vPQRScRemoteDays").Value _
= CostCalcForm1.txtPQRSc


End Sub


Private Sub txtMUOnsite_Change()
'Define
ActiveDocument.Variables("vMUOnsiteDays").Value = _
CostCalcForm1.txtMUOnsite

End Sub
Private Sub txtMURemote_Change()
'Define
ActiveDocument.Variables("vMURemoteDays").Value = CostCalcForm1.txtMURemote


End Sub
Private Sub txtHBIOnsiteDays_Change()
'Define
ActiveDocument.Variables("vHBIOnsiteDays").Value = CostCalcForm1.txtHBIOnsiteDays


End Sub
Private Sub txtHBIRemoteDays_Change()
'Define
ActiveDocument.Variables("vHBIRemoteDays").Value = CostCalcForm1.txtHBIRemoteDays

End Sub


Private Sub cmbEISOnsiteMo_Change()
'Define
ActiveDocument.Variables("vEISOnsiteMo").Value = CostCalcForm1.cmbEISOnsiteMo.Text
'Calculate
Select Case cmbEISOnsiteMo.Value
Case Is = "0 months"
vEISOnsite = "0"
ActiveDocument.Variables("vEISOnsiteMo").Value = "0 months"
Case Is = "1 month"
vEISOnsite = "25,000"
ActiveDocument.Variables("vEISOnsiteMo").Value = "1 month"
Case Is = "2 months"
vEISOnsite = "50,000"
ActiveDocument.Variables("vEISOnsiteMo").Value = "2 months"
Case Is = "3 months"
vEISOnsite = "62,000"
ActiveDocument.Variables("vEISOnsiteMo").Value = "3 months"
Case Is = "4 months"
vEISOnsite = "80,000"
ActiveDocument.Variables("vEISOnsiteMo").Value = "4 months"
Case Is = "6 months"
vEISOnsite = "110,000"
ActiveDocument.Variables("vEISOnsiteMo").Value = "6 months"
Case Is = "9 months"
vEISOnsite = "160,000"
ActiveDocument.Variables("vEISOnsiteMo").Value = "9 months"
Case Is = "12 months"
vEISOnsite = "200,000"
ActiveDocument.Variables("vEISOnsiteMo").Value = "12 months"
Case Else
MsgBox ("Error. Select one of the options from the drop-down list.")
End Select
End Sub
Private Sub txtIISOnsiteDays_Change()
'Define
ActiveDocument.Variables("vIISOnsiteDays").Value = CostCalcForm1.txtIISOnsiteDays

End Sub


Private Sub txtIISRemoteDays_Change()
'Define
ActiveDocument.Variables("vIISRemoteDays").Value = CostCalcForm1.txtIISRemoteDays

End Sub


Private Sub txtTrainingOnsiteDays_Change()
'Define
ActiveDocument.Variables("vTrainingOnsiteDays").Value = CostCalcForm1.txtTrainingOnsiteDays

End Sub


Private Sub txtTrainingRemoteDays_Change()
'Define
ActiveDocument.Variables("vTrainingRemoteDays").Value = CostCalcForm1.txtTrainingRemoteDays

End Sub


Private Sub txtESSOnsiteDays_Change()
'Define
ActiveDocument.Variables("vESSOnsiteDays").Value = CostCalcForm1.txtESSOnsiteDays

End Sub
Private Sub txtICD_Change()
'Define
ActiveDocument.Variables("vICDRemoteDays").Value = CostCalcForm1.txtICD

End Sub
Private Sub txtRCCOnsite_Change()
'Define
ActiveDocument.Variables("vRCCOnsiteDays").Value = CostCalcForm1.txtRCCOnsite

End Sub


Private Sub txtRCCRemote_Change()
'Define
ActiveDocument.Variables("vRCCRemoteDays").Value = CostCalcForm1.txtRCCRemote

End Sub


Private Sub txtNumberOfProviders_Change()
'Define
ActiveDocument.Variables("vNumberOfProviders").Value = CostCalcForm1.txtNumberOfProviders


End Sub
Private Sub txtDiscount_Change()
'Define
ActiveDocument.Variables("vDiscount").Value = CostCalcForm1.txtDiscount
End Sub


Private Sub UserForm_Click()


End Sub

[gmaxey]

Posting the whole think won't help without the user form. I won't have time to try to recreate it. Have your tried an error handler before the problem line? Try to intercept the error and then see if you can determine e.g., with debug.print what the value is that is causing the overflow error.

[docteam135]

Ahh okay, I'll try that - thanks :)

[snb]

To check insert:


With ActiveDocument
msgbox vtotalcost
msgbox vNumberOfProviders
.Variables("vCostPerProvider").Value = FormatCurrency(vTotalCost / vNumberOfProviders)
End With

I assume vNumberOfProviders will be zero to cause the problem.

Besides:


With ActiveDocument
strPMOnsiteOld = ActiveDocument.Variables("vPMOnsiteDays")
strPMOnsiteNew = CostCalcForm1.txtPMOnsiteDays
If strPMOnsiteOld <> strPMOnsiteNew Then .Variables("vPMOnsiteDays").Value = strPMOnsiteNew

strPMRemoteOld = ActiveDocument.Variables("vPMRemoteDays")
strPMRemoteOld = CostCalcForm1.txtPMRemoteDays
If strPMRemoteOld <> strPMRemoteNew Then .Variables("vPMRemoteDays").Value = strPMRemoteNew
'Calculate
vPMOnsite = vPMOnsiteDays * 1250
vPMRemote = vPMRemoteDays * 1000
End With
can be reduced to:

With ActiveDocument
.Variables("vPMOnsiteDays") = txtPMOnsiteDays
.Variables("vPMRemoteDays") = txtPMRemoteDays
'Calculate
vPMOnsite = vPMOnsiteDays * 1250
vPMRemote = vPMRemoteDays * 1000
End With