FreezePanes is a window property
I run Excel 2003.
I have a particular workbook, consisting of 3 sheets.
Sometimes, I save it with one displayed sheet and sometimes with more
than one.
Each sheet had FreezePanes set.
I sometimes lose FreezePanes, when I switch from sheet to sheet.
After some considerable time being irritated with this, I now understand
it.
I wrote my first event procedure - I don't count auto_open.
1) Enter the Visual Basic Editor with Alt+F11 or otherwise.
2) Ctrl+R puts you in the Properties pane. Select ThisWorkbook.
3) The top line of the code pane consists of (General) and (Procedures)
4) Scroll down from (General) to WorkBook. That causes a default event
handler structure to be written. That can be zapped.
5) Scroll (Procedures) down to SheetActivate. That causes a Private Sub
Workbook_SheetActivate outline to be written.
6) Complete that sub and test it.
Where are events enumerated?
I find this code does what I want.
Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
' Put FreezePanes at [2,2]
Dim here As Range
Set here = ActiveCell
Cells(1, 1).Activate
With ActiveWindow
.SplitColumn = 1
.SplitRow = 1
.FreezePanes = True
End With
here.Activate
End Sub
Without messing about with ActiveCell, I found FreezePanes was set
relative to what happened to be in the window.
Can I simplify?
There is some screen flicker. I could use Application.ScreenUpdating to
avoid that.
--
Walter Briscoe
|