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 maint...
Macros & VBA
Need Help
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