MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional ok


Posted by J Tompson on August 16, 2001 2:06 AM

Hi,

I have a user form with 5 textboxes on it. I would like to ensure that the user places siomething in each box before they can click on ok. How can i do this?

Thanks in advance,

John


Posted by Simon James-Morse on August 16, 2001 3:16 AM

One way would be to create a validation cell with 5 concatenated IF() commands, eg

=IF(ISTEXT(cell1),"OK","no")&IF(ISTEXT(cell2),"OK","no")&..... etc

For a valid entry, this would produce OKOKOKOKOK which could be validated.

A more sophisticated routine could also extract which cell was omitted.

You can also hide the validating cell to avoid unnecessary screen clutter!

Simon

Posted by J Tompson on August 16, 2001 3:33 AM

I'm sorry but i don't understand

Hi,

I'm reasonibly new to this VBA. I'm not sure that i understand how i can force users to place something in each textbox eg protect it untill the users filled in the form. Could you please explain in Begginer language. Thanks

John

Posted by Ian on August 16, 2001 4:19 AM

re:I'm sorry but i don't understand


If Textbox1.Text = "" Then
Beep
MsgBox "You must enter ALL information in all the fields", vbOKOnly, "Incorrect Application"
Textbox1.SetFocus
Exit Sub
ElseIf TextBox2.Text = "" Then
Beep
MsgBox "You must enter ALL information in all the fields", vbOKOnly, "Incorrect Application"
TextBox2.SetFocus
Exit Sub
ElseIf Textbox3.Text = "" Then
Beep
MsgBox "You must enter ALL information in all the fields", vbOKOnly, "Incorrect Application"
Textbox3.SetFocus
Exit Sub
ElseIf TextBox4.Text = "" Then
Beep
MsgBox "You must enter ALL information in all the fields", vbOKOnly, "Incorrect Application"
TextBox4.SetFocus
Exit Sub
ElseIf TextBox5.Text = "" Then
Beep
MsgBox "You must enter ALL information in all the fields", vbOKOnly, "Incorrect Application"
TextBox5.SetFocus
Exit Sub
Else
End If

not too clever at VB myself but this worked for me, you could change the messages to something like "you have not enetered information in the name field"

any help

Ian

Posted by J Tompson on August 16, 2001 4:33 AM

Thank you.

Thats a great help.

John