Forum Discussion
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 Removing the View Tab from the Ribbon but that only seems to apply to my workbook.
I need to Freeze the top two panes, then make it so others who access the shared file are UNABLE to Unfreeze the panes. The Protect Workbook (structure) does not seem to work. I'd appreciate the help.
thanks
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
3 Replies
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- Roger Govier
I have tried the above script, changing the address to ("A2") since I only need the fist pane to be kept frozen, but for some reason this doesn't seem to work in Excel 365, or is there something I am missing?
Thank you - Thank you Roger, that worked perfectly!!!!!!!
