Disable print of opened workbook

[CsJHUN]

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]

@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.

 

[Jaafar Tribak]

@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]

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]

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

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]

I have put Debug statement like that in the userform

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

Debug.Print oWnd.Caption

I got the string "Test.xlsm" in the immediate window five times!!

 

[Jaafar Tribak]

I have put Debug statement like that in the userform

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]

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

 

[Jaafar Tribak]

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]

@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]

@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