View Full Version : Calculations between Textbox values on Form

07-18-2007, 03:25 AM

I have a form where users input various values and the data is written to a hidden sheet.

One of the checks carried out is to 2 textboxes on the form. The user inputs numeric values to each textbox. The code then checks that the customer deposit is not more than the asset cost. However, no matter what value is input as the deposit, the code always thinks it is greater than the asset cost. I can't see why this should happen.

Private Sub txbDeposit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'checks the value of the deposit against the asset cost
If txbDeposit.Value > txbAssCost.Value Then 'if the deposit is more than the cost...
MsgBox "The deposit is greater than the cost - please check.", 48, "Invalid Input - Deposit Amount"
Cancel = True
With txbDeposit
.Value = ""
.SetFocus 'reset the focus back to the empty field
End With
If txbAssCost.Value = "" Or txbAssCost.Value = 0 Then 'if the Asset Cost textbox is empty then...
MsgBox "An Asset Cost amount must be entered.", 48, "Calculation Error - Asset Cost Field"
Cancel = True
txbAssCost.SetFocus 'reset the focus back to the empty field
End If
'if the input is OK, update some sheet values...
txbFinance.Value = txbAssCost - txbDeposit 'works out the value to be financed
txbDeposit.Value = Format(txbDeposit.Value, "#,##0") 'formats the deposit field
lbPercentage.Caption = Format((frmMain.txbDeposit.Value / _
frmMain.txbAssCost.Value) * 100, "#,##0.00") & "%" 'calculates the deposit %age
lbPercentage.Visible = True 'shows the %age box
Sheets("Form").Range("B10").Value = txbDeposit.Value
End If
End Sub

I'd be grateful if anyone could point out my latest explosion of stupidity. :dunno

07-18-2007, 03:57 AM
Try converting the textbox values to numbers. Like
If CSng(txbDeposit.Value) > CSng(txbAssCost.Value) Then


07-18-2007, 04:07 AM

Thank you. Obviously too simple an idea for my brain to cope with!