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?
- 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