How to incorporate MultiSelect in userform

[rob nobel]

1. The following code opens a user form which gives the user a choice to
select 1 or more items (which are sheets). He then enters how many to print.
What I can't work out is how to make the procedure print EACH of the
selections and for the number of copies desired. (selecting the number of
copies will apply to each item selected.)

2. The ' choose an item part doesn't do its thing either as it still prints
if nothing is selected. Works OK if set to single selection, just can't
work it for multiple.

3. ALSO, if its not too difficult, can the AddItem section cause the
userform to show the names of the sheets in the list, bearing in mind that
the names of the sheet may change but not the numbers of the sheet. (sheets
1,2,3,8)

Option Explicit

'Allow only whole numbers
Private Sub TextBox1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57
Case Else
KeyAscii = 0
End Select
End Sub

'Part of Allow only whole numbers procedure to stop Ctrl button press
Private Sub TextBox1_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If Shift = 2 Then KeyCode = 0
End Sub

Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub OKButton_Click()
Dim vNoCopies As String

' choose an item
If ListBox1.ListIndex = -1 Then
MsgBox "Select from the list.", vbInformation
Exit Sub
End If

vNoCopies = TextBox1.Text
If vNoCopies = "" Then
MsgBox "You must enter a number."
Unload Me
ufPrintNo.Show
Exit Sub
End If

Select Case ListBox1.ListIndex
Case 0
Sheet1.PrintOut From:=1, To:=1, Copies:=vNoCopies
Case 1
Sheet2.PrintOut From:=1, To:=1, Copies:=vNoCopies
Case 1
Sheet3.PrintOut From:=1, To:=1, Copies:=vNoCopies
Case 1
Sheet8.PrintOut From:=1, To:=1, Copies:=vNoCopies
End Select

Unload Me
End Sub

Private Sub UserForm_Initialize()
With ufPrintNo.ListBox1
.RowSource = ""
.AddItem "Bank Reconcilliation"
.AddItem "Financial Statement"
.AddItem "Balance Sheet"
.AddItem "Statistics Sheet"
End With
End Sub

 

[rob nobel]

Hi all.
I would dearly like some help with these problems that I posted a few days
ago.
If the questions don't make sense, please advise.

Some more explanation which may assist in helping you understand what I'm
trying to achieve....
The userform ListBox's Multi select property is set to Multi.
There are 4 selections to choose from.
One or any number of those selections can be made in the UserForm to print
the selection.
There is a TextBox to allow the user to enter the number of copies which
should print all of the selections by that number.

THE PROBLEMs:
1. I can't get the procedure to print all of the selections whilst set as
multi select. (It will only print the first item in the list, whether it is
selected or not.)

2. I need it to not print anything if no selection made.

3. Can the AddItem section use the Sheet number but still extract the names
of the sheets somehow so that even if the sheet name changes from "Bank
Reconciliation", etc., to something else, the new name will appear in the
ListBox.

Private Sub UserForm_Initialize()
With ufPrintNo.ListBox1
.RowSource = ""
.AddItem "Bank Reconciliation"
.AddItem "Financial Statement"
.AddItem "Balance Sheet"
.AddItem "Statistics Sheet"
End With
End Sub

 

[Dave Peterson]

This:

Select Case ListBox1.ListIndex
Case 0
Sheet1.PrintOut From:=1, To:=1, Copies:=vNoCopies
Case 1
Sheet2.PrintOut From:=1, To:=1, Copies:=vNoCopies
Case 1
Sheet3.PrintOut From:=1, To:=1, Copies:=vNoCopies
Case 1
Sheet8.PrintOut From:=1, To:=1, Copies:=vNoCopies
End Select

gets replaced with something like:

Option Explicit
Private Sub CommandButton1_Click()

Dim i As Long

Me.Hide
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Worksheets(ListBox1.List(i)).PrintOut preview:=True
End If
Next i
Me.Show

End Sub

And I think I'd just cycle throught the list looking for .selected(i) = true to
see if any are true.

dim FoundOne as boolean

foundone = false
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
FoundOne = true
exit for
End If
Next i

if foundone = false then
'warning message...
end if

============
And

Private Sub UserForm_Initialize()
With ufPrintNo.ListBox1
.RowSource = ""
.AddItem sheet1.name
.AddItem sheet2.name
.AddItem sheet3.name
.AddItem sheet8.name
.MultiSelect = fmMultiSelectMulti
End With
End Sub

 

[rob nobel]

That all looks great Dave.
Thank you so much!
Rob

 

[rob nobel]

Hi Dave, sorry about this but I thought initially I could get this to work.
Humph!
1. What's happening is that the print preview for each selection comes up
which
allows you then to print it (which is not a bad idea), but I would rather
have it directly print from the selections made without any further action
by the user.

2. Also, it only prints 1 copy no matter how many are entered in the
UserForm.

3. Furthermore, if the property of the ListBox is already set to Multi, why
is
this line required? .....
..MultiSelect = fmMultiSelectMulti (Is this just a safeguard?)

Also, (not part of what you gave me)
4. The message in this section does not appear if no selection is made...
If ListBox1.ListIndex = -1 Then
MsgBox "Select from the list.", vbInformation
Exit Sub
End If
(It's definitely ListBox1)
(If a figure has been entered in TextBox1, and no selection is made in
ListBox1 it does absolutely nothing when OK is clicked)

and 5. (If I haven't outstayed my welcome) I can't change the Dim vNoCopies
As String to be as Integer which in reality it should be (I think).
When I do that, the "If vNoCopies = "" " part gets upset and changing that
to 0 or value doesn't help.

The entire code is as follows:

Private Sub OKButton_Click()
Dim vNoCopies As String
If Sheet4.FilterMode Then Sheet4.ShowAllData
If Sheet4.Range("N13") > 0 Then Sheet4.Range("N5:N12") = False

vNoCopies = TextBox1.Text
If ListBox1.ListIndex = -1 Then
MsgBox "Select from the list.", vbInformation
Exit Sub
End If
If vNoCopies = "" Then
MsgBox "You must enter a number."
Exit Sub
End If

' choose an item

Dim i As Long

Me.Hide
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Worksheets(ListBox1.List(i)).PrintOut preview:=True
End If
Next i
Me.Show

Dim FoundOne As Boolean

FoundOne = False
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
FoundOne = True
Exit For
End If
Next i

If FoundOne = False Then
'warning message...
End If


Unload Me
End Sub

Private Sub UserForm_Initialize()
With ufPrintNo.ListBox1
.RowSource = ""
.AddItem Sheet1.Name
.AddItem Sheet2.Name
.AddItem Sheet3.Name
.AddItem Sheet8.Name
.MultiSelect = fmMultiSelectMulti
End With

End Sub

 

[Dave Peterson]

I left the preview in for testing purposes only. (I thought you'd see it and
delete it after you tested and add the copies stuff.)

Worksheets(ListBox1.List(i)).PrintOut preview:=True
will become
Worksheets(ListBox1.List(i)).PrintOut From:=1, To:=1, Copies:=vNoCopies

(or whatever you want.)

Sorry about the confusion.