Textbox calculation not working with keypress function

G

gsharad

New Member
Joined
Apr 26, 2017
Messages
13
Hi,

I am trying to calculate the value in textbox in real time as the user are entering them by capturing thru keypress function.
so i have to show that real time calc in 2 places..
1 place it is working fine and the other place it is not.
Apologies for the basic level prog effort.. :)
Below is the code...
----------------------------
Private Sub CommandButton1_Click()
Unload Me
End Sub



Private Sub CommandButtonQtr_Click()
' To write edited info of userform2 to Sheets("Sheet Name")
Dim lastRow As Long
Dim ABnum As Double
Dim ABrng As Range
Dim WriteRow As Long

' Make sure we're on the right sheet
'Sheets("Sheet1").Active
Sheet1.Activate
Sheets("Sheet1").Unprotect Password:="2017"

With ActiveSheet

' Get the last row used so can set up the search range
lastRow = .Cells(.Rows.Count, "AO").End(xlUp).Row
' Set the range to search for the AB number
Set ABrng = .Range("A1:A" & lastRow)
' Get the AB number from what is selected on userform2
ABnum = TextBoxSearch2.Value
' Get the row of sheet for this AB number
WriteRow = Application.Match(ABnum, ABrng, 0)

' Make this AB number the active cell
Cells(WriteRow, 1).Select

If MsgBox("These changes cannot be undone. Do you wish to proceed?", vbYesNo) = vbNo Then
Exit Sub
End If


' Write in all the editable stuff, don't bother with the non-editable things
With ActiveCell
'----------------------------------
'.Offset(0, 1).Value = TextBox1.Value

.Offset(0, 58).Value = TextBoxProdQ1.Value
'to check blanks and non-numeric entry
If Not IsNumeric(TextBoxProdQ1.Value) Then
MsgBox ("Sorry, must enter a Valid Amount")
TextBoxProdQ1.SetFocus
Exit Sub
End If

.Offset(0, 59).Value = TextBoxProdQ2.Value
'to check blanks and non-numeric entry
If Not IsNumeric(TextBoxProdQ2.Value) Then
MsgBox ("Sorry, must enter a Valid Amount")
TextBoxProdQ2.SetFocus
Exit Sub
End If

.Offset(0, 60).Value = TextBoxProdQ3.Value
'to check blanks and non-numeric entry
If Not IsNumeric(TextBoxProdQ3.Value) Then
MsgBox ("Sorry, must enter a Valid Amount")
TextBoxProdQ3.SetFocus
Exit Sub
End If

.Offset(0, 61).Value = TextBoxProdQ4.Value
'to check blanks and non-numeric entry
If Not IsNumeric(TextBoxProdQ4.Value) Then
MsgBox ("Sorry, must enter a Valid Amount")
TextBoxProdQ4.SetFocus
Exit Sub
End If

.Offset(0, 63).Value = TextBoxProdCheck1.Value
'.Offset(0, 63).Value = Total2.Value
'to check blanks and non-numeric entry


.Offset(0, 64).Value = TextBoxNonProdQ1.Value
'to check blanks and non-numeric entry
If Not IsNumeric(TextBoxNonProdQ1.Value) Then
MsgBox ("Sorry, must enter a Valid Amount")
TextBoxNonProdQ1.SetFocus
Exit Sub
End If

.Offset(0, 65).Value = TextBoxNonProdQ2.Value
'to check blanks and non-numeric entry
If Not IsNumeric(TextBoxNonProdQ2.Value) Then
MsgBox ("Sorry, must enter a Valid Amount")
TextBoxNonProdQ2.SetFocus
Exit Sub
End If

.Offset(0, 66).Value = TextBoxNonProdQ3.Value
'to check blanks and non-numeric entry
If Not IsNumeric(TextBoxNonProdQ3.Value) Then
MsgBox ("Sorry, must enter a Valid Amount")
TextBoxNonProdQ3.SetFocus
Exit Sub
End If


.Offset(0, 67).Value = TextBoxNonProdQ4.Value
'to check blanks and non-numeric entry
If Not IsNumeric(TextBoxNonProdQ4.Value) Then
MsgBox ("Sorry, must enter a Valid Amount")
TextBoxNonProdQ4.SetFocus
Exit Sub
End If

.Offset(0, 69).Value = TextBoxCombProdQQ1.Value
'to check blanks and non-numeric entry
'If Not IsNumeric(TextBoxProdQ1.Value) Then
' MsgBox ("Sorry, must enter a Valid Amount")
' TextBoxProdQ1.SetFocus
' Exit Sub
' End If

.Offset(0, 70).Value = TextBoxCombProdQQ2.Value
'to check blanks and non-numeric entry
'If Not IsNumeric(TextBoxProdQ2.Value) Then
' MsgBox ("Sorry, must enter a Valid Amount")
' TextBoxProdQ2.SetFocus
' Exit Sub
' End If


.Offset(0, 71).Value = TextBoxCombProdQQ3.Value
'to check blanks and non-numeric entry
'If Not IsNumeric(TextBoxProdQ3.Value) Then
' MsgBox ("Sorry, must enter a Valid Amount")
' TextBoxProdQ3.SetFocus
' Exit Sub
'End If

.Offset(0, 72).Value = TextBoxCombProdQQ4.Value
'to check blanks and non-numeric entry
'If Not IsNumeric(TextBoxProdQ4.Value) Then
' MsgBox ("Sorry, must enter a Valid Amount")
' TextBoxProdQ1.SetFocus
' Exit Sub
' End If

.Offset(0, 41).Value = QTNewRun.Value

.Offset(0, 42).Value = TextBoxERCPipelineNum.Value
'to check blanks and non-numeric entry
If Not IsNumeric(TextBoxERCPipelineNum.Value) Then
MsgBox ("Sorry, must enter a Valid Amount, if not matched/updated please enter 0")
TextBoxERCPipelineNum.SetFocus
Exit Sub
End If

.Offset(0, 43).Value = TextBoxDateUpdated.Value


' Put the cursor in upper left corner
Cells(1, 1).Select
End With

Sheets("Sheet1").Protect Password:="2017"
' Unload the userform
' Unload Me
Call UserForm_Initialize
End With

End Sub



Private Sub CommandButtonSearchRow_Click()
' to search the data thru Serial number
Dim r As Range, rAll As Range
Dim sTerm As String

Application.ScreenUpdating = False

sTerm = TextBoxSearch2.Value

With Sheets("Sheet1")
Set rAll = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
For Each r In rAll
If InStr(r, sTerm) Then
With CRLInput
'----
TextBoxDealName2 = r.Offset(0, 4)
TextBoxPrjDuration2 = r.Offset(0, 8)
TextBoxDealTCV2 = r.Offset(0, 9)
TextBoxITRMDealVal = r.Offset(0, 44)
QTNewRun = r.Offset(0, 41)
TextBoxDealAnnualCV = r.Offset(0, 12)

TextBoxProdQ1 = r.Offset(0, 58)
TextBoxProdQ2 = r.Offset(0, 59)
TextBoxProdQ3 = r.Offset(0, 60)
TextBoxProdQ4 = r.Offset(0, 61)
'TextBoxProdCheck1 = r.Offset(0, 62)

TextBoxNonProdQ1 = r.Offset(0, 64)
TextBoxNonProdQ2 = r.Offset(0, 65)
TextBoxNonProdQ3 = r.Offset(0, 66)
TextBoxNonProdQ4 = r.Offset(0, 67)

TextBoxCombProdQQ1 = r.Offset(0, 70)
TextBoxCombProdQQ2 = r.Offset(0, 71)
TextBoxCombProdQQ3 = r.Offset(0, 72)
TextBoxCombProdQQ4 = r.Offset(0, 73)

End With
End If
Next r
End With

End Sub

Private Sub Frame1_Click()

End Sub

Private Sub Frame2_Click()

End Sub

Private Sub Frame3_Click()

End Sub

'function to check the numeric values to be calculated
Private Function NumericOnly(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger
Dim Key As MSForms.ReturnInteger
Select Case KeyAscii
Case 46, 48 To 57 ' Accept only decimal "." and numbers [0-9]
Set Key = KeyAscii
Case Else
KeyAscii = 0 ' Minor bug earlier
Set Key = KeyAscii
End Select
Set NumericOnly = Key
End Function


' keypress calculations capture
Private Sub TextBoxProdQ1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
KeyAscii = NumericOnly(KeyAscii)
End Sub

Private Sub TextBoxProdQ2_KeyPress(ByVal KeyAscii1 As MSForms.ReturnInteger)
KeyAscii1 = NumericOnly(KeyAscii1)
End Sub
Private Sub TextBoxProdQ3_KeyPress(ByVal KeyAscii2 As MSForms.ReturnInteger)
KeyAscii2 = NumericOnly(KeyAscii2)
End Sub

Private Sub TextBoxProdQ4_KeyPress(ByVal KeyAscii3 As MSForms.ReturnInteger)
KeyAscii3 = NumericOnly(KeyAscii3)
End Sub

' keypress calculations capture
Private Sub TextBoxNonProdQ1_KeyPress(ByVal KeyAscii4 As MSForms.ReturnInteger)
KeyAscii4 = NumericOnly(KeyAscii4)
End Sub

Private Sub TextBoxNonProdQ2_KeyPress(ByVal KeyAscii5 As MSForms.ReturnInteger)
KeyAscii5 = NumericOnly(KeyAscii5)
End Sub
Private Sub TextBoxNonProdQ3_KeyPress(ByVal KeyAscii6 As MSForms.ReturnInteger)
KeyAscii6 = NumericOnly(KeyAscii6)
End Sub

Private Sub TextBoxNonProdQ4_KeyPress(ByVal KeyAscii7 As MSForms.ReturnInteger)
KeyAscii7 = NumericOnly(KeyAscii7)
End Sub

'FUNCTION for calculation
Private Sub TextBoxesSum()
Dim Total As Double
Total = 0
If Len(TextBoxProdQ1.Value) > 0 Then Total = Total + CDbl(TextBoxProdQ1.Value)
If Len(TextBoxProdQ2.Value) > 0 Then Total = Total + CDbl(TextBoxProdQ2.Value)
If Len(TextBoxProdQ3.Value) > 0 Then Total = Total + CDbl(TextBoxProdQ3.Value)
If Len(TextBoxProdQ4.Value) > 0 Then Total = Total + CDbl(TextBoxProdQ4.Value)
' Add more for the rest of your text boxes
' Total = Val(TextBoxDealTCV2.Value) - (Val(TextBoxProdQ1.Value) + Val(TextBoxProdQ2.Value) + Val(TextBoxProdQ3.Value) + Val(TextBoxProdQ4.Value))
TextBoxProdCheck1 = Val(TextBoxITRMDealVal.Text) - Total
'Total = 0

End Sub


'calling function in individual input text boxes
Private Sub TextBoxProdQ1_Change()
TextBoxesSum
End Sub
Private Sub TextBoxProdQ2_Change()
TextBoxesSum
End Sub
Private Sub TextBoxProdQ3_Change()
TextBoxesSum
End Sub
Private Sub TextBoxProdQ4_Change()
TextBoxesSum
End Sub
Private Sub TextBoxProdCheck1_Change()
TextBoxesSum
End Sub

Private Sub TextBoxCombProdQQ1_Change()
'TextBoxesSum2
If Len(TextBoxNonProdQ1.Value) > 0 Then
TextBoxCombProdQQ1.Value = Val(TextBoxCombProdQQ1.Text) + CDbl((Val(TextBoxProdQ1.Text)) + CDbl(Val(TextBoxNonProdQ1.Text)))
End If
End Sub
Private Sub TextBoxCombProdQQ2_Change()
If Len(TextBoxNonProdQ2.Value) > 0 Then
TextBoxCombProdQQ2.Value = Val(TextBoxCombProdQQ2.Text) + (Val(TextBoxProdQ2.Text) + Val(TextBoxNonProdQ2.Text))
End If
'TextBoxesSum3
End Sub
Private Sub TextBoxCombProdQQ3_Change()
If Len(TextBoxNonProdQ3.Value) > 0 Then
TextBoxCombProdQQ3.Value = Val(TextBoxCombProdQQ3.Text) + (Val(TextBoxProdQ3.Text) + Val(TextBoxNonProdQ3.Text))
End If
'TextBoxesSum4
End Sub
Private Sub TextBoxCombProdQQ4_Change()
If Len(TextBoxNonProdQ4.Value) > 0 Then
TextBoxCombProdQQ4.Value = Val(TextBoxCombProdQQ4.Text) + (Val(TextBoxProdQ4.Text) + Val(TextBoxNonProdQ4.Text))
End If
' TextBoxesSum5
End Sub



Private Sub UserForm_Click()

End Sub
Private Sub UserForm_Initialize()

TextBoxSearch2.Value = ""


QTNewRun.Value = ""
QTNewRun.Enabled = False

TextBoxERCPipelineNum.Value = ""

TextBoxDealName2.Value = ""
TextBoxDealName2.Enabled = False

TextBoxDealTCV2.Value = ""
TextBoxDealTCV2.Enabled = False

TextBoxITRMDealVal.Value = ""
TextBoxITRMDealVal.Enabled = False

TextBoxDealAnnualCV.Value = ""
TextBoxDealAnnualCV.Enabled = False

TextBoxProdQ1.Value = 0
TextBoxProdQ2.Value = 0
TextBoxProdQ3.Value = 0
TextBoxProdQ4.Value = 0

TextBoxProdCheck1.Value = Total
TextBoxProdCheck1.Enabled = False

TextBoxNonProdQ1.Value = 0
TextBoxNonProdQ2.Value = 0
TextBoxNonProdQ3.Value = 0
TextBoxNonProdQ4.Value = 0

TextBoxCombProdQQ1.Value = 0
TextBoxCombProdQQ1.Enabled = False

TextBoxCombProdQQ2.Value = 0
TextBoxCombProdQQ2.Enabled = False

TextBoxCombProdQQ3.Value = 0
TextBoxCombProdQQ3.Enabled = False

TextBoxCombProdQQ4.Value = 0
TextBoxCombProdQQ4.Enabled = False

TextBoxDateUpdated.Value = Now
TextBoxDateUpdated = Format(TextBoxDateUpdated.Value, "dd mmmm yyyy")
TextBoxDateUpdated.Enabled = False

'Set Focus on Serial number
TextBoxSearch2.SetFocus

End Sub
-------------------------------------------------
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Apologies for the long code...
I am shortening it with part that is working and one that is not.
Code that is working :
'function to check the numeric values to be calculated
Private Function NumericOnly(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger
Dim Key As MSForms.ReturnInteger
Select Case KeyAscii
Case 46, 48 To 57 ' Accept only decimal "." and numbers [0-9]
Set Key = KeyAscii
Case Else
KeyAscii = 0 ' Minor bug earlier
Set Key = KeyAscii
End Select
Set NumericOnly = Key
End Function
' keypress calculations capture
Private Sub TextBoxProdQ1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
KeyAscii = NumericOnly(KeyAscii)
End Sub

Private Sub TextBoxProdQ2_KeyPress(ByVal KeyAscii1 As MSForms.ReturnInteger)
KeyAscii1 = NumericOnly(KeyAscii1)
End Sub
Private Sub TextBoxProdQ3_KeyPress(ByVal KeyAscii2 As MSForms.ReturnInteger)
KeyAscii2 = NumericOnly(KeyAscii2)
End Sub

Private Sub TextBoxProdQ4_KeyPress(ByVal KeyAscii3 As MSForms.ReturnInteger)
KeyAscii3 = NumericOnly(KeyAscii3)
End Sub
'FUNCTION for calculation
Private Sub TextBoxesSum()
Dim Total As Double
Total = 0
If Len(TextBoxProdQ1.Value) > 0 Then Total = Total + CDbl(TextBoxProdQ1.Value)
If Len(TextBoxProdQ2.Value) > 0 Then Total = Total + CDbl(TextBoxProdQ2.Value)
If Len(TextBoxProdQ3.Value) > 0 Then Total = Total + CDbl(TextBoxProdQ3.Value)
If Len(TextBoxProdQ4.Value) > 0 Then Total = Total + CDbl(TextBoxProdQ4.Value)
' Add more for the rest of your text boxes
' Total = Val(TextBoxDealTCV2.Value) - (Val(TextBoxProdQ1.Value) + Val(TextBoxProdQ2.Value) + Val(TextBoxProdQ3.Value) + Val(TextBoxProdQ4.Value))
TextBoxProdCheck1 = Val(TextBoxITRMDealVal.Text) - Total
'Total = 0

End Sub

'calling function in individual input text boxes
Private Sub TextBoxProdQ1_Change()
TextBoxesSum
End Sub
Private Sub TextBoxProdQ2_Change()
TextBoxesSum
End Sub
Private Sub TextBoxProdQ3_Change()
TextBoxesSum
End Sub
Private Sub TextBoxProdQ4_Change()
TextBoxesSum
End Sub
Private Sub TextBoxProdCheck1_Change()
TextBoxesSum
End Sub
---------------Code that is not working------------
' keypress calculations capture
Private Sub TextBoxNonProdQ1_KeyPress(ByVal KeyAscii4 As MSForms.ReturnInteger)
KeyAscii4 = NumericOnly(KeyAscii4)
End Sub

Private Sub TextBoxNonProdQ2_KeyPress(ByVal KeyAscii5 As MSForms.ReturnInteger)
KeyAscii5 = NumericOnly(KeyAscii5)
End Sub
Private Sub TextBoxNonProdQ3_KeyPress(ByVal KeyAscii6 As MSForms.ReturnInteger)
KeyAscii6 = NumericOnly(KeyAscii6)
End Sub

Private Sub TextBoxNonProdQ4_KeyPress(ByVal KeyAscii7 As MSForms.ReturnInteger)
KeyAscii7 = NumericOnly(KeyAscii7)
End Sub

Private Sub TextBoxCombProdQQ1_Change()
'TextBoxesSum2
If Len(TextBoxNonProdQ1.Value) > 0 Then
TextBoxCombProdQQ1.Value = Val(TextBoxCombProdQQ1.Text) + CDbl((Val(TextBoxProdQ1.Text)) + CDbl(Val(TextBoxNonProdQ1.Text)))
End If
End Sub
Private Sub TextBoxCombProdQQ2_Change()
If Len(TextBoxNonProdQ2.Value) > 0 Then
TextBoxCombProdQQ2.Value = Val(TextBoxCombProdQQ2.Text) + (Val(TextBoxProdQ2.Text) + Val(TextBoxNonProdQ2.Text))
End If
'TextBoxesSum3
End Sub
Private Sub TextBoxCombProdQQ3_Change()
If Len(TextBoxNonProdQ3.Value) > 0 Then
TextBoxCombProdQQ3.Value = Val(TextBoxCombProdQQ3.Text) + (Val(TextBoxProdQ3.Text) + Val(TextBoxNonProdQ3.Text))
End If
'TextBoxesSum4
End Sub
Private Sub TextBoxCombProdQQ4_Change()
If Len(TextBoxNonProdQ4.Value) > 0 Then
TextBoxCombProdQQ4.Value = Val(TextBoxCombProdQQ4.Text) + (Val(TextBoxProdQ4.Text) + Val(TextBoxNonProdQ4.Text))
End If
' TextBoxesSum5
End Sub
 
Upvote 0
Why do you have KeyAscii1, KeyAscii2, KeyAscii3 etc?
 
Last edited:
Upvote 0
Have you tried setting breakpoints on the KeyPress subs to check if they are actually bring executed?
 
Upvote 0
No. I hvnt tried that. My issue is that one part of the code they are working, and other part they dont.

Is there any other way to show user realtime the user input in txtbox 1,2,3,4 and their auto sum in txtox1 1,2,3,4 and thn user can press submit button
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
124
6StringJazzer
6StringJazzer
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
178
Denny57
D
I
  • Question Question
TextBox value to worksheet cell info
2
Replies
12
Views
685
gallen
gallen
I
  • Question Question
Edit for current working userform
Replies
2
Views
236
ipbr21054
I
J
  • Locked
  • Question Question
VBA Close With Save Causes Runtime Error
Replies
1
Views
1K
Scott Huish
Scott Huish

Forum statistics

Threads
1,224,521
Messages
6,179,277
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top