Set US Letter or A4 paper size using Excel VBA and format as appropriate
Posted on 2011-03-18
I am collecting data using an Excel user form written in Excel 2007. The data collected then creates a report for the user. Because the volume of data captured on each form is variable I can't use a pre defined template, so instead I create formatted output (borders, emboldening etc) on the fly onto a clean worksheet which I then print.
The same form will be used in US and in Europe, so one of the radio buttons on the form prior to creating the output is to ask whether the user wants US Letter size paper or European A4 size.
Therefore I'd like some assistance in how to code the following...
1) How to best determine where page breaks should occur for each papersize (I'm thinking some sort of summation of row heights but I could be missing a better way and I don't know what sum of row heights works best for each size)
2) How to create manual page breaks for each papersize based on 1) above using VBA and not have them automated (I may want to break the page earlier if a section is likely to run over two pages)
Any help will be much appreciated