PDA

View Full Version : Solved: Print all sheets



austenr
06-22-2005, 12:06 PM
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
06-22-2005, 12:44 PM
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
06-22-2005, 12:53 PM
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
06-22-2005, 12:55 PM
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
06-22-2005, 01:02 PM
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
06-22-2005, 08:53 PM
Thanks MD...Is it possible to do a check for Cancel before print?

mdmackillop
06-23-2005, 12:36 AM
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
06-23-2005, 12:24 PM
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
06-23-2005, 12:25 PM
Thanks..Will give it a go.