Print all sheets

[austenr]

My aim is to hide the sheet "PrintLog" and to record each instance of print to that sheet. The code I want to use is below but am having a problem with testing if the sheet has been set to print. The first line of the IF statement is my hang up. Can anyone help? Thanks.

Option Explicit
Sub PrintLogAllSheets()
    Dim ws As Worksheet, LastRow As Long, PrintLog As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
If ws.PrintOut = "True" Then
           Set PrintLog = Worksheets("PrintLog")
           LastRow = PrintLog.Range("A65536").End(xlUp).Row + 1
           With PrintLog
                .Cells(LastRow, 1).Value = Now()
                .Cells(LastRow, 2).Value = Application.UserName
                .Cells(LastRow, 3).Value = ActiveSheet.Name
           End With
    Next ws
    Application.ScreenUpdating = True
End Sub

[Norie]

Why are you testing if the PrintOut method is equal to the string "True"?

As far as I know it doesn't return a value.

What do you mean by the sheet has been set to print?

[MWE]

My aim is to hide the sheet "PrintLog" and to record each instance of print to that sheet. The code I want to use is below but am having a problem with testing if the sheet has been set to print. The first line of the IF statement is my hang up. Can anyone help? Thanks.

Option Explicit
Sub PrintLogAllSheets()
    Dim ws As Worksheet, LastRow As Long, PrintLog As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
If ws.PrintOut = "True" Then
           Set PrintLog = Worksheets("PrintLog")
           LastRow = PrintLog.Range("A65536").End(xlUp).Row + 1
           With PrintLog
                .Cells(LastRow, 1).Value = Now()
                .Cells(LastRow, 2).Value = Application.UserName
                .Cells(LastRow, 3).Value = ActiveSheet.Name
           End With
    Next ws
    Application.ScreenUpdating = True
End Sub

I see a number of problems:
first, there is no End If for the "If ws.Printout ..." statement

second, I am unfamiliar with what ws.printout = "True" means. The PrintOut method is not a boolean (at least not in Excel 2K)

third, I assume that you wish to write the name of the worksheet just printed to the log sheet. You should use ws.name, not activesheet.name

fourth, I do not see any logic to bypass the log sheet (unless that is wrapped up in what you are trying to do with ws.Printout)

[austenr]

What I want to do is write the information to the sheet PrintLog if the user selects sheet1, sheet2 or sheet 3 to print or any combination of the three. And you are right there is not a way to bypass it.

[mdmackillop]

Maybe a partial solution
Unfortunately File/Print records in the log, even if you cancel the printing.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
PrintLogAllSheets
End Sub
Sub PrintLogAllSheets()
Dim ws As Worksheet, LastRow As Long, PrintLog As Worksheet
Application.ScreenUpdating = False
For Each ws In Windows(1).SelectedSheets
Set PrintLog = Worksheets("PrintLog")
LastRow = PrintLog.Range("A65536").End(xlUp).Row + 1
With PrintLog
.Cells(LastRow, 1).Value = Now()
.Cells(LastRow, 2).Value = Application.UserName
.Cells(LastRow, 3).Value = ws.Name
End With
Next ws
Application.ScreenUpdating = True
End Sub

[austenr]

Thanks MD...Is it possible to do a check for Cancel before print?

[mdmackillop]

I haven't worked out how to do that. It doesn't sound impossible, but I don't know much about getting responses from built-in dialog buttons (if that is the way to go)

[mdmackillop]

Hi Austen,
You could try using the BuiltInProperties Last Printed info to confirm actual printing

With PrintLog
            .Cells(LastRow, 1).Value = Now()
            .Cells(LastRow, 2).Value = Application.UserName
            .Cells(LastRow, 3).Value = ws.Name
            .Cells(LastRow, 4).Value = ActiveWorkbook.BuiltinDocumentProperties(10).Value
        End With

It will require a bit of manipulation or comparison with Column 1 values to confirm if a printout actually occurred.

[austenr]

Thanks..Will give it a go.