how to use arithmetic calculations in userform text boxes

[sateesh kumar]

hi all

Wish you happy new year to all

am new to VBA, am creating User form, in that i want to calculate sum, sub, multiplication, %.. etc based other text box values.
am facing problem with % calculations.


Thanks in advance ..

 

[ZVI]

Hi,

Put math formula to TextBox1 and try this: MsgBox Evaluate(Replace(TextBox1, "%", "/100"))
Or build the similar formula in the string variable instead of the TextBox1 and do the same.

Regards

 

[sateesh kumar]

Hi,

Put math formula to TextBox1 and try this: MsgBox Evaluate(Replace(TextBox1, "%", "/100"))
Or build the similar formula in the string variable instead of the TextBox1 and do the same.

Regards

Thanks for replaying Mr ZVI


What you said is am not getit, becuse of am poor in VBA, take time to understand. am complete this using below code. it's working as per my requriements.

what am doing in my user form , when i put figure in text box,it will treat as % value and calculate and give the result in other text box.

Private Sub CandExpHPmPer_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If CandExpHPmPer = "" Or CandCtcPA = "" Or CandCtcPM = "" Then
CandExpHPmPer.SetFocus
Else
CandExpHPMFig.Value = CDbl((CandExpHPmPer.Value) / 100) * CDbl(CandCtcPM.Value)
CandTotExpPM.Value = CDbl(CandCtcPM.Value) + CDbl(CandExpHPMFig.Value)
End If
End Sub

 

[ZVI]

Looks good!

It'll also work without CDbl because of VBA automatic type coercion of string values to the numeric values at any math operations:
CandExpHPMFig = CandCtcPM * CandExpHPmPer / 100
CandTotExpPM = CandCtcPM + CandExpHPMFig

But using of CDbl and property .Value in your code means that all is under your control.

 

[sateesh kumar]

hi ZVI

am getting another error "TypeMissMacthed"

my code is working perfectly, but when textbox is move without value the error comeing. i dont what's going there, what to do now?
my code is here

Private Sub CandCtcPA_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.ActiveControl
        If Not IsNumeric(.Value) And .Value <> vbNullString Then
            MsgBox "Only numbers allowed"
            CandCtcPA.SetFocus
            .Value = vbNullString
            CandCtcPA.Value = ""
        Else
           CandCtcPM.Value = CDbl(CandCtcPA.Value) / 12
        End If
    End With
End Sub

and also am getting wrong output in this code

Private Sub SalePrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
SalePrice.Value = RateToClientPM.Value + IntrCollPeriod.Value + CandNegoti.Value - ClientNegotiation.Value
End Sub

am unable add the jpg image in this therad ,becuse every one easily understood what am facing problem.

thanks for advance.

 

[ZVI]

1. Try using of Val instead of CDbl like this: CandCtcPM.Value = Val(CandCtcPA) / 12
For empty value of control Val function returns zero instead of error in CDbl return.
If decimal separator is used then make sure it is the dot symbol for correct return of Val function.

2. See if this suits:

    If Not IsNumeric(.Value) Then
      MsgBox "Only numbers allowed"
      Cancel = True ' <-- comment this if it is not required
      .Value = vbNullString

3. To understand what is wrong in output, post the values of controls and the expected value

 

[ZVI]

To force highlighting in UserForm of the code line with error set up this option:
VBE - menu 'Tools' – 'Options' – tab 'General' – 'Break in Class Module'.
And after error message is appeared choose 'Debug' to go to the highlighted code line.

In your case debugger will highlight the line CandCtcPM.Value = CDbl(CandCtcPA.Value) / 12
And hovering the mouse cursor over CDbl will show the error Type mismatch <type missmacth=""></type> because CandCtcPA is empty which is not supported by CDbl.

 

[sateesh kumar]

1. Try using of Val instead of CDbl like this: CandCtcPM.Value = Val(CandCtcPA) / 12
For empty value of control Val function returns zero instead of error in CDbl return.
If decimal separator is used then make sure it is the dot symbol for correct return of Val function.

2. See if this suits:

    If Not IsNumeric(.Value) Then
      MsgBox "Only numbers allowed"
      Cancel = True ' <-- comment this if it is not required
      .Value = vbNullString

3. To understand what is wrong in output, post the values of controls and the expected value

hi ZVI..

Thanks for replaying to my another Doubt.

Cancel = True ' <-- comment this if it is not required

it's required because the user will not come out with out filling the text box, cursor will focus on the text box.


my Expected value is 25558.89 for the below code,
Input values is RateToClientPM.Value=31900.00
IntrCollPeriod.Value=943.89
CandNegoti.Value=690.00
ClientNegotiation.Value=7975.00

Private Sub SalePrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
SalePrice.Value = CDbl(RateToClientPM.Value + IntrCollPeriod.Value) + CDbl(CandNegoti.Value - ClientNegotiation.Value)
End Sub

And one more doubt , i want to change text, text color in the text box based on the other text box value is greeter than or smaller than.

Thanks for advance

 

[sateesh kumar]

Yes Mr. ZVI


What you said is 100% correct, Type mismatch is occurring when only text box is empty.
what i want is even though text box is empty, it will does not show the error because user will easily changes the code.


here is my problem is solved with this code. for my self am great feeling now, because am for my self am clear the my error.

but am facing wrong o/p for addition of Textbox values. i will try once again why its?

If CandCtcPM.Value = "" Then
Exit Sub
Else
  CandCtcPM.Value = CDbl(CandCtcPA.Value) / 12
  End If

 

[ZVI]

my Expected value is 25558.89 for the below code,
Input values is RateToClientPM.Value=31900.00
IntrCollPeriod.Value=943.89
CandNegoti.Value=690.00
ClientNegotiation.Value=7975.00
...
And one more doubt , i want to change text, text color in the text box based on the other text box value is greeter than or smaller than.

Use this:

Private Sub SalePrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  SalePrice = Val(RateToClientPM) + Val(IntrCollPeriod) + Val(CandNegoti) - Val(ClientNegotiation)
End Sub

To change background color of control use its .BackColor property
For example: If Val(TextBox1) > 0 Then TextBox1.BackColor = RGB(255, 255, 0)
Read on-line help about RGB.

Select TextBox and press F4 to see its set of properties.
Select any property and press F1 to read the help about it.