Jul 16 2019 08:29 AM
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
Jul 16 2019 09:11 AM
SolutionI 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
Jul 17 2019 06:12 AM
May 14 2023 04:21 AM
Jul 16 2019 09:11 AM
SolutionI 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