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