Apr 05 2018
01:57 PM
- last edited on
Jul 25 2018
09:55 AM
by
TechCommunityAP
Apr 05 2018
01:57 PM
- last edited on
Jul 25 2018
09:55 AM
by
TechCommunityAP
I have created a workbook which contains a number of worksheets as well as macros. I have headers which incorporate macros, and I have frozen panes in the worksheets to allow for scrolling but maintaining column headers etc.
This is great in Normal and Page Break Preview. However, if a user selects Page Layout view, the panes become unfrozen, and it is very easy for a user to edit the header (once just click and delete very easily since now the header is in view).
Is there a VBA code that can disable a change in Workbook View? I found a code that would switch to Normal View any time a user clicked on a new cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveWindow.View = xlNormalView End Sub
However, a user could still click on the header and delete it without the view switching back. One would have to actually click in a cell for the view to change back to Normal View.
As usual, this is about trying to lock down the spreadsheet for less advanced users who can make some major errors.
Apr 05 2018 02:03 PM
Apr 05 2018 10:11 PM
Hi Grace S,
You can depend on this code instead:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "Hello World" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With ActiveWindow.View = xlNormalView End Sub
The idea of this code is to save and set the default texts for the headers and footers in the code itself so that if the user changes them later, they will return directly to their default state.
But this may slow down the workbook as it will start every time the cell selection changes, so I advise you to decrease it as much as possible using other reliable events such as:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) With TargetSheet.PageSetup .LeftHeader = "" .CenterHeader = "Hello World" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With ActiveWindow.View = xlNormalView End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) With TargetSheet.PageSetup .LeftHeader = "" .CenterHeader = "Hello World" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With ActiveWindow.View = xlNormalView End Sub Private Sub Workbook_BeforePrint(Cancel As Boolean) With TargetSheet.PageSetup .LeftHeader = "" .CenterHeader = "Hello World" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With Sheet.Activate ActiveWindow.View = xlNormalView End Sub
Notes
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) For Each Sheet In Sheets With Sheet.PageSetup .LeftHeader = "" .CenterHeader = "Hello World" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With Next ActiveWindow.View = xlNormalView End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) For Each Sheet In Sheets With Sheet.PageSetup .LeftHeader = "" .CenterHeader = "Hello World" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With Next ActiveWindow.View = xlNormalView End Sub Private Sub Workbook_BeforePrint(Cancel As Boolean) For Each Sheet In Sheets With Sheet.PageSetup .LeftHeader = "" .CenterHeader = "Hello World" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" End With Next ActiveWindow.View = xlNormalView End Sub
Please find all this in the attached workbook.
Hope that helps
Haytham