I'm using the following code to provide the user with a Print Preview/Print Settings page:
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!
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: