Removing the Print options

[jasgot]

I have a macro that prints after doing some work for me.

I would like to disable the print options from the menu and tool bars so the user must click my button in the sheet.

I've tried these two peices of code, neither addresses the user pressing <ctrl>-P and both leave the print option removed from excel for other workbooks even if I re enable them in an Auto_Close macro.


Application.CommandBars(1).FindControl(ID:=109, recursive:=True).Enabled = False
'Application.CommandBars(1).FindControl(ID:=2521, recursive:=True).Enabled = False
'Application.CommandBars("File").Controls("Print...").Enabled = False
'Application.CommandBars("Standard").Controls(6).Enabled = False


Can someone shed some light on a better way to force users to use *my* print macro?

 

[jasgot]

btt

 

[gwkenny]

What does "btt" stand for?

Anyways, what you're looking for is:

Application.OnKey "^p", "[Code name you want to run heah]"

Want to do it in AutoOpen so it's defined for everything or via an application wrapper.

g-

 

[jasgot]

Back to Top

Thanks for the control p fix, I still have the problem turning the print menu options back on.

 

[gwkenny]

Don't understand. The print option is still there in your File drop down?

What you could do is manually rebuild the menu on the fly. It's not that hard though it's 3am where I am so I'm going to sleep. If this is indeed your question, then send me a private message and I'll post back the answer on how to do it. Basically, you'd redefine Print to run your macro instead of opening the dialog box. Does this upon app startup.

g-

 

[Legacy 35]

Try this.

Put in the ThisWorkbook module :-

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If OKtoPrint Then
Cancel = False
Else
MsgBox "You can only print by clicking the Special Print button"
Cancel = True
End If
End Sub

Put this in a normal module :-

Public OKtoPrint As Boolean

Sub PrintIt()
OKtoPrint = True
End Sub

Sub DontPrintIt()
OKtoPrint = False
End Sub


In your macro that does the printing, call PrintIt before your print instruction and call DontPrintIt after it.

 

[jasgot]

Thanks!
That is very elegant!

And it works

 

[mrnacar]

I would like say thank you for I have benefited from this post.

Try this.

Put in the ThisWorkbook module :-

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If OKtoPrint Then
Cancel = False
Else
MsgBox "You can only print by clicking the Special Print button"
Cancel = True
End If
End Sub

Put this in a normal module :-

Public OKtoPrint As Boolean

Sub PrintIt()
OKtoPrint = True
End Sub

Sub DontPrintIt()
OKtoPrint = False
End Sub


In your macro that does the printing, call PrintIt before your print instruction and call DontPrintIt after it.