Forum Discussion

Grace S's avatar
Grace S
Copper Contributor
Apr 05, 2018

VBA code to disable change in workbook view?

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.

Macros & VBA
Need Help

2 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Apr 06, 2018

    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:

    • Workbook_BeforeSave
    • Workbook_BeforeClose
    • Workbook_BeforePrint
    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

    • You have to put the above codes in the ThisWorkbook code scope.
    • The above codes are applied to a specific sheet, and its interior name is (TargetSheet), but its visible name is Sheet1.

     

    • You can apply it to each sheet in the workbook as follow:
    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

Resources