Hide Pages On Sheet Based On Value In Cell

[swordswinger710]

Hello, I have an interesting issue here;

If Cell X1 on my sheet receives a 1, only Page 1 may be shown, and Page 2 and 3 hidden.
If Cell X1 on my sheet receives a 2, only Page 1 and 2 may be shown, and Page 3 hidden.
If Cell X1 on my sheet receives a 3, all the pages may be shown.

What would be the best way of doing this?

[Andrew-R]

Something like this, in the worksheet_change event should do the trick:

Please Login or Register  to view this content.

[swordswinger710]

Hmm, I can't seem to make that work, at all. Does it work for you?

[john55]

hi,
the code provided by Andrew works fain!
if you have x1=1, you can see Sheet1 only and so on!

[swordswinger710]

Oh! I see what you're doing there now, that does work great! However, I'm trying to display numbers of pages, not sheets.

[john55]

of course it's not a ...method, but I would see my printarea and then something like this

Please Login or Register  to view this content.

[swordswinger710]

This... might work! Now how do I merge that code with this code already on the sheet?

Please Login or Register  to view this content.

[swordswinger710]

Here is my sample workbook. I need this code:

Please Login or Register  to view this content.

.. to be merged with the Worksheet_Change event already assigned to the Inspection Report. Does anyone know how?

[john55]

it depends of what you want to achieve...
just try

Please Login or Register  to view this content.

[swordswinger710]

Hmm, that almost does it, but it's really slow, especially when I scroll down past the bottom of the last page. All the numbered column cells get really wide too. Any more ideas?

[john55]

nope, I am sorry!

[swordswinger710]

Anyone else have any ideas here? If I could just set the print area according to the X1 value, that would work as well; might be easier than trying to hide entire pages.

[tigeravatar]

swordswinger710,

Attached is a modified version of your posted workbook. I made several code changes, most notably to the "Save Documentation as PDF" button which is where the .PageSetup.PrintArea code was incorporated (great idea on that, john55!).

Take a look and test it out. Let me know if that works for you.

[swordswinger710]

Whoa, I think this is gonna do it! Thank you tigeravatar! How do I get the Inspection Report Attachment to do the same?

[tigeravatar]

swordswinger710,

It already does. If it was not printed, then the condition to print it was not met. Here's the snippet of code to see if it should be printed:

Please Login or Register  to view this content.

And later in the macro, here's the snippet for the 'Inspection Report Attachment' print area:

Please Login or Register  to view this content.

In the file you posted, there is no information in 'Inspection Report Attachment' cell B6, so it was not included in the SaveAsPDF.

[tigeravatar]

Although, now that I've said that, I noticed that the bIRA variable was never declared or set, oops, lol. Here's a corrected version of the code:

Please Login or Register  to view this content.

[swordswinger710]

And that does it! YEEEHAAAAA!! Sorry.

Any idea why it's asking me to save when I haven't done any changes though?

[swordswinger710]

Oh, and am I wrong, or would we be able to get rid of the PrintArea stuff here now and just go with the "R1" value?

Please Login or Register  to view this content.

[tigeravatar]

The R1 value is simply 1, 2, or 3. You need to tell the page the specific range of cells that will be used for the print area. That specific range is based on R1, but the R1 value isn't "A1:R46", it's 1. That's why it's setup like that.

[tigeravatar]

As far as prompting to save, your workbook has a lot going on in open events and sheet_change events. Even if you don't make any changes to the workbook, simply the act of opening it is causing the workbook to be flagged as "dirty" (meaning it thinks it needs to be saved). There's not really a way around that with your current setup.

[swordswinger710]

Awesome - would there be a way to have it automatically save upon opening then? So that if the user closed the workbook immediately after doing so, that prompt wouldn't appear?

[tigeravatar]

You could try adding the line ThisWorkbook.Save as the last line in the workbook_open event.

[swordswinger710]

Ha! You got it! This problem is now OVER! Thank you a MILLION for all you've helped me with!