Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109739

FreezePanes is a window property

Show all headers | View raw

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

Back to microsoft.public.excel.programming | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

FreezePanes is a window property Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-12-09 10:02 +0000
  Re: FreezePanes is a window property Claus Busch <claus_busch@t-online.de> - 2016-12-09 11:24 +0100
    Re: FreezePanes is a window property Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-12-09 13:43 +0000
      Re: FreezePanes is a window property Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-12-10 11:26 +0000
  Re: FreezePanes is a window property Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-12-09 13:37 +0000
    Re: FreezePanes is a window property GS <gs@v.invalid> - 2016-12-09 20:51 -0500
      Re: FreezePanes is a window property Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-12-10 11:58 +0000
        Re: FreezePanes is a window property GS <gs@v.invalid> - 2016-12-10 19:07 -0500
          Re: FreezePanes is a window property GS <gs@v.invalid> - 2016-12-10 19:42 -0500
  Re: FreezePanes is a window property DungSports <DungSports@gmail.com> - 2016-12-09 14:49 +0000

csiph-web