Before printing excel worksheet, disable if it was done by file menu and enable if by button on a sheet

[nemke]

I'm trying to resolve an issue with printing a sheet in Excel. So I was wondering whether there is a way to allow printing via button only. In ThisWorkbook I put the code below. What should I put in a module? How to pass 'button_used' variable from module back to ThisWorkbook?

Public Sub Workbook_BeforePrint(Cancel As Boolean)

'disables print if button is not used

If button_used = False Then MsgBox "Print is disabled, use button" Cancel = True End If End Sub

 

[Anthony47]

So you already have a Sub associated with a button to start printing

The easiest way for doing what you need would be to toggle EnableEvents False/True and beginning/end of your print code

Sub MyPrinting
Application.EnableEvents = False
'
'Your current code here
'
Application.EnableEvents = True
End Sub

This way your Sub Workbook_BeforePrint will not activate when you use the button

The problem with this code is that if the print routine does not complete (in case of run time error, for example) then EnableEvents could stay False, disabling Workbook_BeforePrint and any other event in the workbook.

So my suggestion is:
1) on top of a standard module of your vba project, declare Button_Used as a public variable:

Public Button_Used As Boolean

Again: "On top of a vba standard module"

2) The modify your current print routine:

Sub MyPrinting
Button_Used = True
'
'Your current code here
'
Button_Used = False
End Sub

3) modify your Workbook_BeforePrint as follows

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Button_Used = False Then
    MsgBox "Print is disabled, use button"
    Cancel = True
Else
    Button_Used = False
End If
End Sub

This way Button_Used will be cleared at the first Workbook_BeforePrint (and Events will never get disabled)

Try...

 

[nemke]

Many thanks Anthony for the reply.

It worked well

Best regards,
Nemanja

 

[Anthony47]

Thank you for the feedback