List box to create array

[Nick O]

Hi, I've read through the board and don't see anything that addresses a
problem I'm stuck on. I've embedded a list box that allows multiple
selections in a worksheet. I have a button tied to a macro that executes a
print command, and I'd like to use the selections from the list box to create
a Sheets Array that I can then use in the print macro to print the specified
selections (which equate to individual worksheets in the file). Any help
greatly appreciated!

 

[Bob Phillips]

Dim i As Long

With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then

Worksheets(i).PrintOut
End If
Next i
End With

 

[Nick O]

Ooookay. I see how this would work, but what I failed to mention is that the
printout consists of sheets that are always printed in addition to the sheets
selected, and it all prints as one package (right now I use a Sheets Array
command to specify the all the sheets but was looking to use a list box to
subsitute the optional sheets). So in the end I need to create something
that allows me to combine a fixed set of sheet names with the selected sheets
from the list box and execute a print on the entire package simultaneously.
Does that make sense?

 

[Bob Phillips]

Assuming you have an array of sheets, append to it with

Dim i As Long

With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then

Redim Preserve arySheets(LBound(arySheets) To
UBound(arySheets))
arySheets(UBound(arySheets)) = .List(i)
End If
Next i
End With

Worksheets(arySheets).Printout