Print Out From Listbox

Topic

New Reply

Dear Guys ,

I made a button for taking print out from list box , its working fine but second time when click print , page head ( list head) changing , please any one help me to fix this

here the code

Code:

Private Sub cmdprint_Click()
Dim printanswer As Variant
     Call ApplicationOff

    Sheets(“Report”).Visible = True
    Sheets(“Report”).Range(“A2:K” & Cells(Rows.Count, 1).End(xlUp).Row).Clear ‘Keyword:ColumnChange
        
        With UserForm1.ListBox4
            Sheets(“Report”).Range(“A2:K” & Cells(Rows.Count, 1).End(xlUp).Row).Resize(.ListCount).Value = .List ‘Keyword:ColumnChange
        End With

    MsgBox “Your Report has been created”
    Sheets(“Report”).Activate
    ActiveSheet.Range(“A2”).Select
    ActiveSheet.Cells.Select
        
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With
            
        With Selection.Font
            .Name = “Times New Roman”
            .Size = 11
        End With
        
    ActiveSheet.PageSetup.CenterHeader = “&””Times New Roman,bold””&22″ & UserForm1.TextBox13.Value & ” ” & UserForm1.ComboBox1.Value & “:” & ” ” & Now
    ActiveSheet.Range(“A1”).Select

    Call ApplicationOn

    printanswer = MsgBox(“Would you like to print this report?”, vbYesNo)
            Select Case printanswer
                    Case vbYes
                        Call PrintReport
                    Case vbNo
                        Sheets(“Report”).Activate
            End Select
            ‘End

End Sub

Print report

Code:

Sub PrintReport() ‘***********PRINT REPORT
     Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintTitleRows = “$1:$1”
            .PrintTitleColumns = “$A:$A”
        End With
    
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = “”
    Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .LeftHeader = “”
            .CenterHeader = _
            “&””Times New Roman,bold””&22Initial Intake OVERDUE: 20 Records Found  2/6/2018 5:02:30 PM”
            .RightHeader = “”
            .LeftFooter = “”
            .CenterFooter = “Page &P of &N”
            .RightFooter = “”
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperLegal
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = 100
            .PrintErrors = xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
            .EvenPage.LeftHeader.Text = “”
            .EvenPage.CenterHeader.Text = “”
            .EvenPage.RightHeader.Text = “”
            .EvenPage.LeftFooter.Text = “”
            .EvenPage.CenterFooter.Text = “”
            .EvenPage.RightFooter.Text = “”
            .FirstPage.LeftHeader.Text = “”
            .FirstPage.CenterHeader.Text = “”
            .FirstPage.RightHeader.Text = “”
            .FirstPage.LeftFooter.Text = “”
            .FirstPage.CenterFooter.Text = “”
            .FirstPage.RightFooter.Text = “”
        End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

file attached

Reply | Quote