View Full Version : [SOLVED:] Simple VBA calculation
onmyway
02-03-2015, 04:14 AM
Hi all,
I have a simple math problem.
I am trying to automatically calculate the value of a couple fields.
It is a very simple equation:
txtStockEOS1 = txtStockSOS1.value + txtStockReceive1.value - txtStockLost1.value - txtStockBroken1.value - txtStockWorn1.value
I.e., 10 = 5+8-1-1-1
I get a run-time error 13: type mismatch when I enter a value in one of my fields whilst testing. This is my code:
Private Sub txtStockSOS1_Change()
Me.txtStockEOS1 = CDbl(txtStockSOS1.Value) + CDbl(txtStockReceive1.Value) - CDbl(txtStockLost1.Value) - CDbl(txtStockBroken1.Value) - CDbl(txtStockWorn1.Value)
End Sub
CDbl() will error against an empty string, which is what the other textboxes return if empty when txtStockSOS1 is changed. Does that make sense?
Mark
onmyway
02-03-2015, 04:46 AM
Hi GTO
Thanks for the feedback.
It does make sense. What can I use for auto calculating then? i.e., _change event? Or how can i overcome the challenge of the blank fields?
Bob Phillips
02-03-2015, 06:05 AM
Private Sub txtStockSOS1_Change()
Dim tmp As Double
If txtStockSOS1.Value <> "" Then tmp = tmp + CDbl(txtStockSOS1.Value)
If txtStockReceive1.Value <> "" Then tmp = tmp + CDbl(txtStockReceive1.Value)
If txtStockLost1.Value <> "" Then tmp = tmp - CDbl(txtStockLost1.Value)
If txtStockBroken1.Value <> "" Then tmp = tmp - CDbl(txtStockBroken1.Value)
If txtStockWorn1.Value <> "" Then tmp = tmp - CDbl(txtStockWorn1.Value)
Me.txtStockEOS1 = tmp
End Sub
onmyway
02-03-2015, 06:27 AM
hi xld
Thank you very much! Works great!
One question: i need to have this _change event for each of the fields: txtStockSOS1; txtStockReceive1; txtStockLost1; txtStockBroken1; txtStockWorn1
is there an elegant way to create this event for all, or do I have to do it one-by-one for each field?
Private Sub txtStockSOS1_Change()
Me.txtStockEOS1 = val(txtStockSOS1.Value) + val(txtStockReceive1.Value) - val(txtStockLost1.Value) - val(txtStockBroken1.Value) - val(txtStockWorn1.Value)
End Sub
onmyway
02-03-2015, 07:03 AM
hi snb
thank you, works great! although xld's method worked as well, i got errors when i entered non numeric values (i have the fields pre-formatted to check for numeric values etc.).
Thank you all!
Paul_Hossler
02-03-2015, 07:18 AM
Don't know it's as elegant as XLD will come up with, but something like this with all the calculations captured in a sub in the same userform module would be the simplest approach (I think)
You still need the multiple _Change events
Also, I'd suggest adding some error checking to fail softly if a non-numeric entry happens to be made
Private Sub txtStockSOS1_Change()
If IsNumeric (txtStockSOS1.Value)
Call CalctxtStockEOS
Else
Msgbox "oops"
End If
End Sub
Private Sub txtStockSOS1_Change()
Call CalctxtStockEOS
End Sub
'etc.
Private Sub CalctxtStockEOS()
Dim tmp As Double
If txtStockSOS1.Value <> "" Then tmp = tmp + CDbl(txtStockSOS1.Value)
If txtStockReceive1.Value <> "" Then tmp = tmp + CDbl(txtStockReceive1.Value)
If txtStockLost1.Value <> "" Then tmp = tmp - CDbl(txtStockLost1.Value)
If txtStockBroken1.Value <> "" Then tmp = tmp - CDbl(txtStockBroken1.Value)
If txtStockWorn1.Value <> "" Then tmp = tmp - CDbl(txtStockWorn1.Value)
Me.txtStockEOS1 = tmp
End Sub
onmyway
02-03-2015, 07:23 AM
Hi Paul, thank you for the effort! I have decided to go with snb's method.
other approaches:
Private Sub UserForm_Click()
MsgBox Application.Sum(Array(Val(TextBox1), Val(TextBox2), Val(TextBox3), Val(TextBox4)))
End Sub
Private Sub UserForm_Click()
MsgBox Evaluate("sum(" & TextBox1 & "," & TextBox2 & "," & TextBox3 & "," & TextBox4 & ")")
End Sub
onmyway
02-03-2015, 07:39 AM
hi guys, thanks for your diligence! :)
Do you perhaps want to look at this challenge: Add multiple filtered row data to ListBox
:beg:
hi guys, thanks for your diligence! :)
Do you perhaps want to look at this challenge: Add multiple filtered row data to ListBox
:beg:
If that was supposed to be a link, I think it failed.
Not well tested, but this seems to work, blocking non-numeric entries and using a Class instance for each of the five 'child' textboxes. Please test in a junk/throwaway copy of your workbook...
In the Userform's Module:
Option Explicit
'// You could use a collection, but since how many controls we have seems unlikely to be a //
'// mystery, I don't know anything against sizing an array. //
Private cAT(1 To 5) As clsAssociatedTextboxes
Private Sub UserForm_Initialize()
Dim ctrl As MSForms.Control
Dim n As Long
n = 0 '<--- just for clarity in my pea brain...
For Each ctrl In Me.Controls
Select Case ctrl.Name
Case "txtStockSOS1", "txtStockReceive1", "txtStockLost1", "txtStockBroken1", "txtStockWorn1"
n = n + 1
Set cAT(n) = New clsAssociatedTextboxes
Set cAT(n).Parent = Me
Set cAT(n).ThisTextbox = ctrl
Case Else
' do nothing
End Select
Next
End Sub
In a Class named clsAssociatedTextboxes:
Option Explicit
Private ParentForm As Object
Private WithEvents TB As MSForms.TextBox
Private LastPosition1 As Long
Public Property Set Parent(p As Object)
Set ParentForm = p
End Property
Public Property Get Parent() As Object
Set Parent = ParentForm
End Property
Public Property Set ThisTextbox(tt As MSForms.TextBox)
Set TB = tt
End Property
Public Property Get ThisTextbox() As MSForms.TextBox
Set ThisTextbox = TB
End Property
Private Sub TB_Change()
Static LastText As String
Static SecondTime As Boolean
' Credit to Rick Rothstein at:
' http://www.mrexcel.com/forum/excel-questions/831878-userform-check-textboxes-numerical-values.html
'// In gist - eliminate non-numerical values from being entered, allow a '+' or '-' sign at //
'// the start and allow one decimal point. //
With ThisTextbox
If Not SecondTime Then
If .Text Like "*[!0-9.+-]*" Or .Text Like "*.*.*" Or .Text Like "?*[+-]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition1
Else
LastText = .Text
LastPosition1 = .SelStart
End If
End If
End With
SecondTime = False
'// After disallowing non-numeric values, update the control. CDbl() appears necessary to //
'// force adding (vs. concatenation). //
Parent.Controls("txtStockEOS1").Value = _
CDbl(IIf(Not Parent.Controls("txtStockSOS1").Value = vbNullString, Parent.Controls("txtStockSOS1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockReceive1").Value = vbNullString, Parent.Controls("txtStockReceive1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockLost1").Value = vbNullString, Parent.Controls("txtStockLost1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockBroken1").Value = vbNullString, Parent.Controls("txtStockBroken1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockWorn1").Value = vbNullString, Parent.Controls("txtStockWorn1").Value, 0))
End Sub
Private Sub TB_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'// Record cursor position //
LastPosition1 = TB.SelStart
End Sub
Private Sub TB_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'// Record cursor position //
LastPosition1 = TB.SelStart
End Sub
Hope that helps,
Mark
onmyway
02-03-2015, 10:18 AM
WOW, Mark. Thank you for the excellent response! A great big thumbs-up to you!
FYI: I tried a URL, but got an error: "Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words."
PS: I am still battling with that issue if you'd like to take a look.
Thank you for all your help!
ACK!
Back to bed for an hour, so will check back later. I just realized that typing in the negative/positive will error. No doubt something better than this, but try changing the one part to:
Parent.Controls("txtStockEOS1").Value = _
CDbl(IIf(Not Parent.Controls("txtStockSOS1").Value = vbNullString And Not Parent.Controls("txtStockSOS1").Value = "-" And Not Parent.Controls("txtStockSOS1").Value = "+", Parent.Controls("txtStockSOS1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockReceive1").Value = vbNullString And Not Parent.Controls("txtStockReceive1").Value = "-" And Not Parent.Controls("txtStockReceive1").Value = "+", Parent.Controls("txtStockReceive1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockLost1").Value = vbNullString And Not Parent.Controls("txtStockLost1").Value = "-" And Not Parent.Controls("txtStockLost1").Value = "+", Parent.Controls("txtStockLost1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockBroken1").Value = vbNullString And Not Parent.Controls("txtStockBroken1").Value = "-" And Not Parent.Controls("txtStockBroken1").Value = "+", Parent.Controls("txtStockBroken1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockWorn1").Value = vbNullString And Not Parent.Controls("txtStockWorn1").Value = "-" And Not Parent.Controls("txtStockWorn1").Value = "+", Parent.Controls("txtStockWorn1").Value, 0))
Mark
onmyway
02-03-2015, 10:30 AM
noted:)
Paul_Hossler
02-03-2015, 10:43 AM
Hi Paul, thank you for the effort! I have decided to go with snb's method.
No problem ... less typing
1. You still need to add the calculation to the event handler for each textbox
2. Be aware that Val() will return 0 and not give you a message if the string you entered is not convertable
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.