Need help developing a Excel VBA Macro to Vaildate data entered intoa textbox

[macro rewind]

I am collecting data from users in a Excel spreadsheet. To ensure all
the required fields are filled out, I have created a userform that
loads on opening of the spread sheet.

Currently there are three Textboxes a command button writes the data
to the sheet and highlights in pink any not filledout.

I need a higher level of data vaildation on the two other text boxes.

Textbox2 must contain an email address (checking for hyperlinked
content is probably the easiest way to vaildate for this)

Textbox3 must contain only a 9 digit numerical number

Option Explicit
Dim y As Long
Const pink = 16761855
Private Sub CommandButton1_Click()
Dim EmptyBoxes As Integer
EmptyBoxes = EmptyBoxes + check(TextBox1)
EmptyBoxes = EmptyBoxes + check(TextBox2)
EmptyBoxes = EmptyBoxes + check(TextBox3)
If EmptyBoxes = 0 Then
ActiveWorkbook.Save
Unload Me
End If
End Sub
Function check(tb As Control)
Dim res As Integer
res = 0
If tb.Text = vbNullString Then
res = 1
tb.BackColor = pink
Else
Cells(y, CLng(Right(tb.Name, 1))) = tb
End If
check = res
End Function
Private Sub UserForm_Activate()
y = [A1].End(xlDown).Row + 1
End Sub

 

[Patrick Molloy]

Option Explicit
Dim y As Long
Const pink = 16761855
Private Sub CommandButton1_Click()
Dim EmptyBoxes As Integer
IF check1 AND check2 AND check3 Then
ActiveWorkbook.Save
Unload Me
else
msgbox "Check text boxes"

End If
End Sub
Function check1 as Boolean
Dim res As Integer
res = 0
If textbox1.Text = vbNullString Then

textbox1.BackColor = pink
Else
Cells(y, CLng(Right(textbox1.Name, 1))) = textbox1
check1 = True
End If
End Function
Private Sub UserForm_Activate()
y = [A1].End(xlDown).Row + 1
End Sub
function check2 as boolean
'check textbox2 has a 9 digit number
if isnumber(textbox2.text) then
if len(textbox2.text)=9 then
check2 = True
end if
end if
if not check2 then
textbox2.BackColor = pink
end if
end function
function check3 as boolean
'check textbox3 is an address ???
If textbox3.Text = vbNullString Then
textbox3.BackColor = pink
Else
check3 = True
End If


end function

 

[macro rewind]

Tried this but its giving me an error when I run the command button on
the form.

compile error:

Sub or Function not defined

The Debugger than highlights "IsNumber" in grey and Function Check2
with Yellow

Function check2() As Boolean
'check textbox2 has a 9 digit number
If IsNumber

 

[macro rewind]

Ah should have been Isnumeric

 

[Gord Dibben]

Or maybe as Dave Peterson points out

application.isnumber(cell.value)

Which is much more stringent than VBA's
isnumeric(cell.value)

The text string '1234 will return False with excel's =isnumber() worksheet
function.


Gord Dibben MS Excel MVP

 

[macro rewind]

I dont understand

1234 is a number in my world why would 'isnumeric' return it as false?

More to the point when I use 'isnumber(textbox2.text)' it giving me a
compile error:Sub or Function not defined
isnumeric isn't

another error im getting is, if you delete the data thats written to
the sheet by the form it won't let you add anymore data (even if you
close and restart excel)

Run-time error '1004':
Application-defined or object-defined error

And the Debugger highlights my line of code that should write the
textbox value into the Sheet

Cells(y, CLng(Right(tb.Name, 1))) = tb

think its getting lost as to what row to add the datainto after the
deletion