Solved: Keep Form Open When Using End Command

[Belch]

Hi all,

This is one of those simple things I should know but can't seem to get!

I have a userform and when the user fills it in and clicks OK it checks that various fields meet certain requirements.
If one or more of the fields fail validation a Msgbox appears telling the user what was wrong. I then have the "End" command to stop code execution.

However when running this and validation fails, the message appears to the user and then the form is unloaded.
How can I get the message to appear but then leave the form open (without clearing the fields) so the user can correct their mistake and click OK again.

Thanks,

[lucas]

can we see your code?

[Belch]

Below is one of the several validation tests, that is in the Click event for the OK button on the form. All the validation tests are similar.


If Len(txtLogNumber.Value) <= 0 Then
MsgBox "You have not entered a log number"
End
End If

At the very end of the Click event code is the line
formMain.Hide
But I would have thought using the "End" command stops it ever reaching this.

Let me know if you need more info.

[lucas]

This might work:
Private Sub CommandButton1_Click()
If Len(txtLogNumber.Value) <= 0 Then
MsgBox "You have not entered a log number"
ElseIf txtLogNumber.Value > 0 Then
formMain.Hide
End If
End Sub

[Belch]

Aha, got it, I knew it was simple!

All I have done is replaced the "End" command with "Exit Sub".
I think the problem is that "End" unloads everything from memory whereas exiting the Sub doesn't.

[lucas]

Why are you still using End or Exit sub....you don't really want to exit yet?

[Belch]

I'm not sure how else I could keep the form loaded and let the user edit a field after they have initiated the Click event by clicking the OK button.

It seems to work - keeps the form loaded and allows the user to edit it and click the OK button again.

[lucas]

Hi Neil,
there is no End or exit sub in this and it does exactly what you said...I'm confused....btw you should unload the userform instead of hiding...it will stay in memory.


Private Sub CommandButton1_Click()
If Len(txtLogNumber.Value) <= 0 Then
MsgBox "You have not entered a log number"
ElseIf txtLogNumber.Value > 0 Then
unload me
End If
End Sub

If we are just misunderstanding each other let me know...post your button code..all of it please.

[lucas]

Oh...I'm sorry..I misunderstood...try setting your form to modless and they can edit the page with the form showing:
UserForm1.Show vbModeless

[fumei]

Exit Sub seems reasonable to me. Validation checks to see if a value is correct. If it is not, then you want to stop execution of the everything else...so Exit Sub.

The form is still there. I would also suggest with validation that you put focus back to the offending control.

Also, I would point out that:If Len(txtLogNumber.Value) <= 0 Then
MsgBox "You have not entered a log number"
End
End If would PASS if the the user put in "stick this...", or ANY OTHER input whose length (you are using Len) is greater than 0.

"Hello" would pass.
"Yadda yadda" would pass.

You are ONLY testing the length of the input, NOT the input itself.

Perhaps also use IsNumeric to check if the input is....well, numeric?

[Belch]

For now all I wanted to check was that something had been entered into the text boxes, I understand that Len > 0 etc only checks if there is something there.

I didn't know there was an IsNumeric function, might come in handy, ta.

[fumei]

If you are checking for anything then, If txtLogNumber = "" Then will do as well. No need for Len.