Results 1 to 2 of 2

Thread: Please help me

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    14

    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

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    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:
    1. Private Sub cmdPrintSheet_Click()
    2. '01/25/2009 - hack
    3. Dim i As Integer
    4. Dim j As Integer
    5. Dim arrSheetName() As String
    6.  
    7.     WkbExisting.Activate
    8.     If chkSaveRunDate.Value = True Then
    9.        WkbExisting.Save
    10.     End If
    11.    
    12.     For i = 0 To lstSheets.ListCount - 1
    13.         If lstSheets.Selected(i) = True Then
    14.             'put all selected sheets into a single array
    15.             'so our consecutive page numbering will work
    16.             ReDim Preserve arrSheetName(j)
    17.             arrSheetName(j) = lstSheets.List(i)
    18.             WkbExisting.Worksheets(arrSheetName(j)).Select
    19.             If lstSheets.List(i) = "Comments" Then
    20.                 ActiveSheet.PageSetup.LeftFooter = vbNullString
    21.                 ActiveSheet.PageSetup.RightFooter = vbNullString
    22.                 If chkCommentsPageNumber.Value = True Then
    23.                    ActiveSheet.PageSetup.CenterFooter = "Page &P Of &N"
    24.                 Else
    25.                    ActiveSheet.PageSetup.CenterFooter = vbNullString
    26.                 End If
    27.             Else
    28.                 'these are the default footers for all sheets EXCEPT Comments
    29.                 If chkLeftFooter.Value = False Then
    30.                    ActiveSheet.PageSetup.LeftFooter = Application.ActiveWorkbook.FullName
    31.                 Else
    32.                    ActiveSheet.PageSetup.LeftFooter = vbNullString
    33.                 End If
    34.                 ActiveSheet.PageSetup.CenterFooter = "Page &P of &N"
    35.                 ActiveSheet.PageSetup.RightFooter = "Run Date: " & txtRunDate.Text
    36.             End If
    37.             j = j + 1
    38.         End If
    39.     Next
    40.    
    41.     If j = 0 Then Exit Sub
    42.    
    43.     WkbExisting.Worksheets(arrSheetName).PrintOut Copies:=1, Collate:=True
    44.     WkbExisting.Worksheets(arrSheetName(0)).Select 'ungroup selected sheets
    45.    
    46. 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
  •  



Click Here to Expand Forum to Full Width