How to incorporate MultiSelect in userform

R

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
 
R

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
 
D

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
 
R

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
 
D

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.
 
R

rob nobel

Yes Dave, I knew that but try as I might I couldn't work out where to put
"From:=1, To:=1, Copies:=vNoCopies" without your help, so thanks.

There were a few more questions which are toubling me which you may have
missed?? .....

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...
(In fact the listBox value is never -1 but always 0 if no selection made (0
is obviously the first item in the list box)). Is this because it's a
MultiSelect? I've tried changing the focus on opening the uf but the value
is always 0 when no selection is made and changing the ListIndex = -1 to 0
makes it want to still print the first item in the list. This is that bit
of code...
If ListBox1.ListIndex = -1 Then
MsgBox "Select from the list.", vbInformation
Exit Sub
End If
(It's definitely ListBox1)

and 5. (If I haven't outstayed my welcome) I can't change the
Dim vNoCopies As String
to be
Dim vNoCopies 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.

Rob
 
D

Dave Peterson

#3. Not required. But that's the way I set the multiselect property. (It was
just quicker for me. It also would serve as a reminder when you review the code
and protect yourself from a problem--if you changed it manually.)

#4. Take a look at the help for .listindex when you have multiselect.

Read that last sentence carefully:

(from xl2002 VBA's help)

Note If you use the MultiSelect property to create a ListBox that allows
multiple selections, the Selected property of the ListBox (rather than the
ListIndex property) identifies the selected rows.

The Selected property is an array with the same number of values as the number
of rows in the ListBox. For each row in the list box, Selected is True if the
row is selected and False if it is not. In a ListBox that allows multiple
selections, ListIndex returns the index of the row that has focus, regardless
of whether that row is currently selected.

#5. You may want to be a little more careful with this:
vNoCopies = TextBox1.Text
Textboxes return text <go figure!>

maybe
dim vnocopies as long

if isnumeric(textbox1.text) then
vnocopies = clng(textbox1.text)
else
vnocopies = 1
end if
 
R

rob nobel

Thanks for the info Dave.
1. I did check the multiselect help out initially, but could not understand
what it all meant. But I've re-read it and I've done this to make it
happen. Hope it's OK?
I'm sure there's a better way! (I couldn't get the suggested loop working.)

If ListBox1.Selected(0) = False And _
ListBox1.Selected(1) = False And _
ListBox1.Selected(2) = False And _
ListBox1.Selected(3) = False Then
MsgBox "Select from the list.", vbInformation
Exit Sub
End If

2. >Textboxes return text <go figure!>
I use the following code on all those textboxes when I want to limit input
which has been very useful ....(given to me by Harald Staff)

'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
Private Sub TextBox1_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If Shift = 2 Then KeyCode = 0
End Sub

Rob
 
D

Dave Peterson

I like this instead of listing all of those items:

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

Was this your loop? What didn't work?

And I've stolen, er, borrowed from Harald's routine. It lets you type only
numbers, but still that value is a string--until you do something with it
(cdbl/clng).

It's a lot like typing 1234 and '1234 in a worksheet cell formatted as General.



rob said:
Thanks for the info Dave.
1. I did check the multiselect help out initially, but could not understand
what it all meant. But I've re-read it and I've done this to make it
happen. Hope it's OK?
I'm sure there's a better way! (I couldn't get the suggested loop working.)

If ListBox1.Selected(0) = False And _
ListBox1.Selected(1) = False And _
ListBox1.Selected(2) = False And _
ListBox1.Selected(3) = False Then
MsgBox "Select from the list.", vbInformation
Exit Sub
End If

2. >Textboxes return text <go figure!>
I use the following code on all those textboxes when I want to limit input
which has been very useful ....(given to me by Harald Staff)

'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
Private Sub TextBox1_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If Shift = 2 Then KeyCode = 0
End Sub

Rob
Click to expand...
 
R

rob nobel

I think I'm going to have to ignore your suggestion for the first time. Due
only to my ignorance. I'll need to sit down one day and learn how to
construct loops.
What you suggested I'm sure would be fine, but I have another loop checking
another value in that procedure and if I use your method it throw that into
a spin.
I'm happy to leave what I had as I understand it and it works.

In regard to what didn't work in another loop I tried ...??? It was the
example code in the Helps which I tried to amend to suit but can't remember
what I did now!

Thanks Dave,
Rob
 
D

Dave Peterson

If you have a loop within the loop, then that might be your problem--or maybe
you're using the same index variable and screwing something up.

But glad you got it working ok.



rob said:
I think I'm going to have to ignore your suggestion for the first time. Due
only to my ignorance. I'll need to sit down one day and learn how to
construct loops.
What you suggested I'm sure would be fine, but I have another loop checking
another value in that procedure and if I use your method it throw that into
a spin.
I'm happy to leave what I had as I understand it and it works.

In regard to what didn't work in another loop I tried ...??? It was the
example code in the Helps which I tried to amend to suit but can't remember
what I did now!

Thanks Dave,
Rob
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Print a selected number of sheets 9
Restrict date format for UserForm 2
Preventing need to hit Enter twice in UserForm 1
Reduce code 2
please help.is it possible re-edit macro to convert text like1530-2200 to time format automatically? 1
Need help in printing vba code 0
move item in multiselect listbox 1
Macro Help 2

Top