Check if printing entire sheet or just selection

[Dave_T_]

Good afternoon,

I have a spreadsheet that is a continuous log of data with dates and times. I am dynamically changing the footer based on the user's selection and including the selected date range for the report as part of the footer. This is all working fine.

What I would like to do is check to see if the user has selected "print selection" or "print active sheet" from the print dialog screen. That way I can disable the date range in the footer if the whole sheet is being printed. Currently, if the user is printing the whole sheet, the report footer only displays the date range from the current selection.

I am using VBA in Excel 2016.

 

[Jambi46n2]

Here's one way to go about it..

It prompts the user to select a range with their mouse, then makes that range the print area.

Sub specify_range_w_Mouse()

'Specify Range InputBox
Dim rRange As Range

On Error Resume Next

Application.DisplayAlerts = False

Set rRange = Application.InputBox(Prompt:= _
"Please Select Print Range With Your Mouse and Click OK.", _
Title:="SPECIFY RANGE", Type:=8)

On Error GoTo 0

Application.DisplayAlerts = True

If rRange Is Nothing Then
MsgBox ("Nothing was Selected. Please Try Again")
Exit Sub
Else
rRange.Select
ThisWorkbook.ActiveSheet.PageSetup.PrintArea = Selection.Address
End If

End Sub

 

[Dave_T_]

Jambi,

I am attempting to do this with no user interaction, other than the user selecting the cells they would like to print. This is already working. If, from the print dialog box, the user selects "print active sheet" I want to disable the footer editing code I have in place. I am looking for a way to view the selection, prior to printing, and after the user clicks the Print button. The code I have in place fires when the print button is clicked.

 

[Jaafar Tribak]

I too would like to know the answer to this question.

The following returns an empty string unless the page setup print area is set explicitly.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    MsgBox ActiveSheet.PageSetup.PrintArea
End Sub

 

[Dave_T_]

Also of note for this particular project, I do not want to change the print area setting as the print area is already being used to capture the report area of the spreadsheet. Other areas are being used for calculations, etc, that are not to be printed. The design is that a user can just select the rows that they would like printed, and the footer automatically updates to reflect the selection. I just want to be able to disable the footer update if the user chooses to print the entire report.

 

[Jaafar Tribak]

Also of note for this particular project, I do not want to change the print area setting as the print area is already being used to capture the report area of the spreadsheet. Other areas are being used for calculations, etc, that are not to be printed. The design is that a user can just select the rows that they would like printed, and the footer automatically updates to reflect the selection. I just want to be able to disable the footer update if the user chooses to print the entire report.

Which version of excel are you using ?

 

[Dave_T_]

I too would like to know the answer to this question.

The following returns an empty string unless the page setup print area is set explicitly.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    MsgBox ActiveSheet.PageSetup.PrintArea
End Sub

I use the active selection to accomplish my code, not the print area.

Dim r as Range
Set r = Selection
.
.
.
    dtStart = r.Cells(1, 1).Value
    dtEnd = r.Cells(r.Rows.Count, 1).Value
.
.
.
[\CODE]

Not sure if that helps your issue or not.

 

[Dave_T_]

Which version of excel are you using ?

Excel 2016 (In original post)

 

[Jaafar Tribak]

I use the active selection to accomplish my code, not the print area.

Dim r as Range
Set r = Selection
.
.
.
    dtStart = r.Cells(1, 1).Value
    dtEnd = r.Cells(r.Rows.Count, 1).Value
.
.
.
[\CODE]

Not sure if that helps your issue or not.[/QUOTE]

That doesn't tell if the user is printing the selection or the entire sheet.

I wonder if there is a way of retrieving the PrintArea via the Excel 4 Macro functions or some other way that we are probably missing otherwise I think the only way that might work is by resorting to the API and the MSAA .

I expected the excel object model would offer an easy way to retrieve what is being printed but this doesn't seem to be the case.

 

[Jaafar Tribak]

The following code worked for me in excel 2016.

Basically, the code starts a timer upon opening the workbook and continiously monitors the UI Print setup window to detect the type of printing being requested by the user.

In this example (see second code) , if the user choses to Print Selection, the printing is cancelled and the user is notified.

Workbook Demo

1- Code in a Standard Module:

Option Explicit

Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(0 To 7) As Byte
End Type

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function AccessibleObjectFromWindow Lib "Oleacc" (ByVal hwnd As LongPtr, ByVal dwId As Long, ByRef riid As GUID, ByRef ppvObject As Any) As Long
    Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal arg1 As LongPtr, ppacc As IAccessible, pvarChild As Variant) As Long
    Private Declare PtrSafe Function AccessibleChildren Lib "Oleacc" (ByVal paccContainer As Office.IAccessible, ByVal iChildStart As Long, ByVal cChildren As Long, ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
    Private Declare PtrSafe Function IIDFromString Lib "ole32.dll" (ByVal lpsz As LongPtr, lpiid As GUID) As Long
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Private Declare PtrSafe Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As LongPtr, ByVal wFlag As Long) As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function AccessibleObjectFromWindow Lib "Oleacc" (ByVal hwnd As Long, ByVal dwId As Long, ByRef riid As GUID, ByRef ppvObject As Any) As Long
    Private Declare Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    Private Declare Function AccessibleChildren Lib "Oleacc" (ByVal paccContainer As Office.IAccessible, ByVal iChildStart As Long, ByVal cChildren As Long, ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
    Private Declare Function IIDFromString Lib "ole32.dll" (ByVal lpsz As Long, lpiid As GUID) As Long
    Private 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
    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 GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As Long, ByVal wFlag As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private Const CHILDID_SELF = &H0&
Private Const S_OK = &H0&
Private Const OBJID_CLIENT = &HFFFFFFFC
Private Const IID_IAccessible = "{618736E0-3C3D-11CF-810C-00AA00389B71}"

Public sPrintArea As String


Public Sub StartTimer()
    SetTimer Application.hwnd, 0, 500, AddressOf TimerProc
End Sub

Public Sub StopTimer()
    KillTimer Application.hwnd, 0
End Sub


Private Sub TimerProc()

    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Dim hwnd As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Dim hwnd As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    
    Dim oAccNetUIHWND As IAccessible, oIAccPrintArea As IAccessible, vCopiesField As Variant
    Dim lLeft As Long, lTop As Long, lWidth As Long, lHeight As Long
    Dim IID As GUID
    
    On Error Resume Next
    
    hwnd = FindWindowEx(Application.hwnd, 0, "FullpageUIHost", vbNullString)
    hwnd = GetNextWindow(hwnd, 5): hwnd = GetNextWindow(hwnd, 5)
    Call IIDFromString(StrPtr(IID_IAccessible), IID)
    Call AccessibleObjectFromWindow(hwnd, OBJID_CLIENT, IID, oAccNetUIHWND)
    Call AccessibleChildren(oAccNetUIHWND, 0, 1, vCopiesField, 1)
    Call vCopiesField.accLocation(lLeft, lTop, lWidth, lHeight, CHILDID_SELF)
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Dim lngPtr As LongPtr
        Dim arPt(0 To 1) As Long
        arPt(0) = lLeft: arPt(1) = lTop + (lHeight * 15)
        lngPtr = arPt(1) * &H100000000^ Or arPt(0)
        Call AccessibleObjectFromPoint(lngPtr, oIAccPrintArea, 0)
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Call AccessibleObjectFromPoint(lLeft, lTop + (lHeight * 15), oIAccPrintArea, 0)
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    sPrintArea = oIAccPrintArea.accValue(CHILDID_SELF)

End Sub

2- Code in the ThisWorkbook Module:

Option Explicit

Private Sub Workbook_Activate()
    Call StartTimer
End Sub

Private Sub Workbook_Deactivate()
    Call StopTimer
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Debug.Print sPrintArea
    
    [COLOR=#008000][B]'run code here depending on the printarea as required[/B][/COLOR]

    If sPrintArea = "Print Selection" Then
        MsgBox sPrintArea & vbCrLf & vbCrLf & "Printing Cancelled."
        Cancel = True [B][COLOR=#008000]'<== Cancel printing if Print Selection.[/COLOR][/B]
    End If
End Sub

Unfortunately, the code uses a timer which is best avoided but I couldn't find another way.

In addition, the code relies on the specific UI layout of the Print setup window in excel 2016 so I am not sure it will work in other excel versions and it is also language dependent.