Check UserForm Values Before Saving

[MBlake]

Hi,
Prio to saving UserForm values to a worksheet I want to verify that a user
has completed all sections on the form. I have written the following code
that traps the missing entries and displays a dialog box. Unfortunately the
dialog box then closes and rather than SetFocus the data is transferred to
the database worksheet. How can I stop the code to force the user to
complete the missing entry and click the 'Submit' buttton?

Thanks for any help,
Mickey
==========================================================================
If ComboBoxPIN.Text = "" Then MsgBox "You Must Enter a PIN Number."
ComboBoxPIN.SetFocus
ElseIf Me.ComboBoxDate.Value = "" Then
MsgBox "You Must Enter a Date."
ComboBoxDate.SetFocus
ElseIf ComboBoxArea.Text = "" Then
MsgBox "You Must Enter a Location for this activity."
ComboBoxArea.SetFocus
ElseIf ComboBoxActivity.Text = "" Then
MsgBox "You Must Enter an Activity."
ComboBoxActivity.SetFocus
ElseIf ComboBoxOffence.Text = "" Then
MsgBox "You Must Enter an Offence."
ComboBoxOffence.SetFocus
ElseIf TextBox11.Text = "" Then
MsgBox "You Must Enter a Reference Number."
TextBox11.SetFocus
Exit Sub
End If

<following code then copies the userform values to the database worksheet>

 

[Bob Phillips]

What happens when that sub is exited, does it still unload?

 

[Tom Ogilvy]

You have to add an exit sub in each part of your if . . . elseif construct.

 

[Darrin Henshaw]

What seems to be happening is that if there is a value missing, the
userform is giving focus to the correct control, but then following
through with the code to copy the values to the database worksheet. You
might try adding the "Exit Sub" to all your ElseIf statements, that
should bypass all the other code in the proc, and still show the
userform. Also, is your userform hidden, ie Userform.Hide, before or
after this code. If it is before, then you need to show it again. Or you
could use a GoTo statement to do the same thing.

 

[MBlake]

Excellent help, many thanks for the replies. I have amended the code to
include an Exit Sub after each check, all works fine now.

Regards,
Mickey
=============================================
If ComboBoxPIN.Text = "" Then
MsgBox "You Must Enter a PIN Number."
ComboBoxPIN.SetFocus
Exit Sub
ElseIf ComboBoxDate.Value = "" Then
MsgBox "You Must Enter a Date."
ComboBoxDate.SetFocus
Exit Sub

etc ..........

 

[Guest]

Hi Mickey,
My suggestion will be to use a routine such as follows behind the 'Submit'
button, which I have here assumed is called butSubmit.

Sub butSubmit_Click()

Dim sMsg$

If ComboBoxPIN.Text = "" Then
sMsg = sMsg & "You Must Enter a PIN Number." & vbCrLf
End If

If Me.ComboBoxDate.Value = "" Then
sMsg = sMsg & "You Must Enter a Date." & vbCrLf
End If

< similar other checks>

If sMsg <> "" Then
MsgBox sMsg
Exit Sub
End If

<following code then copies the userform values to the database worksheet>

'This will unload the form
Unload Me

End Sub

Here the advantage is that a dialog box points out all the problems with the
data together. If the user does not fix all the problems, the submit button
does not exit from the form and does not save to the database sheet.

Alok Joshi

 

[MBlake]

Hi Alok,
I will give your code a go, it looks neater.

Thanks,
Mickey

 

[MBlake]

Hi Alok,
Your code works well and produces one MsgBox, however is there any way to
then SetFocus to the first missing entry?. In the code I was using the
SetFocus shelps the user. I guess I could default the SetFocu by adding
ComboBoxPIN.SetFocus as in

If sMsg <> "" Then
MsgBox sMsg
Exit Sub
ComboBoxPIN.SetFocus
End If

Best Wishes,
Mickey

 

[Guest]

Hi Mickey,
How about setting focus within each
if statement like this

 

[Guest]

Hi Mickey,

Sorry for the earlier post - it was incomplete - I mistakenly hit a return.

You can use the following line within each if statement but before the line
which start with
sMsg = sMsg & "...."

if sMsg<>"" then xxxx.setFocus

This will ensure focus moves to the first problem control.

Alok Joshi

 

[Guest]

Hi Mickey,

Sorry for the previos post. I sent it incomplete by mistake.

Change each of the If statements like shown below

If ComboBoxPIN.Text = "" Then
if sMsg="" then ComboBoxPIN.SetFocus
sMsg = sMsg & "You Must Enter a PIN Number." & vbCrLf
End If

This will make the focus go the first problem control.

Alok Joshi

 

[MBlake]

Hi Alok,
Thanks for your help which has been invaluable. One final query on this
point, I need to check that a TextBox does not have 0 entered. I have tried
running the line twice using different parameters but that fails. Do you
know if I can do this? I have tried ("" Or 0) as a Value and also the
below.

Thanks,
Mickey

If Me.TextBox10.Value <= 1 Then
If sMsg = "" Then TextBox10.SetFocus
sMsg = sMsg & "You Must Enter the Number of Offenders relating to
this entry." & vbCrLf
End If
If Me.TextBox10.Value "" Then
If sMsg = "" Then TextBox10.SetFocus
sMsg = sMsg & "You Must Enter the Number of Offenders relating to
this entry." & vbCrLf
End If

 

[Guest]

Hi Mickey,
The number in the text box has to be greater than 1?

if so try the condition

if val(textbox10.text)<=1 then

this will also take care of non-numeric information - as Val will convert
"a" or "b" to zero.

Alok

 

[MBlake]

Hi Alok,
That worked a treat, I am very grateful to you for your help.

Thanks,
Mickey