Disable Setup Button On Print Preview

Activesheet.PrintPreview False

Quote

Originally posted by Ponsonby
Activesheet.PrintPreview False

I don't think that will help Pete as I believe he wants to be able to stop the "setup" from being accessible in Print Preview. If he wanted to disable the Print Preview option from the File menu he could use

Sub no_print_preview()


Application.CommandBars("worksheet menu bar").Controls _
            ("&file").Controls("&Print Preview").Enabled = False
            
End Sub

I cannot find a way to prevent access to the "setup" button in Print Preview - sorry Pete!

Perhaps one of the VBA experts have some ideas?

Dave

I believe it can't be done (there's a challenge that usually invokes the way to do it!) because the Print Preview doesn't display one of Excel command bars so there is nothing that a user can manipute. Since the print preview comes after the before print event, you can't even trap changes to the page setup before someone could print from print preview.

I agree Derk, because it isn't one of the command bars you (at least I) cannot control it. You can easily disable the Print Preview as my code shows but the rest...hmmm?

I reckon Will Richie or Dave will know a smart way??

Dave

Quote

Originally posted by Derk
I believe it can't be done (there's a challenge that usually invokes the way to do it!) because the Print Preview doesn't display one of Excel command bars so there is nothing that a user can manipute. Since the print preview comes after the before print event, you can't even trap changes to the page setup before someone could print from print preview.

Quote

Originally posted by XL Dave

I don't think that will help Pete as I believe he wants to be able to stop the "setup" from being accessible in Print Preview. If he wanted to disable the Print Preview option from the File menu he could use

Sub no_print_preview()

Application.CommandBars("worksheet menu bar").Controls _
("&file").Controls("&Print Preview").Enabled = False

End Sub

I cannot find a way to prevent access to the "setup" button in Print Preview - sorry Pete!

Perhaps one of the VBA experts have some ideas?

Dave

Display More

Why don't you actually try my suggestion, rather than just dismissing it because you think you know what it does?

It does not disable Print Preview.

It opens Print Preview with SetUp and Margins disabled.

Yes......TEST what Ponsonby has posted

The ActiveSheet.PrintPreview False is a little know property that disables the setup in print preview........

Will Richie or Dave AREN"T the only ones who know a little coding, I would suggest Ponsonby knows a great deal as well !

Ivan, Ponsonby

You are quite correct, I should have tried the code first. Because I am a VBA newbie I didn't have a clue what it would do and my code suggestion was from a recent VBA lesson.

I never meant to question the knowledge of Ponsonby and I apologise unreservedly if that is how it came across.

Lesson learned and a nice bit of info for the future.

Thanks

Dave

Hello Xl Dave

Name reminds me of my friend in Sweden Xl Dennis

No problem, sorry if I came across, perhaps a little to heavy .............

All the Best..........

I took no offense to anything posted in this thread.
Perhaps my last post could have been a bit more delicate - I tend to be a bit abrupt.

The answer to the question posed in this thread is really not so difficult to find.

A search for "PrintPreview" in VBE Help shows that the PrintPreview method has one argument called "EnableChanges".
It doesn't take a great deal of logical deduction to set the argument to False, and then run it to see what it does.

Hi Ponsonby

Unfortunately you will find that most posters totally ignore the existence of Help files and indeed the Search fcility on this forum.

Whatever, nicely handled and keep posting

Interesting. I did not try the printpreview false, but I did go to VBE help for print preview. My version (Excel 97) made no mention of an argument. It merely says

Shows a preview of the object as it would look when printed.

Syntax

expression.PrintPreview

expression Required. An expression that returns an object in the Applies To list.

I then checked on a machine with Excel 2000 and it also did not mention an argument. Microsoft must of had it as a little known feature as well until ??? Which version first has the argument in the help file?

False does work, howver, for Excel 97.

You are correct that the PrintPreview Method in the Help file does not mention any argument.

However, a search for PrintPreview produces only 3 topics :-

PrintPreview Method
PrintPreview Method Example
Methods with New Arguments

The 3rd topic states that PrintPreview has an argument called EnableChanges

Quote

Originally posted by Ivan F Moala
Will Richie or Dave AREN"T the only ones who know a little coding, I would suggest Ponsonby knows a great deal as well !

Agreed. I would suggest that there are many talented posters here, including you Ivan (presumably your modesty prevented you from saying as much ;;) )

Quote

Originally posted by Ponsonby
... I tend to be a bit abrupt.

Really? :biggrin:

Just to keep the discussion going, while Activesheet.PrintPreview False
does show the print preview screen with the setup button disabled, it is a one time thing. If the user selects print preview the setup button will be enabled. Pete would need to replace the standard calls from Excel's Print Preview wherever occurring and replace them with his own macro. Can this be done from each possible location? For example the user selects Print and on the Print dialog box is a Preview button. Would a custom Print form need to be created to mimic the Print dialog box? I don'y know what Pete intended with his original question, but if it was for a comprehensive disable, how tough would it be?