Perform Data Validation on textbox

[sipsu]

In a userForm, I have a textbox that I'm trying to perform data validation on. If invalid data is entered, the focus needs to remain on the textbox.
An if..then statement in the afterUpdate event traps the invalid data but the textbox.setFocus method does not keep the focus on the textbox.
Any help would be appreciated.
Thanks.

 

[senators40]

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&quot
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&quot
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, ",", "&quot
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