-
Aug 30th, 2010, 05:47 AM
#1
Thread Starter
New Member
Please help me
Hello,
I want to make a VBA code for printing out sheets that are listed in a ListBox. I've tried the following loop:
For Each ListBoxItem As ListItem In ListBox1.Items
ActiveWorkbook.Sheets(CStr(ListBoxItem)).PrintOut
Next
This however doesn't work, and I don't know why...
Could you help me with this one? Maybe there are multiple faults in the code...
Jeroen
-
Aug 30th, 2010, 06:00 AM
#2
Re: Please help me
This is taken directly from one of my VBA projects. I have a multiselect listbox so they can select more than one sheet if they wish. If they do select more than one sheet, my code is setup to print consecutive page numbers. You may pick and choose from this to suite your own needs. wkbExisting is predeclared As Wookbook
vb Code:
Private Sub cmdPrintSheet_Click()
'01/25/2009 - hack
Dim i As Integer
Dim j As Integer
Dim arrSheetName() As String
WkbExisting.Activate
If chkSaveRunDate.Value = True Then
WkbExisting.Save
End If
For i = 0 To lstSheets.ListCount - 1
If lstSheets.Selected(i) = True Then
'put all selected sheets into a single array
'so our consecutive page numbering will work
ReDim Preserve arrSheetName(j)
arrSheetName(j) = lstSheets.List(i)
WkbExisting.Worksheets(arrSheetName(j)).Select
If lstSheets.List(i) = "Comments" Then
ActiveSheet.PageSetup.LeftFooter = vbNullString
ActiveSheet.PageSetup.RightFooter = vbNullString
If chkCommentsPageNumber.Value = True Then
ActiveSheet.PageSetup.CenterFooter = "Page &P Of &N"
Else
ActiveSheet.PageSetup.CenterFooter = vbNullString
End If
Else
'these are the default footers for all sheets EXCEPT Comments
If chkLeftFooter.Value = False Then
ActiveSheet.PageSetup.LeftFooter = Application.ActiveWorkbook.FullName
Else
ActiveSheet.PageSetup.LeftFooter = vbNullString
End If
ActiveSheet.PageSetup.CenterFooter = "Page &P of &N"
ActiveSheet.PageSetup.RightFooter = "Run Date: " & txtRunDate.Text
End If
j = j + 1
End If
Next
If j = 0 Then Exit Sub
WkbExisting.Worksheets(arrSheetName).PrintOut Copies:=1, Collate:=True
WkbExisting.Worksheets(arrSheetName(0)).Select 'ungroup selected sheets
End Sub
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|