Disable Print Option in File Menu for a workbook

[tompsed]

I am making an excel sheet for quality control use and only want the user to be able to print via a button that I put on the sheet. So I need to disable to the option to print from the File menu. I am running excel 2000.

Thanks

 

[zzjasonzz]

in the worksheet open sub try

CommandBars("Worksheet Menu Bar").Controls("File").Controls("Print...").Delete

but make sure you put this in the close

CommandBars("Worksheet Menu Bar").Reset

 

[tompsed]

Gives Run Tim error 91

The code in the open gives Run Tim error 91.
Object variable or With block variable not set.

Any ideas?

 

[tompsed]

The code does work, running it in the open method is to soon

The code does work, running it in the open method is to soon. I ran it separately after the sheet was opened and it works well.

So I need for it to run automatically, but after the open method.

Is there a way to do this?

 

[Jaafar Tribak]

Hi,

I think that rather than having to delete all Commandbar\Menu Printing Items you should set a global Boolean flag at the start of your Button code to indicate that the Printing is requested from the Button.

So , the Button code should look something like this :

'\\ in a Standard Module

Global g_blnCustomPrint As Boolean


Sub PrintTest() ' \\ Button Code

    g_blnCustomPrint = True
    
    '\\ do your printing stuff here...
    Sheet1.PrintOut
    
    g_blnCustomPrint = False

End Sub

Then you just need to place this in the ThisWorkBook Module :

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    If Not g_blnCustomPrint Then Cancel = True

End Sub

I assume here that Sheet1 is the worksheet being printed. Change this as required.

Regards.

 

[tompsed]

Worked well.

Either way was a little questionable. I am not a big fan of global variables, but meddling with the menu system is probably more dangerous.

Thanksssssssssssssssss