Disabling 'Print What - Entire Workbook'

Hello, I've been searching hundreds of posts but not found an answer to this. I have a procedure that applies lots of formatting, filters, repaginates and then finally opens the print dialogue box with

Application.Dialogs(xlDialogPrint).Show

What I need it to do is disable / grey out the "Entire workbook" button so that my users can only print out the active sheet. It defaults to active sheet but I just know that someone will change it and get a lot of wasted paper.

According to the help files and other posts, the 'Print what' section is governed by argument 7 but I don't know if this is where to disable an option or what value to pass to do this.

Another option would be to get my procedure to run across all the sheets but this would take quite a bit of time (takes about 30s for one sheet) and the users would think something was wrong even if I popped up a message box telling them it would take a while.

Any guidance on this would be greatly appreciated

Thanks

John

Originally Posted by davegugg

Can you just use VBA to print the sheet instead of showing the Print Dialog?

Assuming you have already defined your print area, you could use something like:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.Print
Cancel = True
End Sub

Hi Dave, I would need to recreate the whole print dialogue as I need the users to be able to select the printers (their own or networked) and change any other ranges etc. If I just printed directly from VBA then the correct printer might not be chosen.

Items can be greyed out, at the moment 'Table' is greyed out when the dialogue opens so I think it must be possible and that is what is really bugging me.

thanks

John