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