Calling Forms from Forms - Exit problems

[Stuart]

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.

 

[Bob Phillips]

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]

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.

 

[Stuart]

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.