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

November 25, 2009 10:03 AM

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

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 it to only write to the sheet ONLY when all the textboxes are filled out after being called by a cmd button - If it not satisifed the blank fields are highlighted in pink.

I also 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

Any VBA gurus - please help

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

posted by complience to Computers & Internet (3 answers total)

It's been a while since I've used VBA so I don't want to mislead you.

However, there is a great discussion group available for exactly these kinds of questions and you will likely get a quick answer there than here: http://groups.google.com/group/microsoft.public.excel.programming/topics
posted by dfriedman at 10:55 AM on November 25, 2009

Dim NineDigits as string
Dim Position as integer

Ninedigits = inputbox("please enter a nine digit number.") ('if you're using a textbox, use ninedigits = textbox.value)
'if they didn't enter a number, close the program. If they did, force it to be nine digits.
if isnumber(ninedigits) = false then
msgbox "Please enter a number."
end
end if
ninedigits = format(ninedigits, "000000000")

'for use with a textbox
'To validate the email find an 'at' sign
position = instr(textbox2.value, "@")
if position = 0 then
msgbox "You need to enter an email address."
end
end if

I can't remember the cell format off the top of my head, but it's easy, either Google 'cells select format color' or record it and use the code.
posted by A Terrible Llama at 1:13 PM on November 25, 2009

This is a bit late but...

I would do the validation exclusively through VBA. No need to color the spreadsheet or anything since you never let the user update the spreadsheet from the form unless the data is valid.

You should have two buttons: OK (to commit) and Cancel (to allow the user to exit without updating data):

Private Sub butCancel_Click()
Unload Me
End Sub

Private Sub butOK_Click()
Dim iPos As Integer

If Len(TextBox1.Text) = 0 Then
MsgBox "Please enter text in field 1", vbExclamation
Exit Sub
ElseIf InStr(TextBox2.Text, "@") = 0 Or InStr(TextBox2.Text, ".") = 0 Then
MsgBox "Please enter a valid email address in field 2", vbExclamation
Exit Sub
ElseIf Len(TextBox3.Text) <> 9 Or Not IsNumeric(TextBox3.Text) Then
MsgBox "Please enter a 9 digit number in field 3", vbExclamation
Exit Sub
End If

' ** code to update the spreadsheet here**

Unload Me

End Sub

Alternatively, you don't need a VBA solution. You could use Conditional Formatting to color cells on the spreadsheet pink when they don't have valid data. Then use Data > Validation to enforce valid data entry. You can restrict field 2 to 100000000 to 999999999 for instance. Not sure about the email though.
posted by NailsTheCat at 6:28 PM on November 26, 2009