Listbox problem

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

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.
 
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.
 
Stuart,
returned to the calling sub, the variables are not set.
Click to expand...

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 (e-mail address removed)
 
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
 
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.
 
You never show where your variables are declared and it was unclear when
commandbutton1 is pressed to set values to the variables, so I couldn't
hazard a guess.
 
Thanks for persisting with this.

I've changed things to try to copy a similar form
in the same module, which works (sorry to change
things, but I'm trying anything!)

The similar form is named: GetUserPrintOptions.
This is the call from the standard module, and the
subsequent code to set the variables, when code
returns:

GetUserPrintOptions.Show
'get user's print options
With GetUserPrintOptions.ListBox1
If GetUserPrintOptions.ListBox1.Selected(0) = True Then
Global_PrintAllBooks_Sheets = True
End If
If GetUserPrintOptions.ListBox1.Selected(1) = True Then
Global_HideSameCols = True
End If
If GetUserPrintOptions.ListBox1.Selected(2) = True Then
Global_DoNot_HideCols = True
End If
If GetUserPrintOptions.ListBox1.Selected(3) = True Then
Global_PrintZeroPages = True
End If
If GetUserPrintOptions.ListBox1.Selected(4) = True Then
Global_DoNot_PrintZeroPages = True
End If
If GetUserPrintOptions.ListBox1.Selected(5) = True Then
Global_PrintBlankPages = True
End If
If GetUserPrintOptions.ListBox1.Selected(6) = True Then
Global_DoNot_PrintBlankPages = True
End If
End With
Unload GetUserPrintOptions

This is the only code behind that form:

Option Explicit
Private Sub CancelButton_Click()
Unload GetUserPrintOptions
End Sub

Private Sub OKButton_Click()
Me.Hide
End Sub

Private Sub UserForm_Initialize()

'Fill the ListBox
With GetUserPrintOptions.ListBox1
.RowSource = ""
.AddItem "You want to print EVERY Worksheet in EVERY chosen Workbook"
.AddItem "You want to hide a Column(s) before printing, and you want to"
& vbNewLine _
& "hide the same column(s) in every workbook and every
worksheet" & vbNewLine _
& "you wish to print"
.AddItem "You don't wish to hide any columns, in any workbook or
worksheet"
.AddItem "You want to print EVERY page...including pages that total
'0.00'" & vbNewLine _
& "in every workbook and every worksheet you wish to print"
.AddItem "You don't wish to print any pages that total '0.00'" &
vbNewLine & _
"in any workbook or sheet"
.AddItem "You want to print EVERY page...including pages with no totals"
& vbNewLine _
& "in every workbook and every worksheet you wish to print"
.AddItem "You don't wish to print any pages with no totals in any" &
vbNewLine & _
"workbook or sheet"
End With

End Sub

Taking this as a working example, I have amended the originally
posted code so that later in the same routine (from the standard module):

GetUserWkbkPrintOptions.Show
'get user's workbook-specific print options
With GetUserWkbkPrintOptions.ListBox1
If GetUserPrintOptions.ListBox1.Selected(0) = True Then
Wkbk_HideSameCols = True
End If
If GetUserPrintOptions.ListBox1.Selected(1) = True Then
Wkbk_HideDifferentCols = True
End If
If GetUserPrintOptions.ListBox1.Selected(2) = True Then
Wkbk_DoNotHideCols = True
End If
If GetUserPrintOptions.ListBox1.Selected(3) = True Then
Wkbk_PrintAllZeroPages = True
End If
If GetUserPrintOptions.ListBox1.Selected(4) = True Then
Wkbk_PrintSomeZeroPages = True
End If
If GetUserPrintOptions.ListBox1.Selected(5) = True Then
Wkbk_DoNotPrintZeroPages = True
End If
If GetUserPrintOptions.ListBox1.Selected(6) = True Then
Wkbk_PrintAllBlankPages = True
End If
If GetUserPrintOptions.ListBox1.Selected(7) = True Then
Wkbk_PrintSomeBlankPages = True
End If
If GetUserPrintOptions.ListBox1.Selected(8) = True Then
Wkbk_DoNotPrintBlankPages = True
End If
End With
Unload GetUserWkbkPrintOptions

and then the only code behind this form is :

Option Explicit

Private Sub CancelButton_Click()
Unload GetUserWkbkPrintOptions
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 OKButton_Click()

Me.Hide
End Sub

Now I thought I had followed the earlier example faithfully, and that all
would work.........not so.

All appears to work correctly, until the routine returns to the standard
module. When the line " If GetUserPrintOptions.ListBox1.Selected(0) = True
Then"
is stepped through, I'm immediately sent to the line

Private Sub UserForm_Initialize() in the code behind the form
GetUserPrintOptions

......???

All variables are declared in the standard module, at the start of the
routine.

Hope it is ok to have snipped some of this overlong post.

Regards.
 
Back
Top