Dialog Window.docx
Below is my vba code that i use to calculate a word table. Attached is the Dialog box that the end user use to enter their figures. The code below works ONLY if all the figures entered are positive numbers. However, I do not know if the user will enter negative or positive numbers therefore, I cannot specifically code a field in the dialog box. So, if a user enters a negative code, I would like the dialog box to display the number like (13.00) and have the calculation subtract that number.
ValidateNumber Me.NameOfField will not allow user to insert (13.00) How do i fix this and have to total correctly calculate the numbers?
I hope my explanation is clear.


This is my current Code
Private Sub tbamt1_Change()
ValidateNumber Me.tbamt1
AddTextBox
End Sub
Private Sub tbamt2_Change()
ValidateNumber Me.tbamt2
AddTextBox
End Sub
Private Sub tbamt3_Change()
ValidateNumber Me.tbamt3
AddTextBox
End Sub
Private Sub tbamt4_Change()


ValidateNumber Me.tbamt4
AddTextBox
End Sub
Private Sub tbamt5_Change()
ValidateNumber Me.tbamt5
AddTextBox
End Sub
Private Sub tbamt6_Change()
ValidateNumber Me.tbamt6
AddTextBox
End Sub
Sub ValidateNumber(TextBox)
Dim sTxt As String
sTxt = TextBox.Text
If sTxt = "" Then Exit Sub
If IsNumeric(sTxt) Then
If InStr(sTxt, ".") > 0 Then
If Len(sTxt) - InStr(sTxt, ".") > 2 Then
TextBox.Text = Mid(sTxt, 1, Len(sTxt) - 1)
End If
End If
Exit Sub
End If
TextBox.Text = Mid(sTxt, 1, Len(sTxt) - 1)
End Sub
Private Sub AddTextBox()
With Me
sVal11 = .tbamt1.Value
sVal12 = .tbamt2.Value
sVal13 = .tbamt3.Value
sVal14 = .tbamt4.Value
sVal15 = .tbamt5.Value
sVal16 = .tbamt6.Value
If sVal11 = "" Then sVal11 = 0
If sVal12 = "" Then sVal12 = 0
If sVal13 = "" Then sVal13 = 0
If sVal14 = "" Then sVal14 = 0
If sVal15 = "" Then sVal15 = 0
If sVal16 = "" Then sVal16 = 0
AMOUNTDUE = CDbl(sVal11) + CDbl(sVal12) + CDbl(sVal13) + CDbl(sVal14) _
+ CDbl(sVal15) + CDbl(sVal16)
.tbgtotal = Format(AMOUNTDUE, "$#,##0.00")
End With
End Sub

This works for me. I used the Tag property set to "AddMe" on any controls I want to be in the total.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

Thank you so much for responding. Everything in the code works except it is not totaling up. I get "0" in the total field. What am i missing?

The attachment is the table used but only has 0 as a total.

Attached Files

DialogWindow.docx (31.6 KB, 10 views)

I can't diagnose things from a picture but I'm guessing you didn't set the tag value on the controls that should be included in the total. For each of those controls, look in the properties window and set the value in the Tag property to AddMe

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

You just lightened a very heavy load.
I completely missed the step in your first post about the "Tag Property".
Once I corrected this on my end, the code works perfectly.
Thank you so much.
Problem solved.

I have another issue related to this same problem.

I have a second and third totals to add to the same table.
So I used the following.

Private Sub tbamt1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbamt1) = True Then SuperAdder
End Sub

Private Sub tbamt_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbamt2) = True Then SuperAdder
End Sub

Private Sub tbamt3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbamt3) = True Then SuperAdder
End Sub

Private Sub tbamt4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbamt4) = True Then SuperAdder
End Sub

Private Sub tbamt5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbamt5) = True Then SuperAdder
End Sub

Private Sub tbamt6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbamt6) = True Then SuperAdder
End Sub

Private Function FormatCell(aCtl As Control) As Boolean
If IsNumeric(aCtl.Value) Then
aCtl.Value = Format(aCtl.Value, "#,##0.00;(#,##0.00);0")
FormatCell = True
ElseIf aCtl = "" Then
aCtl = 0
FormatCell = True
End If
End Function

Private Sub SuperAdder()
Dim aCtl As Control, dblTotal As Double
For Each aCtl In Me.Controls
If aCtl.Tag = "AddMe" Then
If IsNumeric(aCtl) Then dblTotal = dblTotal + aCtl
End If
Next aCtl
Me.tbgtotal = Format(dblTotal, "#,##0.00;(#,##0.00);0")
End Sub



Private Sub tbsubamt1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbsubamt1) = True Then SuperAdd
End Sub
Private Sub tbsubamt2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbsubamt2) = True Then SuperAdd
End Sub
Private Sub tbsubamt3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbsubamt3) = True Then SuperAdd
End Sub
Private Sub tbsubamt4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbsubamt4) = True Then SuperAdd
End Sub
Private Sub SuperAdd()
Dim aCtl As Control, dblTotal As Double
For Each aCtl In Me.Controls
If aCtl.Tag = "Subit" Then
If IsNumeric(aCtl) Then dblTotal = dblTotal + aCtl
End If
Next aCtl
Me.tbsubtot = Format(dblTotal, "$#,##0.00;($#,##0.00);0")
End Sub
Private Sub tbtotalsub_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbtotalsub) = True Then SuperAd
End Sub
Private Sub tbsubtot_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If FormatCell(tbsubtot) = True Then SuperAd
End Sub
Private Sub SuperAd()
Dim aCtl As Control, dblTotal As Double
For Each aCtl In Me.Controls
If aCtl.Tag = "grandt" Then
If IsNumeric(aCtl) Then dblTotal = dblTotal + aCtl
End If
Next aCtl
Me.tbgrantot = Format(dblTotal, "$#,##0.00;($#,##0.00);0")
End Sub



Everything works except the grand total "tbgrantot".
What I am trying to do is sum tbgtotal + tbsubtot = tbgrantot
so I tagged tbgtotal and tbsubtot but nothing shows up in tbgrantot
Help..

This thread can be marked as solved.