Solved

Set US Letter or A4 paper size using Excel VBA and format as appropriate

Posted on 2011-03-18
3
1,300 Views
Last Modified: 2012-06-27
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

Pete


0
Comment
Question by:pfmurray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 3

Accepted Solution

by:
jerbear1337 earned 500 total points
ID: 35171214
That sounds like a pain. What about just formating it with the length of letter and width of A4 and then setting your page size A4 or letter and then centering the document vertically and horizontal for printing purposes?  I dont know the VBA for that but there is button up top to center vertical and horizontal which can direct you to the VBA obviously if you do record macro and click it.   I know you wont be optimizing the space on both paper formats but it should look acceptable and be a heck of alot easier
0
 

Author Comment

by:pfmurray
ID: 35171756
Hi Jerbear. Thank you for the suggestion. I'll try that and see what happens. The only problem I foresee with vertical centering is that with shorter 2nd or 3rd pages it'll look a bit weird. However it is something I'll experiment with.
0
 

Author Comment

by:pfmurray
ID: 35204943
Hi Jerbear I'm going to award some points to thank you for your suggestion, but I'm going to re-word and re-post the question in the hope someone can point me to the actual VBA.  Thanks again for your time in posting
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question