I hide my ribbon, so users can't change the margins from there.
I am having absolutely no luck with Private Sub Workbook_BeforePrint(Cancel As Boolean)
My best solution, so far, is the code below, which sets the margins when the user clicks the Print CommandButton.
But it still doesn't prevent the user from changing the margins once they are in Print Preview.
Can Page Setup be greyed out?
Is there a way that even if the change it, it immediately reverts back to my default settings?
Thanks
Code
Private Sub CommandButton1_Click()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.17)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.25)
.Orientation = xlLandscape
End With
ActiveWindow.SelectedSheets.PrintPreview ' preview
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Dim rng As Range
Set rng = ActiveSheet.Range("B2:B4800")
For j = rng.Column To (rng.Column + rng.Columns.Count - 1)
For i = rng.Row To (rng.Row + rng.Rows.Count - 1)
If Cells(i, j).Locked = False And Len(Trim(Cells(i, j).Value)) = 0 Then
Cells(i, j).Select
Exit For
End If
Next i
Next j
End Sub