How to set Fix Print area.

[SamarthSalunkhe]

Hello Everyone,

I have set the print area in the sheet using VBA code, but I realise that the user can modify it using "Set Print Area" at the time of printing the page.

can someone help me to prevent this, as I don't want modification in the Print area to avid misuse of macro?

Currently, I'm using the below code for the print area.

Private Sub Worksheet_Activate()

Sheet1.PageSetup.PrintArea = "$B$2:$D$39"

End Sub

 

[ClaireS]

Attach your macro to the BeforePrint event instead of (or as well as) the Activate event. Then it will reset the print area at the time of print if your user has been fiddling with it.

 

[SamarthSalunkhe]

Attach your macro to the BeforePrint event instead of (or as well as) the Activate event. Then it will reset the print area at the time of print if your user has been fiddling with it.

sorry i didn't get you, can you please elaborate or provide code.

sorry but i'm beginner in VBA.

 

[ClaireS]

In the VBA Editor, select ThisWorkbook in the Project pane, then at the top of the code window, where it says (General), click on the drop-down and select Workbook. Then to the right (where it says Open), instead choose BeforePrint. Then place your code into the subroutine:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Sheet1.PageSetup.PrintArea = "$B$2:$D$39"
    
End Sub

You are simply fixing the print area at the time the sheet is printed, rather than at the time of activating the worksheet. If you still want the print area set at the start, leave your current code in the sheet's Activate event (where it currently is).

 

[SamarthSalunkhe]

In the VBA Editor, select ThisWorkbook in the Project pane, then at the top of the code window, where it says (General), click on the drop-down and select Workbook. Then to the right (where it says Open), instead choose BeforePrint. Then place your code into the subroutine:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Sheet1.PageSetup.PrintArea = "$B$2:$D$39"
   
End Sub

You are simply fixing the print area at the time the sheet is printed, rather than at the time of activating the worksheet. If you still want the print area set at the start, leave your current code in the sheet's Activate event (where it currently is).

Thank you so much ChaireS, It works Perfectly. ?

 

[ClaireS]

Happy to help