Dynamic Print Areas with VBA

[excely1]

I have an excel sheet with 7 different 'forms' on it ranging from A1:O43, A44:O77, A78:O111, A112:O145, A146:O167, A168:O189, A190:O220. Each form is on 1 page. If a form is not used, I have made buttons to hide the form on the workpage (code below) but it would still print a blank page (I know you can manually change print areas/print selection). The last form (A190:O220) is a totals page which should only be added to the print area if 2 or more forms are used. Im looking for a solution to include/remove these forms from the print area when their respective buttons are clicked to show/hide them from the worksheet (if possible). If this is not possible, an alternative would be to have a new macro to set the print area to current active cells (not including the hidden rows of forms not in use). Im not advanced at coding so I have essentially been going through each possible combination of forms (code below) that have a value on them to include in the print area. Im sure there is a much faster method out there.. any help would be appreciated. Thanks!

Button to hide/show 1 of the forms (for example):

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[Greg M]

Hi there,

See if the attached workbook does what you need - it uses the following code:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your eventual needs.

Feel free to ask about anything you need to know regarding the above code.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[excely1]

Looked at the excel file attached.. the print preview still shows a blank page when a form is hidden. I actually came across a solution on mrexcel forum and made adjustments to suit my needs. It wont let me post links unfortunately :/

Thanks for the help anyways!

[excely1]

This is the code from the page since i cant link it:

Please Login or Register  to view this content.

[Greg M]

Hi again,

Many thanks for your feedback and for the code you discovered.

What you encountered seems strange . . . I downloaded and opened the file I posted earlier, "hid" some of the ranges (by removing the text from various red cells) and then ran the code. The resulting PrintPreview showed one page for each of the non-hidden ranges, but NO blank pages - as Alice said, curiouser and curiouser

Anyway, glad to hear that you now have a solution that works for you.

Regards,

Greg M