Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Forms from Forms - Exit problems
I call a form containing 4 listboxes, Ok and Cancel buttons.
Listbox1 is a straightforward select (or not) option. Listboxes 2-4 each call a different form if they are selected. Each of these sub-forms contain one listbox and thus are straightforward select (or not) options. I believe I have solved an earlier unload issue (thanks), and anyway the following problem occurs even the first time through the routine: If I select any of the Listbox 2-4 options (and hence their sub-options) then when I attempt to exit the original form (with the Ok button) I am sent 'round the houses' again through some of the sub-forms, before I can exit. Should I post what will be fairly lengthy code, or can anyone point me to the likely error, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Forms from Forms - Exit problems
Stuart,
I think it needs the code. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I call a form containing 4 listboxes, Ok and Cancel buttons. Listbox1 is a straightforward select (or not) option. Listboxes 2-4 each call a different form if they are selected. Each of these sub-forms contain one listbox and thus are straightforward select (or not) options. I believe I have solved an earlier unload issue (thanks), and anyway the following problem occurs even the first time through the routine: If I select any of the Listbox 2-4 options (and hence their sub-options) then when I attempt to exit the original form (with the Ok button) I am sent 'round the houses' again through some of the sub-forms, before I can exit. Should I post what will be fairly lengthy code, or can anyone point me to the likely error, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Forms from Forms - Exit problems
Here's the module code (and at this point it is being called
at the 'root', ie not within any If/End If, With/End With, Do/Until construct): With GetUserPrintOptions .Show If .OkButton.Tag = "Selected" Then If .ListBox1.Selected(0) Then Global_PrintAllBooks_Sheets = True End If If .ListBox2.Selected(0) Then HideCols = True With GetUserHideColumnOptions If .ListBox1.Selected(0) = True Then Global_HideSameCols = True End If Unload GetUserHideColumnOptions End With End If If .ListBox3.Selected(0) Then PrintZeroPages = True With GetUserPrintZeroPagesOptions If .ListBox1.Selected(0) = True Then Global_PrintZeroPages = True End If Unload GetUserPrintZeroPagesOptions End With End If If .ListBox4.Selected(0) Then PrintBlankPages = True With GetUserPrintBlankPagesOptions If .ListBox1.Selected(0) = True Then Global_PrintBlankPages = True End If Unload GetUserPrintBlankPagesOptions End With End If ElseIf .Tag = "Cancel" Then 'Cancel Button was pressed so tidy up Unload GetUserPrintBlankPagesOptions Unload GetUserHideColumnOptions Unload GetUserPrintZeroPagesOptions Unload GetUserPrintOptions Exit Sub End If Unload GetUserPrintOptions End With Code in "GetUserPrintOptions" - the principal Form: Option Explicit Private Sub CancelButton_Click() Dim ans As VbMsgBoxResult ans = MsgBox("Are you sure", vbQuestion + vbYesNo, "Close Form") If ans = vbYes Then Me.Tag = "Cancel" Me.Hide End If End Sub Private Sub OKButton_Click() OkButton.Tag = "Selected" CancelButton.Tag = "" GetUserPrintOptions.Hide End Sub Private Sub UserForm_Initialize() 'Fill the ListBoxes With GetUserPrintOptions.ListBox1 .RowSource = "" .AddItem "You want to print EVERY Worksheet in EVERY chosen Workbook" End With With GetUserPrintOptions.ListBox2 .RowSource = "" .AddItem "You will want to hide Column(s)" End With With GetUserPrintOptions.ListBox3 .RowSource = "" .AddItem "You want to include the printing of pages that total '0.00'" End With With GetUserPrintOptions.ListBox4 .RowSource = "" .AddItem "You want to include the printing of pages with no totals" End With End Sub Private Sub ListBox2_Change() Me.Hide GetUserHideColumnOptions.Show Me.Show End Sub Private Sub ListBox3_Change() Me.Hide GetUserPrintZeroPagesOptions.Show Me.Show End Sub Private Sub ListBox4_Change() Me.Hide GetUserPrintBlankPagesOptions.Show Me.Show End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then 'disable the Close button ("X") Cancel = True MsgBox "Please use only the OK or Cancel buttons", vbCritical ElseIf CloseMode = vbFormCode Then Unload Me End If End Sub Code in form "GetUserHideColumnsOptions" called by Listbox2 in the main form: Option Explicit Private Sub CancelButton_Click() CancelButton.Tag = "Selected" OkButton.Tag = "" GetUserHideColumnOptions.Hide End Sub Private Sub OKButton_Click() OkButton.Tag = "Selected" CancelButton.Tag = "" GetUserHideColumnOptions.Hide End Sub Private Sub UserForm_Initialize() 'Fill the ListBox With GetUserHideColumnOptions.ListBox1 .RowSource = "" .AddItem "You want to hide the same Column(s) in EVERY Worksheet in EVERY Workbook" End With End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then 'disable the Close button ("X") Cancel = True MsgBox "Please use only the OK or Cancel buttons", vbCritical ElseIf CloseMode = vbFormCode Then Unload GetUserHideColumnOptions End If End Sub Code in form "GetUserPrintZeroPagesOptions" called by Listbox3 in the main form: Option Explicit Private Sub CancelButton_Click() CancelButton.Tag = "Selected" OkButton.Tag = "" GetUserPrintZeroPagesOptions.Hide End Sub Private Sub OKButton_Click() OkButton.Tag = "Selected" CancelButton.Tag = "" GetUserPrintZeroPagesOptions.Hide End Sub Private Sub UserForm_Initialize() 'Fill the ListBox With GetUserPrintZeroPagesOptions.ListBox1 .RowSource = "" .AddItem "You want to print '0.00' pages in EVERY Worksheet in" & _ "EVERY Workbook" End With End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then 'disable the Close button ("X") Cancel = True MsgBox "Please use only the OK or Cancel buttons", vbCritical ElseIf CloseMode = vbFormCode Then Unload GetUserPrintZeroPagesOptions End If End Sub Code in form "GetUserPrintBlankPagesOptions" called by Listbox4 in the main form: Option Explicit Private Sub CancelButton_Click() CancelButton.Tag = "Selected" OkButton.Tag = "" GetUserPrintBlankPagesOptions.Hide End Sub Private Sub OKButton_Click() OkButton.Tag = "Selected" CancelButton.Tag = "" GetUserPrintBlankPagesOptions.Hide End Sub Private Sub UserForm_Initialize() 'Fill the ListBox With GetUserPrintBlankPagesOptions.ListBox1 .RowSource = "" .AddItem "You want to print Blank Pages in EVERY Worksheet in" & _ "EVERY Workbook" End With End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then 'disable the Close button ("X") Cancel = True MsgBox "Please use only the OK or Cancel buttons", vbCritical ElseIf CloseMode = vbFormCode Then Unload GetUserPrintBlankPagesOptions End If End Sub I hope I've pasted code correctly and clearly. Regards and thanks. "Bob Phillips" wrote in message ... Stuart, I think it needs the code. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I call a form containing 4 listboxes, Ok and Cancel buttons. Listbox1 is a straightforward select (or not) option. Listboxes 2-4 each call a different form if they are selected. Each of these sub-forms contain one listbox and thus are straightforward select (or not) options. I believe I have solved an earlier unload issue (thanks), and anyway the following problem occurs even the first time through the routine: If I select any of the Listbox 2-4 options (and hence their sub-options) then when I attempt to exit the original form (with the Ok button) I am sent 'round the houses' again through some of the sub-forms, before I can exit. Should I post what will be fairly lengthy code, or can anyone point me to the likely error, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Forms from Forms - Exit problems
The posted code actually works better than I thought. It
looks as if some recent changes have improved things, such that only listbox2 in the original form is causing problems. So I seem to be able to select any combination of listboxes 1, 3 and 4, and exit correctly. However, as soon as listbox2 is chosen the problems begin. So what's wrong with the HideColumns code? Regards. "Bob Phillips" wrote in message ... Stuart, I think it needs the code. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I call a form containing 4 listboxes, Ok and Cancel buttons. Listbox1 is a straightforward select (or not) option. Listboxes 2-4 each call a different form if they are selected. Each of these sub-forms contain one listbox and thus are straightforward select (or not) options. I believe I have solved an earlier unload issue (thanks), and anyway the following problem occurs even the first time through the routine: If I select any of the Listbox 2-4 options (and hence their sub-options) then when I attempt to exit the original form (with the Ok button) I am sent 'round the houses' again through some of the sub-forms, before I can exit. Should I post what will be fairly lengthy code, or can anyone point me to the likely error, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forms | Excel Discussion (Misc queries) | |||
How to get userform Cancel button to invoke Exit Sub in calling macro? | New Users to Excel | |||
help - with forms | Excel Discussion (Misc queries) | |||
Forms | Excel Discussion (Misc queries) | |||
VBA forms | Excel Programming |