to print or not to print...

[david s.]

i have a worksheet with five pages in it. typically only three of those pages
will be used. i want to scan a particular range on the optional pages and
determine if there is data, and if not, do not print them.

 

[JLGWhiz]

Offhand, I would think that setting the print range to only the part of the
sheet with data in in would do the trick. But I am not sure what your
definition of "pages" is. Normally, Excel will only print the used range for
any worksheet, unless otherwise specified in the page setup/print settings.

 

[FSt1]

hi
add this in somewhere
Dim cnt As Long
For Each cell In Sheets("sheet2").UsedRange
If Not IsEmpty(cell) Then
cnt = cnt + 1
End If
Next
If cnt = 0 Then
If MsgBox("There is no data ", vbYesNo, "Infomation") = vbNo Then
Exit Sub
End If
End If

regards
FSt1

 

[david s.]

actually, calling it a "FORM" would be more acurate. and this "FORM" has
certain information on it, and formulas too. but unless data is entered into
the cells designated for the user to enter data, there is no need to print
these optional pages. i want to check a specific cell and if no data has been
entered, exclude a range of cells from printing. (for example... If B94=0
then B89:B120=don't print ) now i just print all the "pages" and throw away
the two "blank" pages.

 

[JLGWhiz]

I have my printer set for automatic print preview. You can do that in the
advanced page setup by checking the print preview block. This gives me the
opportunity to see how many pages are going to print and to make any
adjustments prior to clicking the print button to release it to the printer.
This works fine for my purposes, but would probably draw all kinds of groans
and gripes from other users.

 

[david s.]

you are so right about that..... once i have this form completed, i will not
be the one to use it, and i already am looking forward to (NOT) phone calls
for directions. i am trying to make it as "childproof" as possible. so, with
that in mind, i believe my solution will be on the order of inserting code at
the BEFORE PRINT event for the worksheet and using an IF,THEN,ELSE statement
to set the print area, (the syntax of which I am not certain about and will
have to 'trial-and-error my way through it if someone doesn't spell it out
for me). also, thanks to those who seem to think they know more about what i
want than I do, I now have OFFICE 2007 (formerly OFFICE 2000), and I cannot
find the BEFORE PRINT event where i am used to seeing it. these are my
issues: what to put and where to put it. need closure to this problem so i
can move on to the next issue: locking / unlocking cells based on their value.

 

[david s.]

OK, I did find the BEFORE PRINT event... somehow overlooked before (duh). so
now i am trying to set print area based on a single cell's value using

(copied from another post: thanks to the author)

If Application.WorksheetFunction.CountA(.Range("B53:C53")) < 1 Then
ActiveSheet.PageSetup.PrintArea = "$A$1:$L$40" & "$A$82:$L$194"
End if

but this isn't working, and i am sure it is simply a syntax issue.

 

[david s.]

mission accomplished.... with credit to "OssieMac" in a previous post.

for anyone who has the same issue, here is what worked for me:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

If ActiveSheet.Range("B125") = 0 And ActiveSheet.Range("B166") = 0 Then

ActiveWindow.SelectedSheets.PrintOut _
From:="1", To:="3", Copies:=1, Collate:=True

ElseIf ActiveSheet.Range("B125") = 0 And ActiveSheet.Range("B166") <> 0 Then

ActiveWindow.SelectedSheets.PrintOut _
From:="5", To:="5", Copies:=1, Collate:=True

ActiveWindow.SelectedSheets.PrintOut _
From:="1", To:="3", Copies:=1, Collate:=True

ElseIf ActiveSheet.Range("B125") <> 0 And ActiveSheet.Range("B166") = 0 Then

ActiveWindow.SelectedSheets.PrintOut _
From:="1", To:="4", Copies:=1, Collate:=True

Else: ActiveWindow.SelectedSheets. _
PrintOut From:="1", To:="5", Copies:=1, Collate:=True

End If

End Sub