unload userform from within one of that form's beforeupdate events?

  • Thread starter Thread starter Andrew H
  • Start date Start date
A

Andrew H

I'm still pretty much a novice excel programmer; I've been stumped
with the following question for a day, and I haven't seen in addressed
in the groups. Please forgive me if it has.

I've got a userform with a control called "TextBoxNoOfFilters". In a
simplified version of the problem, I need to make sure that a number
is entered, and that that number is NOT 2. If the user does enter 2,
I'd like to immediately indicate this elsewhere in the workbook (with
textboxes) and then exit the userform.

When I call the Unload Userform command, it works, but the unload
process seems to call the BeforeUpdate procedure again. Therefore, if
the user enters 2 and confirms it, the userform disappears and then
they get another msgbox asking them to confirm their entry.

I can't figure out why the unload calls the beforeupdate again, or how
to avoid that re-call.

A sketch of the code follows. Any thoughts would be most appreciated.

Thanks,
Andrew H.


Private Sub TextBoxNoOfFilters_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)

If IsNumeric(TextBoxNoOfFilters) Then
If TextBoxNoOfFilters = 2 Then

msg = "Are you sure you want to enter 2?"
response = MsgBox(prompt:=msg, _
Buttons:=vbQuestion + vbYesNo, _
Title:="Confirm 2 Filters")

If response = vbYes Then
msg = "New warning message goes here"
MsgBox prompt:=msg, Buttons:=vbExclamation
'call a sub to put some text boxes on the worksheets
Unload Userform
End If

End if
Else
msg = "Please enter a number"
MsgBox prompt:=msg, Buttons:=vbExclamation
Cancel = True
TextBoxNoOfFilters = TextBoxNoOfFilters.BoundValue
End Sub
 
Try this: (use it as i have done it. The message box
prompts are not required). Do not use Unload, use End.
Unload is used if the userform is a second form. It
unloads the second form to show the main form but even
still, it is best to use the userform.hide instead.
The "End" statement unloads all forms and frees up memory.


'Command button named cmdClose
sub cmdClose_Click()
End
end sub


Private Sub TextBoxNoOfFilters()
dim response
dim msg
If IsNumeric(TextBoxNoOfFilters.text) Then
'Yes, it's a number.
If TextBoxNoOfFilters.text = 2 Then
'Yes it's 2.
msg = "Are you sure you want to enter 2?"
response = MsgBox(msg,vbQuestion + vbYesNo, _
"Confirm 2 Filters")

If response = vbYes Then
msg = "New warning message goes here"
MsgBox msg, vbExclamation
'call a sub to put some text boxes on the
worksheets
cmdClose_Click
End If
End if
else

msg = "Please enter a number value only."

MsgBox msg, vbExclamation
End Sub


regards

Mark
See some of my handiwork at
http://www.geocities.com/excelmarksway
-----Original Message-----
I'm still pretty much a novice excel programmer; I've been stumped
with the following question for a day, and I haven't seen in addressed
in the groups. Please forgive me if it has.

I've got a userform with a control
Click to expand...
called "TextBoxNoOfFilters". In a
 
Back
Top