Simple VBA calculation

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:

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

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?

Code:

---

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

---

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?

Code:

---

Private Sub txtStockSOS1_Change()
  Me.txtStockEOS1 = val(txtStockSOS1.Value) + val(txtStockReceive1.Value) - val(txtStockLost1.Value) - val(txtStockBroken1.Value) - val(txtStockWorn1.Value)
End Sub

---

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!

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

Code:

---

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

---

Hi Paul, thank you for the effort! I have decided to go with snb's method.

other approaches:

Code:

---

Private Sub UserForm_Click()
    MsgBox Application.Sum(Array(Val(TextBox1), Val(TextBox2), Val(TextBox3), Val(TextBox4)))
End Sub

---

Code:

---

Private Sub UserForm_Click()
    MsgBox Evaluate("sum(" & TextBox1 & "," & TextBox2 & "," & TextBox3 & "," & TextBox4 & ")")
End Sub

---

hi guys, thanks for your diligence! :)

Do you perhaps want to look at this challenge: Add multiple filtered row data to ListBox

:beg:

Quote:

---

Originally Posted by onmyway

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:

Code:

---

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:

Code:

---

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-q...al-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

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:

Code:

---

  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

noted:)

Quote:

---

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