View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Marcolino Marcolino is offline
external usenet poster
 
Posts: 8
Default Data validation in Userform sub

If a user enters the wrong info into a textbox control, how do I program the
userform to display an error message and return the user to the textbox to
enter the correct info?

I've tried using the Exit event with a MsgBox inside a Do While...Loop, but
either the MsgBox keeps looping, or the focus switches to the next control,
even if I use the SetFocus method:

Private Sub ControlNumb_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Do While IsNumeric(NewResEntryForm.ControlNumb.Value) = False _
Or Len(NewResEntryForm.ControlNumb.Value) < 5

response = MsgBox("Please enter a valid control number", vbOKOnly)

If response = vbOK Then
NewResEntryForm.ControlNumb.SetFocus
Exit Sub
End If

Loop

End Sub

Please help, thanks.