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.
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
Best Response confirmed by
Paneross (Occasional Contributor)