Prevent Users from Unfreezing Panes
Posted by JAF on August 22, 2001 7:42 AM
Hiya
I have a workbook set up that has some Workbook_Open code that restricts the scroll area on a particular worksheet to range B2:AB5101. I have the panes frozen at cell B2.
If the user unfreezes the panes and then scrolls down or right so the the headers disappear off the screen, then they cannot scroll left or up far enough to restore the headers on row 1 or the data in column A.
I have set up a "restore_headers" macro so that if they do manage to "break it" thy they can restore it without having to call me up to do it for them (code is password protected).
Is there any way of stopping the user from unfreezing the panes in the first place? I suppose I could create a custom menu, but I don't want to go to all that inconvenience for a fairly simple workbook.
Is there some way of identifying the unfreeze panes event and immediately undoing it (or running my "restore_headers" macro to reset the panes in the correct place???
JAF.
Posted by Ivan F Moala on August 22, 2001 8:54 PM
Jaff
Something like this may help
Use change selection event to test if Freeze pane
is True.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastCell As String
If Not ActiveWindow.FreezePanes Then
Application.EnableEvents = False
LastCell = Target.Address
Application.Goto Range("A1"), True
Range("B2").Select
ActiveWindow.FreezePanes = True
Application.EnableEvents = True
Range(LastCell).Select
End If
End Sub
