I often have found the Freeze Panes option on shared workbooks having been changed to a setting I don't like.
To correct this I wrote the code below wich could probably also help others.
It uses the getHomeCellAddress() function which finds the cell on which the Freeze Panes option is currently set.
This code works on the active sheet and resets the Freeze Panes top left cell to D5, with the first two columns hidden (ajust accordingly)
Code
Sub resetFreezePanes(myRange As String)
' Bart Hoeksel, Nedcom IT, 20160614
' Uses Function getHomeCellAddress
If Not getHomeCellAddress = myRange Then
ActiveWindow.FreezePanes = False
' these two lines only if you want to hide the first (two) columns '
Range("A" & Range(myRange).Row).Select
ActiveWindow.SmallScroll ToRight:=2
' end hide columns ----------------------------------------------- '
Range(myRange).Select
ActiveWindow.FreezePanes = True
End If
End Sub
Function getHomeCellAddress() As String
' Bart Hoeksel, Nedcom IT, 20160614
getHomeCellAddress = Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn).Address(0, 0)
End Function
Display More
You could eventually call the resetFreezePanes procedure from the sheet's Activate event:
or call the code from a separate procedure (f.i. to use on a ribbon button):
I hope this will be useful to others, you may use it freely.
Bart Hoeksel,
Nedcom IT