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 SubThe 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 !