View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Walter Briscoe Walter Briscoe is offline
external usenet poster
 
Posts: 279
Default 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