Set Print Area conditionally?

[Steve Coates]

My worksheet is a student report consisting of one page per subject. Cells are populated with data from an external source. Some students take 3 subjects and some take 4, so my worksheet has 4 pages.

I want to be able to set the print area according to whether 3 or 4 pages are populated with data. So is it possible to enter something like "IF cell XY99 is empty then print area is [range1], otherwise print area is [range2]"? Currently I don't know whether such a thing is possible in Excel.

Thanks for any suggestions.

Steve

 

[erik.van.geit]

Hi, Steve,

try this

Sub test1()
    With ActiveSheet.PageSetup
        If Cells(99, "H") = "" Then
        .PrintArea = "A1:H98"
        Else
        .PrintArea = "A1:H140"
        End If
    End With
End Sub

same result, but oneliner

Sub test2()
ActiveSheet.PageSetup.PrintArea = IIf(Cells(99, "H") = "", "A1:H98", "A1:H140")
End Sub

kind regards,
Erik

 

[Steve Coates]

Many thanks Erik.

I guess this is VB, which I'm not really familiar with but I'll have a go.

1. How do I go about entering this routine? and
2. I don't understand the "cells(99,"H")" part.

Best wishes,

Steve

 

[erik.van.geit]

yes, you need a macro

start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the your code

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)

assign shortcutkey
menu Tools/Macro/Macros
select your macro
click options
choose a character as shortcut: example T
to run the macro press Ctrl+Shift+T

perhaps you will want to run this just before printing the sheet
we'll see how to do this once you get this code running

you were talking about cell XY99, whic doesn't exist
so I made it H99
edit to your suits

best regards,
Erik

 

[Steve Coates]

Thanks Erik, I'm back on this job now.

I have successfully entered and run the macro and checked that the print area is appropriately set. So that's working fine.

Now...

The Excel spreadsheet is being called up by an external program which is also merging the data from its database into the appropriate cells in the worksheet. The instruction to print is also made from the external program, and when all my data is in place the instruction to print will be for multiple students. So, finally, how do I ensure that, during this multiple print run, the macro is called up before each printing to ensure that the correct print area is set for each student?

Best wishes,

Steve

 

[erik.van.geit]

Hi,

try

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.PrintArea = IIf(Cells(99, "H") = "", "A1:H98", "A1:H140")
End Sub

TO INSTALL IN THISWORKBOOK CODE WINDOW:
1. Rightclick the little Excel-icon on the topleft of your page just beside the Filemenu
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE

hoping this will run when print is called from an external program
(no experience whith this)
I would be curious to know what you mean by "an external program"

best regards,
Erik

 

[Steve Coates]

Hello again Erik, and thanks for your help on this.

OK. The "external program" is a program I and my colleagues use to manage lots of different student data, including grades and results of different sorts, comments from teachers, what subjects they are studying, what classes they attend, etc.

One of its features is that it allows me to produce reports on individual students, populated with whatever kinds of data I choose. Within this program, when I use the reports module, Excel is opened and the data I have selected to be used are inserted into a worksheet already set up as a template. I can edit the Excel worksheet, for example controlling what data are placed where.

When I am working in the Excel worksheet and testing the layout, printing etc, both the macros you've given me do their job and the print area is changed appropriately. However, back in the external program, when I choose which students I wish to print a report for, and press "Print Reports", the print area remains constant and it appears that the macro routine is not running prior to each report being printed.

Difficult to be both clear and also concise - does that give you a clear picture? It looks perhaps as though the problem will have to be resolved by tweaking the external program rather than Excel itself, but if you have any further suggestions or ideas I'd be delighted to know.

Thanks again and best wishes,

Steve

 

[erik.van.geit]

Difficult to be both clear and also concise - does that give you a clear picture? It looks perhaps as though the problem will have to be resolved by tweaking the external program rather than Excel itself, but if you have any further suggestions or ideas I'd be delighted to know.

what a colorful language

this will probably be beyond the limits of my knowledge

I'll call some guys for help in a few minutes
you will need top post the relevant part of the code you are using in the external programe
(how do you refer to Excel from there ?)

best regards,
Erik

 

[erik.van.geit]

to be sure for 100%

1. did you put the before-print in the correct module ?
2. is it called ??: add a testline

Private Sub Workbook_BeforePrint(Cancel As Boolean) 
MsgBox "BEFORE PRINT"
ActiveSheet.PageSetup.PrintArea = IIf(Cells(99, "H") = "", "A1:H98", "A1:H140") 
End Sub

print from excel: message should popup
print from external source ... no message ?

 

[Steve Coates]

Thanks Erik for your further suggestions. I'm now very confused.

1. Yes, I'm sure both macros are in the right place. I followed your instructions carefully

2. With the amendment you suggested to the Before-Print code, there is now a pop-up message when I print either from Excel or from the external program. This suggests that the routine is being called.

3. However, I still have the same outcome as before. If when I last used Excel the print area was set by the macro at 3 pages, then when I run my reports from the external program, the report prints 3 pages even if it should be 4 - despite the fact that the pop-up message is appearing which tells me the macro is running!!!

4. Sorry but I don't understand enough to be able to give you any code from the external program - as far as I can tell, it is using Excel for what is essentially a mail-merge process.

(sigh)

Steve