Disable print of opened workbook

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
357
Office Version
  1. 2016
Platform
  1. Windows
Hi guys, i would like to disable any kind of print option of the opened file (excel or ppt mainly).
There is a wb which contain the macro. In this wb there is ~4k link (let's says its a master list of xlsx, pptx, ....) I would like to diable any kind of printing possibilities on that files.
I thought i write a wb_deactivate/window_deactivate event then disable. After opened file are closed, the printing should be enabled again.

I familiar with wb_beforeprint event, but that should be in each and every file linked to this master list file, right? Also tried playing along with disabling the Print and Print... option (which not worked flawlessly :) ) but there is still the Ctrl+P shortcut

Any workaround, ideas, solutions?
Thanks
 

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
815
@Jaafar Tribak
I have put a sample excel file path in the list and the excel was open .. Clicked on Start Monitoring Printing, then go to the sample excel file and pressed Ctrl + P and it printed without any interruption.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,168
Office Version
  1. 2016
Platform
  1. Windows
@Jaafar Tribak
I have put a sample excel file path in the list and the excel was open .. Clicked on Start Monitoring Printing, then go to the sample excel file and pressed Ctrl + P and it printed without any interruption.
Thanks Yasserkhalil for testing.

Did you place the file Fullname (Path+ file name + file extension) in the correct Sheet and correct column ? - ie: Sheet1.Column A
 

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
815
Yes I did. In the filename `CancelPrint` in Sheet1 in column 1 in A2 I put `C:\Users\Future\Desktop\Test.xlsm` then I clicked the start button. Then I go the opened Test.xlsm and printed the first worksheet.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,168
Office Version
  1. 2016
Platform
  1. Windows
Yes I did. In the filename `CancelPrint` in Sheet1 in column 1 in A2 I put `C:\Users\Future\Desktop\Test.xlsm` then I clicked the start button. Then I go the opened Test.xlsm and printed the first worksheet.
Ok- Difficult to figure out what is going on without debugging as you don't seem to get any error.

I have tested the code on several workbooks and docs and it all worked as expected.

Maybe, if you can place a Debug.Print statement inside the WordAppEvents_DocumentBeforePrint eevent handler and see if the code reaches there.
Debug.Print Wb.Name

Also here : Debug.Print oWnd.Caption in the GetObjectRef Function
VBA Code:
If hwnd Then
   If AccessibleObjectFromWindow(hwnd, OBJID_NATIVEOM, VarPtr(tDisp(0)), VarPtr(oWnd)) = S_OK Then
      Debug.Print oWnd.Caption
      Set GetObjectRef = oWnd.Application
   End If
End If
 

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
815

ADVERTISEMENT

I have put Debug statement like that in the userform
VBA Code:
Private Sub WordAppEvents_DocumentBeforePrint(ByVal Doc As Word.Document, Cancel As Boolean)
    Debug.Print Wb.Name
    Cancel = CancelPrinting(Doc)
End Sub
And printed the worksheet in the Test.xlsm and I didn't find any output in the immdiate window.

And after putting this line
Code:
Debug.Print oWnd.Caption
I got the string "Test.xlsm" in the immediate window five times!!
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,168
Office Version
  1. 2016
Platform
  1. Windows
I have put Debug statement like that in the userform
VBA Code:
Private Sub WordAppEvents_DocumentBeforePrint(ByVal Doc As Word.Document, Cancel As Boolean)
    Debug.Print Wb.Name
    Cancel = CancelPrinting(Doc)
End Sub
And printed the worksheet in the Test.xlsm and I didn't find any output in the immdiate window.
Did you try the other (Debug.Print oWnd.Caption) in the GetObjectRef Function ?
Sorry for the hassle.

PS. Is the Test.xlsm open in the same excel application or in a second instance ?
 

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
815

ADVERTISEMENT

The Test.xlsm is opened in a second instance of excel application.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,168
Office Version
  1. 2016
Platform
  1. Windows
The Test.xlsm is opened in a second instance of excel application.
What about the other (Debug.Print oWnd.Caption) in the GetObjectRef Function ?

Anyway, let's wait and see what the OP gets.
 

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
357
Office Version
  1. 2016
Platform
  1. Windows
@Jaafar Tribak thanks, edited the functions and subs it's working on my end with PPT's
(still trying to figure some functions out like uwatcher, its a whole new level for me with VBA)
Thanks for your time for both of you @Jaafar Tribak and @YasserKhalil
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,168
Office Version
  1. 2016
Platform
  1. Windows
@Jaafar Tribak thanks, edited the functions and subs it's working on my end with PPT's
(still trying to figure some functions out like uwatcher, its a whole new level for me with VBA)
Thanks for your time for both of you @Jaafar Tribak and @YasserKhalil
Glad to hear the code is working for you.... I am still curious to know why the same code doesn't work for YasserKhalil
 

Watch MrExcel Video

Forum statistics

Threads
1,127,105
Messages
5,622,762
Members
415,926
Latest member
jerrynababa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top