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]

what i want is even though text box is empty, it will does not show the error because user will easily changes the code.

Use my suggestion in post # 6, point 1

 

[sateesh kumar]

my code is not working, as said that if textbox2 is empty it jumping to the next textbox3.. thats great but even though text box2 have value it's does not give the output in textbox3
my code is here i mention

Private Sub CandCtcPA_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If CandCtcPM.Value = "" Then
    Exit Sub
  CandCtcPM.Value = CDbl(CandCtcPA.Value) / 12
End If
End Sub

 

[ZVI]

my code is not working

Private Sub CandCtcPA_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If CandCtcPM.Value = "" Then
    Exit Sub
  CandCtcPM.Value = CDbl(CandCtcPA.Value) / 12
End If
End Sub

My suggestion for the code of post #5 was:

1. Try using of Val instead of CDbl like this: CandCtcPM.Value = Val(CandCtcPA) / 12

 

[sateesh kumar]

your code is working as you said in #6

thank you very much ZVI.

i hope you , you will clear the my doubts in future also ..

 

[ZVI]

Glad to know it is working now
My opinion you are fast in learning!
There are a plenty of helpers here, but as they are all volunteers just post your next question in new thread and wait for someone will come with help.
Regards

 

[sateesh kumar]

Hi ZVI

thanks very Much for replaying all my Doubts. here is last doubt.
below code is not working.

Private Sub ServiceTax_Exit(ByVal Cancel As MSForms.ReturnBoolean)


 ServiceTax = Val(ServiceTax) * 12.36 / 12
End Sub
Private Sub ServTaxTDS_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ServTaxTDS = Val(ServiceTax) - Val(TdsPer)


End Sub

i will ask onther doubt in new therad , i think it will usefull for all members .

thanks for advance

 

[ZVI]

below code is not working.

Could you please explain what exactly means "it is not working"?
Are those textboxes located in the Frame control(s)?

 

[sateesh kumar]

hi ZVI

how can i attach the file in this thread ,it will easy to you and others get on idea what am need and what am asking?

thanks for advance

 

[sateesh kumar]

Hi Zvi

i am getting zero in text box named as Service tax & TDS Differ, Service Tax (12.36%)

here is my total code.
where am getting zero values is colored in these code..

thanks for advance

Private Sub BillingAmountRec_Exit(ByVal Cancel As MSForms.ReturnBoolean)
BillingAmountRec = Val(ServTaxTDS) + Val(SalePrice) - Val(ServiceTax)
End Sub


Private Sub CandCtcPA_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  CandCtcPM = Val(CandCtcPA) / 12
End Sub


Private Sub CandCtcPA_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 46 And KeyAscii < 58) Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
CandCtcPA.Value = ""
CandCtcPA.SetFocus
End If
End Sub


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


Private Sub CandName_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
CandName.Text = LTrim(CandName.Text)
If (KeyAscii > 96 And KeyAscii < 123) Or (KeyAscii > 64 And KeyAscii < 91) Or KeyAscii = 32 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
Cancel = True
CandName.Text = ""
CandName.SetFocus
MsgBox "Only Alphabets allowed"
End If
End Sub
Private Sub CandNegoti_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CandNegoti = Val(CandTotExpPM.Value * 10) / 100
End Sub


Private Sub CandNo_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With Me.ActiveControl
        If Not IsNumeric(.Text) And .Text <> vbNullString Then
            MsgBox "Only numbers allowed"
            CandNo.SetFocus
            CandNo.Text = ""
            .Text = vbNullString
        End If
    End With
End Sub


Private Sub CandTpyeMarg_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If CandTpyeMarg = "" Then
CandTpyeMarg.SetFocus
Else
TextBox11 = CandTpyeMarg.Value
End If
End Sub




Private Sub CandTypeMargPer_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If CandTypeMargPer = "" Then
CandTypeMargPer.SetFocus
Else
CmpnMargPM = CDbl(CandTotExpPM.Value) * CDbl(CandTypeMargPer.Value / 100)
End If
If TextBox11.Value > CmpnMargPM.Value Then
IntialMarg = TextBox11.Value
Else
IntialMarg = CmpnMargPM.Value
End If
End Sub
Private Sub ClientNegotiation_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ClientNegotiation.Value = Val(RateToClientPM * 25) / 100
End Sub


Private Sub cmdSubmit_Click()
Dim CandCtcPM As Long
Dim RowCount As Long
If Me.CandName.Text = "" Then
MsgBox "Please enter CandName", vbExclamation, "Cand Data"
Me.CandName.SetFocus
End If
If Not IsNumeric(Me.CandNo.Value) Then
MsgBox "The Amount box must contain a number.", vbExclamation, "Employee Data"
Me.CandNo.SetFocus
End If
RowCount = Worksheets("Sheet1").Range("A2").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A2")
    .Offset(RowCount, 0) = Me.CandNo.Value
    .Offset(RowCount, 1) = CandName.Value
    .Offset(RowCount, 2) = CandCtcPA.Value
    .Offset(RowCount, 3) = CandCtcPM
    .Offset(RowCount, 4) = CandExpHPA.Value
    .Offset(RowCount, 5) = CandExpHPmPer.Value
    .Offset(RowCount, 6) = CandExpHPMFig.Value
    .Offset(RowCount, 7) = CandTotExpPM.Value
    .Offset(RowCount, 8) = CandTpyeMarg.Value
    .Offset(RowCount, 9) = CandTypeMargPer.Value
    .Offset(RowCount, 10) = TextBox11.Value
    .Offset(RowCount, 11) = CmpnMargPM.Value
    .Offset(RowCount, 12) = IntialMarg.Value
    .Offset(RowCount, 13) = RateToClientPM.Value
    .Offset(RowCount, 14) = Terms.Value
    .Offset(RowCount, 15) = IntrCollPeriod.Value
    .Offset(RowCount, 16) = ClientNegotiation.Value
    .Offset(RowCount, 17) = CandNegoti.Value
    .Offset(RowCount, 18) = CtcAftNegoti.Value
    .Offset(RowCount, 19) = SalePrice.Value
    .Offset(RowCount, 20) = ServTaxTDS.Value
    .Offset(RowCount, 21) = ServiceTax.Value
    .Offset(RowCount, 22) = TdsPer.Value
    .Offset(RowCount, 23) = BillingAmountRec.Value
    .Offset(RowCount, 24) = FinalMarg.Value
            
End With


MsgBox "Successfully Inserted Candidate Details"
End Sub
Private Sub CtcAftNegoti_Exit(ByVal Cancel As MSForms.ReturnBoolean)


CtcAftNegoti = Val(CandTotExpPM) - Val(CandNegoti)
End Sub


Private Sub FinalMarg_Exit(ByVal Cancel As MSForms.ReturnBoolean)
FinalMarg = Val(BillingAmountRec) - (Val(CandTotExpPM) - Val(CandNegoti)) + Val(IntialMarg)
End Sub


Private Sub IntialMarg_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox11.Value > CmpnMargPM.Value Then
RateToClientPM.Value = Val(CandTotExpPM.Value) + Val(TextBox11.Value)
Else
RateToClientPM.Value = Val(CandTotExpPM.Value) + Val(CmpnMargPM.Value)
End If
End Sub


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


 ServiceTax = Val(ServiceTax) * 12.36 / 12
End Sub


Private Sub ServTaxTDS_Exit(ByVal Cancel As MSForms.ReturnBoolean)


ServTaxTDS = Val(ServiceTax) - Val(TdsPer)


End Sub


Private Sub TdsPer_Exit(ByVal Cancel As MSForms.ReturnBoolean)


TdsPer = Val(SalePrice.Value) + Val(ServiceTax.Value) / 10
End Sub



Private Sub Terms_Exit(ByVal Cancel As MSForms.ReturnBoolean)
IntrCollPeriod.Value = Val(RateToClientPM * 18 / 36500) * Val(Terms)
End Sub

 

[ZVI]

how can i attach the file in this thread?

You can't attach file in this forum. But it is possible to upload file for example onto https://app.box.com and provide us by link.

Please ask my question: Are those textboxes located in the Frame control(s)?