MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need help with userform textboxes please.


Posted by Bob on December 13, 2000 11:54 AM

I have a userform which has several textboxes. I have the following commands for checking for a blank value in textbox1:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If UserForm1.TextBox1.Value = "" Then
MsgBox "CANNOT LEAVE FIELD BLANK.", vbOKOnly + vbExclamation, "ERROR"
Cancel = True
End If
End Sub

This works fine for pressing tab or mouse-clicking to go to the next text box. But it also gives the error message box if I press
the cancel button on the userform (if I accidently hit the command button for the userform for instance). How can I do this so
that the user must enter something in this text box, unless they press cancel? Any help is greatly appreciated


Posted by Tim Francis-Wright on December 14, 2000 8:16 AM

The following code in the code for the Userform
will do what you want:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If TextBox1.Value = "" And CloseMode = vbFormControlMenu Then
MsgBox "CANNOT LEAVE FIELD BLANK.", vbOKOnly + vbExclamation, "ERROR"
Cancel = True
End If
End Sub

(The CloseMode variable checks how the userform
was closed: here, either with the X box, the
the close command from the right-click menu,
or Alt-F4.)

Good luck!