Print Sheet Array from List Box
 

I am getting hung up with the code below using a sheet array. For some
reason, xl is not recognizing the string. I have added quotes before and
after . . . still not working. The problem area is designated with "=".

Please help,
Stan


Private Sub CommandButton1_Click()
Dim L As Long
Dim zlist As String
Dim zLabel1 As String

zLabel1 = """, """
For L = 0 To SelectBox.ListCount - 1
If SelectBox.Selected(L) Then
alist = SelectBox.List(L)
Sheets(alist).Select
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = Sheets("Front Page").Range("title")
.RightHeader = ""
.LeftFooter = Sheets("Front Page").Range("stamp")
.CenterFooter = ""
.RightFooter = "&T &P of &PAGES"
End With
zlist = zlist & SelectBox.List(L)
If SelectBox.ListIndex < L Then zlist = zlist & zLabel1
End If
Next
zlist = """" & zlist
zlist = zlist & """"
Unload UserForm4

=Sheets(Array(zlist)).Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

Print Sheet Array from List Box
 

Hi,

In the loop, build a comma-separated string -- with NO quote surrounding the
string:
'build string: sheet1,sheet2,...
For .............
zlist= zlist & SelectBox.List(L) & ","
Next
'remove last comma
If right(zlist,1) = "," then zlist = left(zlist,len(zlist)-1)

At this point, the only thing you have is a string. If you do Array(zlist)
you only get an array containing 1 element being the string zlist.
To get an array with each sheet name being an element of the array, use the
split function:
Sheets(Split(zlist,",")).Select

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"StanJ" wrote:

I am getting hung up with the code below using a sheet array. For some
reason, xl is not recognizing the string. I have added quotes before and
after . . . still not working. The problem area is designated with "=".

Please help,
Stan


Private Sub CommandButton1_Click()
Dim L As Long
Dim zlist As String
Dim zLabel1 As String

zLabel1 = """, """
For L = 0 To SelectBox.ListCount - 1
If SelectBox.Selected(L) Then
alist = SelectBox.List(L)
Sheets(alist).Select
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = Sheets("Front Page").Range("title")
.RightHeader = ""
.LeftFooter = Sheets("Front Page").Range("stamp")
.CenterFooter = ""
.RightFooter = "&T &P of &PAGES"
End With
zlist = zlist & SelectBox.List(L)
If SelectBox.ListIndex < L Then zlist = zlist & zLabel1
End If
Next
zlist = """" & zlist
zlist = zlist & """"
Unload UserForm4

=Sheets(Array(zlist)).Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

Print Sheet Array from List Box
 

Thank you. This worked first time through.

"sebastienm" wrote:

Hi,

In the loop, build a comma-separated string -- with NO quote surrounding the
string:
'build string: sheet1,sheet2,...
For .............
zlist= zlist & SelectBox.List(L) & ","
Next
'remove last comma
If right(zlist,1) = "," then zlist = left(zlist,len(zlist)-1)

At this point, the only thing you have is a string. If you do Array(zlist)
you only get an array containing 1 element being the string zlist.
To get an array with each sheet name being an element of the array, use the
split function:
Sheets(Split(zlist,",")).Select

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"StanJ" wrote:

I am getting hung up with the code below using a sheet array. For some
reason, xl is not recognizing the string. I have added quotes before and
after . . . still not working. The problem area is designated with "=".

Please help,
Stan


Private Sub CommandButton1_Click()
Dim L As Long
Dim zlist As String
Dim zLabel1 As String

zLabel1 = """, """
For L = 0 To SelectBox.ListCount - 1
If SelectBox.Selected(L) Then
alist = SelectBox.List(L)
Sheets(alist).Select
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = Sheets("Front Page").Range("title")
.RightHeader = ""
.LeftFooter = Sheets("Front Page").Range("stamp")
.CenterFooter = ""
.RightFooter = "&T &P of &PAGES"
End With
zlist = zlist & SelectBox.List(L)
If SelectBox.ListIndex < L Then zlist = zlist & zLabel1
End If
Next
zlist = """" & zlist
zlist = zlist & """"
Unload UserForm4

=Sheets(Array(zlist)).Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub