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]

i wrote correct formula , but am not getting expected output is 47601.5672865788
text box names is CTCAftNego,Stax,IntFact,MaxamtNoInt and there corresponding values 36559.5993656,8652,0.0443836,53848
here is my code

Expenses.Value = Val(CTCAftNego) + Val(Stax) + (Val(IntFact) * Val(MaxamtNoInt))
Cprofit = Val(RateFromClienPM) - Val(Expenses)

Have you tried the suggested debugging? What exactly?
It works for me, see the below testing code.

' Put this on Module1 and run to silulate values in textboxes and formula evaluation
Sub Test1()
  Const CTCAftNego = "36559.5993656"
  Const Stax = "8652"
  Const IntFact = "0.0443836"
  Const MaxamtNoInt = "53848"
  MsgBox Val(CTCAftNego) + Val(Stax) + (Val(IntFact) * Val(MaxamtNoInt))
End Sub
 
' Put this in UserForm to test formula evaluation
Private Sub UserForm_Activate()
  CTCAftNego = "36559.5993656"
  Stax = "8652"
  IntFact = "0.0443836"
  MaxamtNoInt = "53848"
  MsgBox Val(CTCAftNego) + Val(Stax) + (Val(IntFact) * Val(MaxamtNoInt))
End Sub

Check symbol of decimal separator in textboxes - it should be the dot, not the comma.

 

[ZVI]

when am use this code am getting Error "Object Requried"

Expenses.Value = CDbl(CTCAftNego.Value) + CDbl(Stax.Value) + (CDbl(IntFact.Value) * CDbl(MaxamtNoInt.Value))

Check the used names of textboxes and fix your misprints.
Then debugger stops & highlights that code line, hover the cursor of mouse over items of that formula and see which causes error.
Try debugging.

 

[sateesh kumar]

Hi ZVI


why your are put the code in userform _Activate()?

actually My CTCAftNego,Stax,IntFact,MaxamtNoInt values are depending on other textbox values. so no chance to enter COMMA operator.


here is my code

Private Sub Billamtfrmclient_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Expenses = Val(CTCAftNego) + Val(Stax) + (Val(IntFact) * Val(MaxamtNoInt))
Cprofit = Val(RateFromClienPM) - Val(Expenses)


If CprofitValue > 7500 Then
Approval.BackColor = RGB(0, 255, 0)
Approval.Text = "Proceed"
Else
Approval.BackColor = RGB(255, 0, 0)
Approval.Text = "Contact Manager : Punnam/Atul"
End If
End Sub

when am try once again am getting 11041.966046576 instead of 47601.####

i think it's Val(CTCAftNego) is not adding that why am getting 11041.966046576.

how can fix this error

Thanks for advance.

 

[ZVI]

why your are put the code in userform _Activate()?

It's for the testing purpose, of course. Have you even tried that?

As I said the code works, something is incorrect in your Userform or in your other code.
To find the reason of issue use debugging instead of guessing.

To see the code works correctly download my example 747614_Arithmetic_calculations_in_userform.zip
Open it in Excel, press Run button and press Tab key (or use Calculate button) - it puts the correct result into the textbox Expenses.
Compare that example with yours and find the difference(s) by yourself.
Use Option Explicit in the top of the code, it gets more control for debugger and helps to find the not defined variables and wrong names of controls in the code

 

[sateesh kumar]

It's for the testing purpose, of course. Have you even tried that?

As I said the code works, something is incorrect in your Userform or in your other code.
To find the reason of issue use debugging instead of guessing.

To see the code works correctly download my example 747614_Arithmetic_calculations_in_userform.zip
Open it in Excel, press Run button and press Tab key (or use Calculate button) - it puts the correct result into the textbox Expenses.
Compare that example with yours and find the difference(s) by yourself.
Use Option Explicit in the top of the code, it gets more control for debugger and helps to find the not defined variables and wrong names of controls in the code

Hi ZVI

thanks for the replaying.

the code is working when mention the Option Explicit

actually am done this help creating another variable.
here is the code

Private Sub CNegot_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim var As Long
var = Val(Stax) + (Val(IntFact) * Val(MaxamtNoInt))
Expenses = var + CTCAftNegot.Value
Cprofit = Val(RateFromClienPM) - Val(Expenses)
end sub

the above code also working .
what is Explicit means. is it need mention Option Explicit for every program?
when am entering name in C1, the output date is overwriting in same line.?
if C1 is Empty output is inserting in next empty row, why it's not overwriting when C1 have text?
find the attachment it this link https://app.box.com/s/nmben3jeuszn85q3lv7h

thanks for advance