Force print settings to 'Print Active Sheets' (Excel 2016)

Chao§pawN

New Member
Joined
Jul 2, 2015
Messages
10
I'm using the following code to provide the user with a Print Preview/Print Settings page:

Code:
Private Sub PrintPage1Button()
'Set up Print Preview
With ActiveSheet.PageSetup
    .PrintArea = "B1:T56"
    .Orientation = xlLandscape
    .LeftMargin = Application.CentimetersToPoints(0.5)
    .RightMargin = Application.CentimetersToPoints(4.5)
    .TopMargin = Application.CentimetersToPoints(1.5)
    .BottomMargin = Application.CentimetersToPoints(1.5)
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With
'Open Print Preview
Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")
End Sub

This code works fine, however there is a slight problem I can't seem to fix.
The first time the button is pressed it sets the PrintArea correctly, and sets the Print Setting to 'Print Active Sheets', which it should be on. However, if the user changes this to something else like 'Print Selection' and cancels the print, the next time the button is pressed the Print Setting will stay on 'Print Selection'.
Is there a way to force this setting to 'Print Active Sheets' each time the button is pressed?

Note: All the other settings such as 'Orientation' do not behave this way and are always set to the correct setting when the button is pressed, regardless of what the user selected manually.

Thanks very much!
 
Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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
Back
Top