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