Excel 2003 Print hidden sheet 2

[JudyL]

I want to keep a final calculation sheet hidden from the user but I want to be able to print it and only that sheet. Is there a way to do this without making the sheet visible?
I need to do the same thing for print preview.

 

[SkipVought]

Hi,

One way...

with YourSheetObject
   .visible=xlsheetvisible
   .printout
   .visible=xlsheethidden
end with

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JudyL]

Thanks. that helps. Is there any way to do the same for Print preview. In other words, I want to be able to have the Print Preview button or menu item, just show the hidden sheet. When the user closes the Print Preview screen, have it still hide the sheet.

 

[SkipVought]

Turn on your macro recorder and record opening the print preview.

Observe your recorded code.

Substitute for PrintOut.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JudyL]

Perfect. Thanks

 

[JudyL]

Actually, that doesn't quite work after all. What I want is the printout when the person chooses to print. I prompt them ahead of time to find out how many copies.

I am using the BeforePrint to force it to print out just the sheet I want. I think I have that down.

I also want them to be able to preview it but if they select print from the preview button, it triggers the BeforePrint. Is there a way I can tell if they chose Print Preview?

 

[SkipVought]

Too much attempt to control your user. Excel is not designed to be used so narrowly, IMHO. Even if your could HIDE the controls that you do not want the user to use, a savy user can use keyboard shortcuts to accomplish nearly anything that has no visible button or menu.

I believe that you ought to rethink your application.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JudyL]

It's not my call. It is the client's.

I think I will just go with asking the user if they want to print or preview and then use the printout if they want to print.

Thanks.

 

[JudyL]

Let me try to state what I need to do again. I want to just allow the user to print a hidden sheet that has totals from the other sheets. I want to let them print preview it or print whatever number of copies they want but otherwise keep the sheet hidden.

The BeforePrint statment kicks in when they use any method of trying to print including going to print preview.
I can ask them via user box how many copies they want, but I don't want them to be able to get into the print dialog box unless I can control that they just change the number of copies or the printer. No other changes should be possible.

I don't need evaluation on whether this is a good idea or not. That is out of my control. I just need to know how to do it. Thanks for any help you can provide.

 

[SkipVought]

but I don't want them to be able to get into the print dialog box

Not possible. ctr+P opens the Print Dialog, regardless of what controls you have.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

You can limit the printing funcionality in the other way.
Add an empty worksheet, copy the range that you need to print and paste as the picture link (available in 'Edit' menu when clicked with SHIFT key pressed) on the empty page.
You can now even make the source sheet very hidden. You can change the source address of the picture to named range, incl. dynamic one.
As for printing limitation:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
ActiveSheet.PrintPreview False
Application.EnableEvents = True
End Sub

combo

 

[SkipVought]

However, the question, as I understand it is, while in PrintPreview, prohibit print; I maintain that since the sheet at that point is VISIBLE, the user will be able to PRINT using the keyboard shortcut. The OP want to prohibit that capability.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JudyL]

While in PrintPreview, I just don't want them to be able to print anything but the sheet that is now visible that was hidden. Once printed, I want that sheet to be invisible.

Thanks so far for all your help with this.

 

[SkipVought]

I've been playing around with a CAMERA concept.

Use the Camera tool to copy a picture of the sheet(s) in question to another sheet(s).

The values from your hidden sheets can then be viewed and printed using the PICTURE on visible sheet(s).

I might NAME each picture, so you'd know which is which, and put them ALL on one sheet, making each picture visible or not programatically. The user will not see any formulas and cannot unhide anything that's hidden, since all they're seeing is a PICTURE.

Would this be a possible course of action?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JudyL]

We have thought of that too. Would I insert that into the BeforePrint event?

 

[SkipVought]

Sure.

You have not disclosed the reasons for all this control, so it's difficult to make suggestions.

When my user says that they want something, I find out why then want that something. Many times they are thinking in very limited terms, and the REASON uncovers the REAL requirement.

It's like the guy that cuts the end off the ham every year for the holiday feast. When asked why, he stated that thats how Mom did it. When Mom was asked why she did it that way, she replied, "I had to make it fit in my roaster."

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JudyL]

I think I am actually going back and using Combo's suggestion of copying the hidden sheet to another workbook either as a picture or just paste values and then the user can do what they want in terms of print preview, printing or even printing to pdf. Can I do that within the Workbook_BeforePrint event or just the Before_Print event? If not, I will create a button to run and just have the Before_Print cancel all printing.

Thanks for your ideas.

 

[combo]

The 'Camera' tool does exactly the same - pastes picture link.

combo

 

[SkipVought]

combo,

Sorry, I stepped on your suggestion.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JudyL]

I'm not familiar with that. Will it capture any graphic I have on that sheet also?