Forum Discussion

Paneross's avatar
Paneross
Copper Contributor
Jul 16, 2019
Solved

Prevent Unfreezing in a Shared Workbook Excel 2016

Good Morning,   I have a Shared Workbook that I need to prevent others users from being able to Unfreeze Panes. I've already Protected the Sheet to and Locked specific cells, I've also tried Removi...
  • Roger Govier's avatar
    Jul 16, 2019

    Paneross 

    I think the only way is to use some VBA.

    The following code needs to be copied into the sheet module. Right click on sheet tab, and choose View Code. In the white pane that appears in the VB Editor, paste the code, then press Alt+F11 to return to Excel.

    Remember to save the file as a ,xlsm or .xlsb file, otherwise the code won't be saved.

     

    Basically the code gets triggered whenever the user moves to another cell on the sheet. It looks to see if Freeze Panes is active, and if it isn't it sets it at the location you choose and returns the user to the cell they had selected.

     

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cellreturn As String
    cellreturn = ActiveCell.Address
    Application.EnableEvents = False
    If Not ActiveWindow.FreezePanes Then
    ActiveSheet.Range("A3").Select ' change address to where you want panes frozen
    ActiveWindow.FreezePanes = True
    ActiveSheet.Range(cellreturn).Select
    End If
    Application.EnableEvents = True
    End Sub

     

     

Resources