Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unload userform from within one of that form's beforeupdate events?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
unload userform from within one of that form's beforeupdate events?
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 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I expose the TextBox BeforeUpdate event to my user class? | Excel Discussion (Misc queries) | |||
IeTimer events not working in modeless Userform? | Excel Programming | |||
BeforeUpdate and Cancel | Excel Programming | |||
BeforeUpdate event for Combo box | Excel Programming | |||
UserForm Events | Excel Programming |