Hi,
There are 3 options for you:
1) Input Box
2) Message Box
3) To insure that a number is submitted to allow for
addition of 2 or more textboxes
1) Input Box
Create your if then statement and then ask them to put the information into a input box as follows
Private Sub TextBox1_Change()
'To allow for negative numbers we have to have an input box and that input box will populate the textbox
If TextBox1.Text = "-" Or TextBox1.Text = "0-" Then
TextBox1 = InputBox("Enter the negative number", "TextBox1"

TextBox1.Text = TextBox1
'If the person does not enter any number into the InputBox a 0 will be entered (or whatever over the 0)
If TextBox1 = "" Then
TextBox1.Text = 0
End If
End If
End sub
2) Message Box
Display a message box - it will clear the information and go back to the original information in the textbox
Private Sub TextBox1_Change()
'To make sure that the person enters a number
If TextBox1 = vbNullString Then Exit Sub
If Not IsNumeric(TextBox1) Then
MsgBox "Sorry, numbers only"
TextBox1 = vbNullString
End If
End Sub
3) Numeric for adding textboxes
If you want to make sure that there are only numbers entered into the textbox (without $ or , or . or text) then do the following. (eventually using the application.sum feature)
Private Sub TextBox1_Change()
'To allow for negative numbers we have to have an input box and that input box will populate the textbox
If TextBox1.Text = "-" Or TextBox1.Text = "0-" Then
TextBox1 = InputBox("Enter the negative number", "TextBox1"

TextBox1.Text = TextBox1
'If the person does not enter any number into the InputBox a 0 will be entered
If TextBox1 = "" Then
TextBox1.Text = 0
End If
End If
'To make sure that the person enters a number
If TextBox1 = vbNullString Then Exit Sub
If Not IsNumeric(TextBox1) Then
MsgBox "Sorry, numbers only"
TextBox1 = vbNullString
End If
'To make sure that there is a number if the textbox is blank
If TextBox1.Text = "" Then
TextBox1.Text = 0
End If
'If they enter trailing negatives ie. 50- in the textbox it
'will make the negative show in front of the number ie. -50
If Right(TextBox1.Text, 1) = "-" Then
TextBox1.Text = -Left(TextBox1.Text, _
Len(TextBox1.Text) - 1)
End If
'If the person enters $, +, or , into the textbox a message will tell them not to enter those characters and will remove them
If Right(TextBox1.Text, 1) = "$" Or _
Right(TextBox1.Text, 1) = "+" Or _
Right(TextBox1.Text, 1) = "," Then
MsgBox "Do not enter $, +, or , in the TextBox"
TextBox1 = Left(TextBox1.Text, _
Len(TextBox1.Text) - 1)
End If
'To remove comma's anywhere in the textbox
If TextBox1.Text Like "*,*" Then
MsgBox "Do not enter , in the TextBox"
TextBox1.Text = Application.WorksheetFunction _
.Substitute(TextBox1.Text, ",", ""

End If
'To remove period's anywhere in the textbox
If TextBox1.Text Like "*.*" Then
MsgBox "Only Whole Numbers should be used - do not use . The value in the textbox has been updated - verify the number is correct"
TextBox1.Text = Application.WorksheetFunction. _
Round(TextBox1.Text, 0)
SendKeys "{RIGHT}"
End If
End Sub
Hope it helps
Jeff
senators40@hotmail.com