How to prevent printing while still be able to PrintPreview ?

[Jaafar Tribak]

Setting the Cancel arg in the BeforePrint event to TRUE also prevents using the PrintPreview command.

Has a workround this been addressed before?

Regards.

 

[Jaafar Tribak]

----Bump----

 

[Jaafar Tribak]

----Bump---- There's got to be a trick to this one.

 

[Peter_SSs]

Jaafar

Does this do what you want?

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforePrint(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    ActiveWindow.SelectedSheets.PrintPreview<br>    Cancel = <SPAN style="color:#00007F">True</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[RoryA]

You might also try intercepting the Print Preview commands: http://www.mrexcel.com/forum/showthread.php?t=81706

There isn't a good way of detecting which is occurring from within the event that I've ever seen.

 

[Jaafar Tribak]

Peter - that was so simple yet so neat . Only problem is that the user can still perform a printing from within the PrintPreview window. One workaround would be to disable that control from within the Print event.I'll be looking into that.

rory- the approach adopted in the link relies on disabling each instance of the Print Control. Although it works (except if a new/custom print control was created or Ctrl+ P was pressed) i am looking to detecting which is occurring from within the event as you mentioned.

Thanks you both for your help.

Regards.

 

[Jaafar Tribak]

Hi again.

As it happens,the buttons on the excel PrintPreview menu are not standard commandbar controls despite their ressemblance.

Using Winspector showed their Class names & Hwnd. this is handy as one can then perform API functions on them.

A bit of research on the msdn site came up with the GetDlgItem API function which retrieves the hwnd of Dialog controls.Conviniently, this function also works for any Parent/child window!

Once the hwnd of the Print button is retrieved, it's just a simple case of disabling the button via the EnableWindow function.

In order to asynchronously execute the code that disables the Print button while the modal Printpreview window is on display, i found no other solution but to use a timer as shown in the code below.



http://www.datafilehost.com/download-b34f13bc.html

Place in a standard module:

Option Explicit
 
Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
 
Private Declare Function FindWindowEx _
Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Long
 
Private Declare Function GetDlgItem _
Lib "user32" _
(ByVal hDlg As Long, _
ByVal nIDDlgItem As Long) As Long
 
Private Declare Function EnableWindow _
Lib "user32" _
    (ByVal hwnd As Long, _
    ByVal fEnable As Long) As Long
 
Public Declare Function SetTimer _
Lib "user32" _
    (ByVal hwnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerFunc As Long) As Long
 
Private Declare Function KillTimer _
Lib "user32" _
    (ByVal hwnd As Long, _
    ByVal nIDEvent As Long) As Long
Public lTimerID As Long
 
Public Sub DisablePrint()
 
    Dim lXlHwnd As Long
    Dim lPrintPrevHwnd As Long
    Dim lPrintButtonHwnd As Long
 
    [COLOR=seagreen]'use the timer once so remove it[/COLOR]
    KillTimer 0, lTimerID
 
   [COLOR=seagreen]'get the excel window hwnd[/COLOR]
    lXlHwnd = FindWindow("XLMAIN", Application.Caption)
 
    [COLOR=seagreen]'get the printpreview window hwnd[/COLOR]
    lPrintPrevHwnd = FindWindowEx(lXlHwnd, 0, "EXCELC", vbNullString)
 
    [COLOR=seagreen]'get the printpreview print button window hwnd[/COLOR]
[COLOR=seagreen]  '"3" being the unique ID of the print button (found by trial & error)[/COLOR]
    lPrintButtonHwnd = GetDlgItem(lPrintPrevHwnd, 3)
 
    [COLOR=seagreen]'finally,disable the print button[/COLOR]
    EnableWindow lPrintButtonHwnd, False
 
End Sub

Place in the workbook module:

Option Explicit
 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
 
    Application.EnableEvents = False
    [COLOR=seagreen]'use a timer to execute code asynchronously[/COLOR]
   [COLOR=seagreen]'the vba ontime method doesn't work[/COLOR]
    lTimerID = SetTimer(0, 0, 1, AddressOf DisablePrint)
    ActiveWindow.SelectedSheets.PrintPreview
    Cancel = True
    Application.EnableEvents = True
 
End Sub

Again, thanks Peter for your help with this.

Regards.