Printing Formula

Posted by Rob on April 27, 2001 5:38 PM

Is there a way to choose what cells get printed by using a formula? I tried putting it in under page setup/Print Area, but this evaluates the formula at that moment, and sets the print area accordingly. I want the formula to evaluate each time someone prints.
Thanks.

Posted by STEVE on April 27, 2001 6:18 PM

Hi rob
do you want to give me an idea of how you want it to work, you will have to use VBa code, maybe some if statements on the worksheet and specify the range's you want to print.

I would be happy to write something for you if you give me more info.
steve

Posted by Rob on April 27, 2001 6:31 PM

thanks for the offer. Heres what I'm trying to do. I have 5 pages of rows of cells that have the same borders and size etc. They are used to put comments into. Currently, when I print the comments, it prints all 5 pages, even if there is only 1 cell filled on the first page. I want it to only print the pages that have cells with comments. All I would have to do would be to test the first cell of a page to see if its blank, and that would determine if I want to print that page or not. I tried putting a nested if formula in the print area, and it works, but it only evaluates when I click ok, then it sets the print area statically (takes out the formula). Anything else you need to know? :)

Posted by steve w on April 27, 2001 6:51 PM

Can you tell me your ranges example(A1:S20)(T1:BD20)..........

Posted by Steve W on April 27, 2001 9:15 PM

Try this

You will need to sum to A30 when its equal to zero it wont print the range assigned to it. You will need to expand this and modify it to your needs.
You will need to place a form button on your page ang assign this macro

Past this into a module

Sub Myprint()

If Not Sheet1.Range("A30") = "0" Then
With Sheet1.Range("A1:s35").PrintOut
End With
End If

If Not Sheet2.Range("A30") = "0" Then
With Sheet2.Range("A1:s35").PrintOut
End With
End If

End Sub

Hope this helps
steve

Posted by Rob on April 28, 2001 12:09 AM

Try this You will need to sum to A30 when its equal to zero it wont print the range assigned to it. You will need to expand this and modify it to your needs.

Steve,

THANKS for the help! you sure got me on the right track... heres the code I came up with that works great:

Sub Button1_Click()

If Sheet1.Range("A52") = "" Then
With Sheet1.Range("A1:B51").PrintOut
End With
Else

If Sheet1.Range("A103") = "" Then
With Sheet1.Range("A1:B102").PrintOut
End With
Else

If Sheet1.Range("A154") = "" Then
With Sheet1.Range("A1:B153").PrintOut
End With
Else

With Sheet1.Range("A1:B204").PrintOut
End With

End If

End If

End If

End Sub

Thanks again Steve! I'm loving this forum, along with Excel more and more everyday :)

Rob