excel vba to sum textbox values inside a userform frame

[simora]

I have a Userform with lots of textboxes. I want to SUM only the values of the textboxes inside the specific frame 1, and put the value in a textbox 39 which is outside the frame 1.

[SamT]

Off the top of my head

For Each Ctrl in Me.Controls("Frame1").Controls
On Error Resume Next
Total = Total + CDbl(Ctrl.Value)
Next Ctrl
Me.TextBox39.Value = CStr(Total)

[GTO]

Hi there,

Only lightly tested, and I hope I am remembering to catch the keystrokes correctly. It should allow 0-9 and one Dot in each of the textboxes.

Option Explicit
  
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  RetKeyStroke Me.TextBox1, KeyAscii
End Sub
  
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  UpdateVal
End Sub
  
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  RetKeyStroke Me.TextBox2, KeyAscii
End Sub
  
Private Sub TextBox2_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  UpdateVal
End Sub
  
Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  RetKeyStroke Me.TextBox3, KeyAscii
End Sub
  
Private Sub TextBox3_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  UpdateVal
End Sub
  
Private Sub TextBox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  RetKeyStroke Me.TextBox4, KeyAscii
End Sub
  
Private Sub TextBox4_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  UpdateVal
End Sub
  
Private Sub TextBox5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  RetKeyStroke Me.TextBox5, KeyAscii
End Sub
  
Private Sub TextBox5_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  UpdateVal
End Sub
  
Private Function RetKeyStroke(TBox As MSForms.TextBox, ByVal AsciiKey As MSForms.ReturnInteger)
  
  Select Case AsciiKey
    Case 46 To 57:    If AsciiKey = 46 And Len(TBox.Value) > Len(Replace(TBox.Value, Chr$(46), vbNullString)) Then AsciiKey = 0
    Case Else:        AsciiKey = 0
  End Select
  
End Function
  
Private Function UpdateVal()
Dim Ctl As MSForms.Control
Dim dblCurVal As Double
  
  For Each Ctl In Me.Frame1.Controls
    If TypeName(Ctl) = "TextBox" Then
      If IsNumeric(Ctl.Value) Then
        dblCurVal = dblCurVal + CDbl(Ctl.Value)
      End If
    End If
  Next
  
  Me.TextBox39.Value = dblCurVal
  
End Function

Hope that helps,

Mark

[simora]

Off the top of my head

For Each Ctrl in Me.Controls("Frame1").Controls
On Error Resume Next
Total = Total + CDbl(Ctrl.Value)
Next Ctrl
Me.TextBox39.Value = CStr(Total)

Hi Thanks:
Had to add this bit

If TypeName (Ctrl)= "TextBox" Then

I will post the code tomorrow sometime.

Thanks

[simora]

Mark Thanks:

Did not see your post. & your solution gave me some ideas for another problem.

Thanks.

[snb]

Since controls in a userform have unique names, you know exactly which textboxes reside in frame1.

Private Sub UserForm_Click()
    textbox39.Text = Application.Sum(TextBox1, TextBox2, TextBox3, TextBox4, etc)
End Sub

[mikerickson]

Combining several of the ideas here, you could put this in a class module (named clsGroupedTextBox)

' in class module clsGroupTextBox

Public WithEvents TextBox As MSForms.TextBox
Public GroupType As String

Event Change()
Event KeyPress(KeyAscii As MSForms.ReturnInteger)

Property Get UFParent() As Object
    Set UFParent = TextBox
    On Error Resume Next
    Do
        Set UFParent = UFParent.Parent
    Loop Until Err
    On Error GoTo 0
End Property

Private Sub TextBox_Change()
    Set Me.UFParent.AnyGroupedTextBox = Me
    RaiseEvent Change
End Sub

Private Sub TextBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Set Me.UFParent.AnyGroupedTextBox = Me
    RaiseEvent KeyPress(KeyAscii)
End Sub

And this in the user form's code module

' in userform's code module

Dim GroupedTextBoxes As Collection
Public WithEvents AnyGroupedTextBox As clsGroupedTextBox

Private Sub AnyGroupedTextBox_Change()
    Dim dblTotal As Double
    Dim oneGroupedTextBox As clsGroupedTextBox
    For Each oneGroupedTextBox In GroupedTextBoxes
        dblTotal = dblTotal + Val(oneGroupedTextBox.TextBox.Text)
    Next oneGroupedTextBox
    Me.tbxTotal.Text = dblTotal
End Sub

Private Sub AnyGroupedTextBox_KeyPress(KeyAscii As MSForms.ReturnInteger)
    Dim newString As String
    If AnyGroupedTextBox.GroupType = "Integer" Then
        Rem allow non-negative  integer
        If Not (Chr(KeyAscii) Like "[0-9]") Then
            KeyAscii = 0
        End If
    Else
        Rem allow any number
        With AnyGroupedTextBox.TextBox
            newString = Left(.Text, .SelStart) & Chr(KeyAscii) & Mid(.Text, .SelStart + .SelLength + 1)
        End With
        If Not IsNumeric(newString & "0") Then
            KeyAscii = 0
        End If
    End If
End Sub

Private Sub UserForm_Initialize()
    Dim oneControl As MSForms.Control
    Dim aNBox As clsGroupedTextBox
    
    Set GroupedTextBoxes = New Collection
    
    For Each oneControl In Frame1.Controls
        If TypeName(oneControl) = "TextBox" Then
            Set aNBox = New clsGroupedTextBox
            Set aNBox.TextBox = oneControl
            GroupedTextBoxes.Add Item:=aNBox
        End If
    Next oneControl
    Set aNBox = Nothing
End Sub

The GroupType property can be used as a tag to distinguish what kind of validation to apply or to distinguish between the text boxes of Frame 1 and those of Frame 2 or ... anything a .Tag property could.

[simora]

Hi Guys:
Had to take a break. Thanks for all the ideas & suggestions.
I solved the problem using this code:

On Error Resume Next 
For Each Ctrl In Me.Controls("Frame1").Controls
        If TypeName(Ctrl) = "TextBox" Then
           
            TextBox40.Value = Ctrl.Value
            TextBox41.Value = (Val(TextBox41.Value) * 1) + (Val(TextBox40.Value) * 1)
            TextBox42.Value = (Val(TextBox39.Value) * 1) - (Val(TextBox41.Value) * 1)
        End If

 Next Ctrl

I'm using some of these TextBoxes as checks for other purposes.
( They're hidden in the actual UserForm )
Again Thanks All !