Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List and subtotal selected items, then print separate item list | Excel Worksheet Functions | |||
Array Formulas - Unique List from List with Duplicates | Excel Discussion (Misc queries) | |||
Active cell counting in particular print page (one sheet having different print area) | Excel Worksheet Functions | |||
Why do I get a print error light trying to print an excel sheet ? | Excel Discussion (Misc queries) | |||
Sheet(Array( Won't print as I expect it to | Excel Worksheet Functions |