I am doing a project which involves collecting H&S data, via a series of
UserForms, and dumping all the information into a worksheet for further
reporting etc.

My current stumbling block is how to make certain fields on the UserForm
mandatory, so that they have to be filled in by the user before they can
close down the form?

Any ideas will be hugely appreciated - it's getting late and I can't sleep
not knowing this!!!
--
Zani
(if I have posted here, I really am stuck!)

Just add a close button to the userform and test in ther. Something like

Private Sub cmdClose_Click()
With TextBox1
If .Text = "" Then
MsgBox "Invalid data"
.SetFocus
Else
Me.Hide
End If
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zani" wrote in message
...
I am doing a project which involves collecting H&S data, via a series of
UserForms, and dumping all the information into a worksheet for further
reporting etc.

My current stumbling block is how to make certain fields on the UserForm
mandatory, so that they have to be filled in by the user before they can
close down the form?

Any ideas will be hugely appreciated - it's getting late and I can't sleep
not knowing this!!!
--
Zani
(if I have posted here, I really am stuck!)

Thanks Bob

Does the trick, why is it always the most simple of things that cause the
most grief!

Thanks again!
--
Zani
(if I have posted here, I really am stuck!)



"Bob Phillips" wrote:

Just add a close button to the userform and test in ther. Something like

Private Sub cmdClose_Click()
With TextBox1
If .Text = "" Then
MsgBox "Invalid data"
.SetFocus
Else
Me.Hide
End If
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zani" wrote in message
...
I am doing a project which involves collecting H&S data, via a series of
UserForms, and dumping all the information into a worksheet for further
reporting etc.

My current stumbling block is how to make certain fields on the UserForm
mandatory, so that they have to be filled in by the user before they can
close down the form?

Any ideas will be hugely appreciated - it's getting late and I can't sleep
not knowing this!!!
--
Zani
(if I have posted here, I really am stuck!)

You can also try to insert Bob's code into forms QueryClose event sub, for
example like this:

------
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If Len(TextBox1) = 0 Then
MsgBox "You have to insert value in TextBox1"
Cancel = True
End If

End Sub
------

This time user is not able to close form before filling Textbox1.

Good luck

MaC



U¿ytkownik "Zani" napisa³ w wiadomo¶ci
...
Thanks Bob

Does the trick, why is it always the most simple of things that cause the
most grief!

Thanks again!
--
Zani
(if I have posted here, I really am stuck!)



"Bob Phillips" wrote:

Just add a close button to the userform and test in ther. Something like

Private Sub cmdClose_Click()
With TextBox1
If .Text = "" Then
MsgBox "Invalid data"
.SetFocus
Else
Me.Hide
End If
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zani" wrote in message
...
I am doing a project which involves collecting H&S data, via a series
of
UserForms, and dumping all the information into a worksheet for
further
reporting etc.

My current stumbling block is how to make certain fields on the
UserForm
mandatory, so that they have to be filled in by the user before they
can
close down the form?

Any ideas will be hugely appreciated - it's getting late and I can't
sleep
not knowing this!!!
--
Zani
(if I have posted here, I really am stuck!)

Thanks! Lots of clever people in here!
--
Zani
(if I have posted here, I really am stuck!)



"MaC" wrote:

You can also try to insert Bob's code into forms QueryClose event sub, for
example like this:

------
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If Len(TextBox1) = 0 Then
MsgBox "You have to insert value in TextBox1"
Cancel = True
End If

End Sub
------

This time user is not able to close form before filling Textbox1.

Good luck

MaC



U¿ytkownik "Zani" napisa³ w wiadomo¶ci
...
Thanks Bob

Does the trick, why is it always the most simple of things that cause the
most grief!

Thanks again!
--
Zani
(if I have posted here, I really am stuck!)



"Bob Phillips" wrote:

Just add a close button to the userform and test in ther. Something like

Private Sub cmdClose_Click()
With TextBox1
If .Text = "" Then
MsgBox "Invalid data"
.SetFocus
Else
Me.Hide
End If
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zani" wrote in message
...
I am doing a project which involves collecting H&S data, via a series
of
UserForms, and dumping all the information into a worksheet for
further
reporting etc.

My current stumbling block is how to make certain fields on the
UserForm
mandatory, so that they have to be filled in by the user before they
can
close down the form?

Any ideas will be hugely appreciated - it's getting late and I can't
sleep
not knowing this!!!
--
Zani
(if I have posted here, I really am stuck!)

I would like to do the same - require certain cells in certain worksheets be
mandatory. Where do I copy/paste these suggestions?
-Steven

"Zani" wrote:

Thanks! Lots of clever people in here!
--
Zani
(if I have posted here, I really am stuck!)



"MaC" wrote:

You can also try to insert Bob's code into forms QueryClose event sub, for
example like this:

------
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If Len(TextBox1) = 0 Then
MsgBox "You have to insert value in TextBox1"
Cancel = True
End If

End Sub
------

This time user is not able to close form before filling Textbox1.

Good luck

MaC



U¿ytkownik "Zani" napisa³ w wiadomo¶ci
...
Thanks Bob

Does the trick, why is it always the most simple of things that cause the
most grief!

Thanks again!
--
Zani
(if I have posted here, I really am stuck!)



"Bob Phillips" wrote:

Just add a close button to the userform and test in ther. Something like

Private Sub cmdClose_Click()
With TextBox1
If .Text = "" Then
MsgBox "Invalid data"
.SetFocus
Else
Me.Hide
End If
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zani" wrote in message
...
I am doing a project which involves collecting H&S data, via a series
of
UserForms, and dumping all the information into a worksheet for
further
reporting etc.

My current stumbling block is how to make certain fields on the
UserForm
mandatory, so that they have to be filled in by the user before they
can
close down the form?

Any ideas will be hugely appreciated - it's getting late and I can't
sleep
not knowing this!!!
--
Zani
(if I have posted here, I really am stuck!)