Sorry for the delay, but I've been trying to work out
why your code works fine, when mine will not return
the values to the routine that calls the form.
Your code correctly returns the selected items to the
Immediate window, whilst mine recognises all items in
the Listbox to be False.
Any ideas, please?
Regards and thanks.
"Tom Ogilvy" wrote in message
...
I suspect the main problem is you named commandbutton1 to OkButton and
forgot to move your code, but just to cover all bases, the following
worked
fine for me:
In a general module, I had this:
Public Wkbk_HideSameCols As Boolean
Public Wkbk_HideDifferentCols As Boolean
Public Wkbk_DoNotHideCols As Boolean
Public Wkbk_PrintAllZeroPages As Boolean
Public Wkbk_PrintSomeZeroPages As Boolean
Public Wkbk_DoNotPrintZeroPages As Boolean
Public Wkbk_PrintAllBlankPages As Boolean
Public Wkbk_PrintSomeBlankPages As Boolean
Public Wkbk_DoNotPrintBlankPages As Boolean
Sub ShowUserform()
GetUserWkbkPrintOptions.Show
Debug.Print "Wkbk_HideSameCols: " & Wkbk_HideSameCols
Debug.Print "Wkbk_HideDifferentCols: " & Wkbk_HideDifferentCols
Debug.Print "Wkbk_DoNotHideCols: " & Wkbk_DoNotHideCols
Debug.Print "Wkbk_PrintAllZeroPages: " & Wkbk_PrintAllZeroPages
Debug.Print "Wkbk_PrintSomeZeroPages: " & Wkbk_PrintSomeZeroPages
Debug.Print "Wkbk_DoNotPrintZeroPages: " & Wkbk_DoNotPrintZeroPages
Debug.Print "Wkbk_PrintAllBlankPages: " & Wkbk_PrintAllBlankPages
Debug.Print "Wkbk_PrintSomeBlankPages: " & Wkbk_PrintSomeBlankPages
Debug.Print "Wkbk_DoNotPrintBlankPages: " & Wkbk_DoNotPrintBlankPages
End Sub
in the userform for useform GetUserWkbkPrintOptions I had this
Private Sub UserForm_Initialize()
'Fill the ListBox
With GetUserWkbkPrintOptions.ListBox1
.RowSource = ""
.AddItem "You want to hide the same Columns in every Worksheet in this
Workbook"
.AddItem "You want to hide different Columns in different Worksheets
in
this Workbook"
.AddItem "You don't want to hide any Columns in this Workbook before
printing"
.AddItem "You want to print '0.00' pages in every Worksheet in this
Workbook"
.AddItem "You want to print '0.00' in some Worksheets in this
Workbook"
.AddItem "You don't want to print any '0.00' pages in this Workbook"
.AddItem "You want to print blank pages in every Worksheet in this
Workbook "
.AddItem "You want to print blank pages in some Worksheets in this
Workbook"
.AddItem "You don't want to print any blank pages in this Workbook"
End With
End Sub
Private Sub OKButton_Click()
'
' if you are going to have a with construct
' then use it
'
With GetUserWkbkPrintOptions.ListBox1
If .Selected(0) = True Then
Wkbk_HideSameCols = True
End If
If .Selected(1) = True Then
Wkbk_HideDifferentCols = True
End If
If .Selected(2) = True Then
Wkbk_DoNotHideCols = True
End If
If .Selected(3) = True Then
Wkbk_PrintAllZeroPages = True
End If
If .Selected(4) = True Then
Wkbk_PrintSomeZeroPages = True
End If
If .Selected(5) = True Then
Wkbk_DoNotPrintZeroPages = True
End If
If .Selected(6) = True Then
Wkbk_PrintAllBlankPages = True
End If
If .Selected(7) = True Then
Wkbk_PrintSomeBlankPages = True
End If
If .Selected(8) = True Then
Wkbk_DoNotPrintBlankPages = True
End If
End With
Me.Hide
End Sub
--
Regards,
Tom Ogilvy
Stuart wrote in message
...
Many thanks, but there must be something fundamental with
forms, that I don't understand
Here is the call from the module:
GetUserWkbkPrintOptions.Show
then in the form, I currently have:
Option Explicit
Private Sub CancelButton_Click()
Unload GetUserWkbkPrintOptions
End Sub
Private Sub OKButton_Click()
Me.Hide
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
'Fill the ListBox
With GetUserWkbkPrintOptions.ListBox1
.RowSource = ""
.AddItem "You want to hide the same Columns in every Worksheet in
this
Workbook"
.AddItem "You want to hide different Columns in different Worksheets
in
this Workbook"
.AddItem "You don't want to hide any Columns in this Workbook before
printing"
.AddItem "You want to print '0.00' pages in every Worksheet in this
Workbook"
.AddItem "You want to print '0.00' in some Worksheets in this
Workbook"
.AddItem "You don't want to print any '0.00' pages in this Workbook"
.AddItem "You want to print blank pages in every Worksheet in this
Workbook"
.AddItem "You want to print blank pages in some Worksheets in this
Workbook"
.AddItem "You don't want to print any blank pages in this Workbook"
End With
End Sub
Private Sub CommandButton1_Click()
With GetUserWkbkPrintOptions.ListBox1
If ListBox1.Selected(0) = True Then
Wkbk_HideSameCols = True
End If
If ListBox1.Selected(1) = True Then
Wkbk_HideDifferentCols = True
End If
If ListBox1.Selected(2) = True Then
Wkbk_DoNotHideCols = True
End If
If ListBox1.Selected(3) = True Then
Wkbk_PrintAllZeroPages = True
End If
If ListBox1.Selected(4) = True Then
Wkbk_PrintSomeZeroPages = True
End If
If ListBox1.Selected(5) = True Then
Wkbk_DoNotPrintZeroPages = True
End If
If ListBox1.Selected(6) = True Then
Wkbk_PrintAllBlankPages = True
End If
If ListBox1.Selected(7) = True Then
Wkbk_PrintSomeBlankPages = True
End If
If .ListBox1.Selected(8) = True Then
Wkbk_DoNotPrintBlankPages = True
End If
End With
Me.Hide
End Sub
Any further help would be much appreciated.
Regards.
"Chip Pearson" wrote in message
...
Stuart,
returned to the calling sub, the variables are not set.
What variables are not set? And where are those variables
declared? If they are declared in the form module, they get
cleared because you are using 'Unload Me'. Instead of unloading,
just use 'Me.Hide'.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Stuart" wrote in message
...
Many thanks, but I've not explained properly because
the same is occurring.
I've moved the code that loads the listbox items into
the code behind the form as follows:
Private Sub CommandButton1_Click()
With GetUserWkbkPrintOptions.ListBox1
If ListBox1.Selected(0) = True Then
Wkbk_HideSameCols = True
End If
If ListBox1.Selected(1) = True Then
Wkbk_HideDifferentCols = True
End If
If ListBox1.Selected(2) = True Then
Wkbk_DoNotHideCols = True
End If
If ListBox1.Selected(3) = True Then
Wkbk_PrintAllZeroPages = True
End If
If ListBox1.Selected(4) = True Then
Wkbk_PrintSomeZeroPages = True
End If
If ListBox1.Selected(5) = True Then
Wkbk_DoNotPrintZeroPages = True
End If
If ListBox1.Selected(6) = True Then
Wkbk_PrintAllBlankPages = True
End If
If ListBox1.Selected(7) = True Then
Wkbk_PrintSomeBlankPages = True
End If
If .ListBox1.Selected(8) = True Then
Wkbk_DoNotPrintBlankPages = True
End If
End With
Unload Me
End Sub
and 'remmed the same code in the calling module.
The options are being highlighted in the Listbox, but when
returned to the calling sub, the variables are not set.
Do I need something for CommandButton1 (ie the left
mouse button)? Is this the root problem?
Regards.
"Orlando Magalhães Filho" wrote in
message
...
Hi Stuart,
Now test this code. It works fine for me.
Private Sub UserForm_Initialize()
'Fill the ListBox
With GetUserWkbkPrintOptions.ListBox1
.RowSource = ""
.AddItem "You want to hide the same Columns"
' etc
' etc
.AddItem "You don't want to print any blank pages"
End With
'GetUserWkbkPrintOptions.Show You don't need this line!!!
End Sub
Private Sub CommandButton1_Click()
With GetUserWkbkPrintOptions.ListBox1
If ListBox1.Selected(0) = True Then
Wkbk_HideSameCols = True
End If
If ListBox1.Selected(1) = True Then
Wkbk_HideSameCols = True
End If
End With
Unload Me
End Sub
--
HTH
---
Orlando Magalhães Filho
(So that you get best and rapid solution and all may benefit
from the
discussion, please reply within the newsgroup, not in email)
"Stuart" escreveu na mensagem
...
From the module, this calls the form:
GetUserWkbkPrintOptions.Show
then in the form:
Private Sub UserForm_Initialize()
'Fill the ListBox
With GetUserWkbkPrintOptions.ListBox1
.RowSource = ""
.AddItem "You want to hide the same Columns"
etc
etc
.AddItem "You don't want to print any blank pages"
End With
GetUserWkbkPrintOptions.Show
End Sub
The form shows and user makes their selection.
Clicking the OKButton hides the form and returns to the
module code:
'get user's workbook-specific print options
With GetUserWkbkPrintOptions.ListBox1
If GetUserPrintOptions.ListBox1.Selected(0) = True Then
Wkbk_HideSameCols = True
End If
etc.
When I step through all appears ok, but when the routine
returns to the above last code, it simply checks each line
for
True or False....it doesn't pick up the Selected items from
the Listbox.
What am I doing wrong, please?
Regards.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.509 / Virus Database: 306 - Release Date:
12/08/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.509 / Virus Database: 306 - Release Date:
12/08/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.509 / Virus Database: 306 - Release Date: 12/08/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.509 / Virus Database: 306 - Release Date: 12/08/2003