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

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

FreezePanes is a window property

Back to article view | Back to microsoft.public.excel.programming

Contents

  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

#109739 — 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

[toc] | [next] | [standalone]

#109740

Hi Walter,

Am Fri, 9 Dec 2016 10:02:06 +0000 schrieb Walter Briscoe:

>     With ActiveWindow
>         .SplitColumn = 1
>         .SplitRow = 1
>         .FreezePanes = True
>     End With

> Can I simplify?

with SplitColumn =1 you freeze column A (1) and with 
SplitRow=1 you freeze row 1. There is no need to select a cell.
Try for all sheets:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With ActiveWindow
    .SplitColumn = 1
    .SplitRow = 1
    .FreezePanes = True
End With
End Sub


Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [next] | [standalone]

#109742

In message <o2e0lq$luq$1@dont-email.me> of Fri, 9 Dec 2016 11:24:09 in
microsoft.public.excel.programming, Claus Busch <claus_busch@t-
online.de> writes
>Hi Walter,
>
>Am Fri, 9 Dec 2016 10:02:06 +0000 schrieb Walter Briscoe:
>
>>     With ActiveWindow
>>         .SplitColumn = 1
>>         .SplitRow = 1
>>         .FreezePanes = True
>>     End With
>
>> Can I simplify?
>
>with SplitColumn =1 you freeze column A (1) and with
>SplitRow=1 you freeze row 1. There is no need to select a cell.
>Try for all sheets:
>
>Private Sub Workbook_SheetActivate(ByVal Sh As Object)
>With ActiveWindow
>    .SplitColumn = 1
>    .SplitRow = 1
>    .FreezePanes = True
>End With
>End Sub
Thanks, Claus. I agree.
I don't know how I got it wrong. ;)
-- 
Walter Briscoe

[toc] | [prev] | [next] | [standalone]

#109750

In message <y4ie0uB4TrSYFweC@freenetname.co.uk> of Fri, 9 Dec 2016
13:43:20 in microsoft.public.excel.programming, Walter Briscoe
<wbriscoe@nospam.demon.co.uk> writes
>In message <o2e0lq$luq$1@dont-email.me> of Fri, 9 Dec 2016 11:24:09 in
>microsoft.public.excel.programming, Claus Busch <claus_busch@t-
>online.de> writes
>>Hi Walter,
>>
>>Am Fri, 9 Dec 2016 10:02:06 +0000 schrieb Walter Briscoe:
>>
>>>     With ActiveWindow
>>>         .SplitColumn = 1
>>>         .SplitRow = 1
>>>         .FreezePanes = True
>>>     End With
>>
>>> Can I simplify?
>>
>>with SplitColumn =1 you freeze column A (1) and with
>>SplitRow=1 you freeze row 1. There is no need to select a cell.
>>Try for all sheets:
>>
>>Private Sub Workbook_SheetActivate(ByVal Sh As Object)
>>With ActiveWindow
>>    .SplitColumn = 1
>>    .SplitRow = 1
>>    .FreezePanes = True
>>End With
>>End Sub
>Thanks, Claus. I agree.
>I don't know how I got it wrong. ;)

I now think I was right.
Set the window so the top left hand cell shows [8,1}
Transfer focus to a second sheet
Return to first sheet and Run
        With ActiveWindow
            .SplitColumn = 1
            .SplitRow = 1
            .FreezePanes = True
        End With

SplitColumn and SplitRow are relative to what the window shows.
We want data relative to the Worksheet displayed in the Window.
-- 
Walter Briscoe

[toc] | [prev] | [next] | [standalone]

#109741

In message <FcbTtsfeEoSYFwLj@freenetname.co.uk> of Fri, 9 Dec 2016
10:02:06 in microsoft.public.excel.programming, Walter Briscoe
<wbriscoe@nospam.demon.co.uk> writes
>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.

[snip]

>Where are events enumerated?

By googling excel event name descriptions, I found worksheet events
enumerated and described in
<https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.w
orksheet_events.aspx>
I also found
<https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.w
orkbook_events.aspx>
I can probably find others.
-- 
Walter Briscoe

[toc] | [prev] | [next] | [standalone]

#109746

> In message <FcbTtsfeEoSYFwLj@freenetname.co.uk> of Fri, 9 Dec 2016
> 10:02:06 in microsoft.public.excel.programming, Walter Briscoe
> <wbriscoe@nospam.demon.co.uk> writes
>> 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.
>
> [snip]
>
>> Where are events enumerated?
>
> By googling excel event name descriptions, I found worksheet events
> enumerated and described in
> <https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.w
> orksheet_events.aspx>
> I also found
> <https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.w
> orkbook_events.aspx>
> I can probably find others.

Note that you can manage sheet events in ThisWorkbook as you've done 
here, but that it can also be done with an events handler class. This 
might be a better alternative since all code in ThisWorkbook is subject 
to not work correctly if the workbook becomes corrupted for any reason.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#109751

In message <o2fn0h$vvl$1@dont-email.me> of Fri, 9 Dec 2016 20:51:31 in
microsoft.public.excel.programming, GS <gs@v.invalid> writes
>> In message <FcbTtsfeEoSYFwLj@freenetname.co.uk> of Fri, 9 Dec 2016
>> 10:02:06 in microsoft.public.excel.programming, Walter Briscoe
>> <wbriscoe@nospam.demon.co.uk> writes
>>> 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.
>>
>> [snip]
>>
>>> Where are events enumerated?
>>
>> By googling excel event name descriptions, I found worksheet events
>> enumerated and described in
>> <https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.w
>> orksheet_events.aspx>
>> I also found
>> <https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.w
>> orkbook_events.aspx>
>> I can probably find others.
>
>Note that you can manage sheet events in ThisWorkbook as you've done
>here, but that it can also be done with an events handler class. This
>might be a better alternative since all code in ThisWorkbook is subject
>to not work correctly if the workbook becomes corrupted for any reason.
>

Where would I put an events handler class to escape corruption?
I find Microsoft's standard events are not raised as I expect.
e.g. <https://msdn.microsoft.com/en-us/library/microsoft.office.tools.ex
cel.workbook_events.aspx> says:
SheetActivate  Occurs when any sheet is activated.

When a workbook is opened, "Open  Occurs when the workbook is opened.",
but SheetActivate is not raised, even if a sheet is shown on screen.

My only experience of classes is copies I have made in the last couple
of weeks.
-- 
Walter Briscoe

[toc] | [prev] | [next] | [standalone]

#109752

> Wherere would I put an events handler class to escape corruption?
> I find MiMicrosoft's standard events are not raised as I expect.
> e.g. 
> <https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook_events.aspx> 
> says:
> SheetActivate  Occurs when any sheet is activated.
>
> When a workbook is opened, "Open  Occurs when the workbook is 
> opened.",
> but ShSheetActivate is not raised, even if a sheet is shown on 
> screen.
>
> My only experience of classes is copies I have made in the last 
> couple
> of weeks.

This would be a class component in your project. Given the amount of 
programming you've done over the years (some of it fairly complex), I 
recommend you get a copy of *Professional Excel Development* if you 
don't already have it...

  http://www.appspro.com/Books/Books.htm

..because it describes in detail how to put together a global events 
handler for your projects. Note that this book (and mentoring by its 
author Rob Bovey) is the prime source for my learning and growing my 
Excel development skills.

Note that there are quirks when developing with global event handlers 
that are easily managed via a switch that allows you to turn event 
handling on or off at any time. The most noticed quirk is global vars 
losing their values/refs.

I use a standard module named 'm_OpenCclose' to handle startup, 
shutdown, and global defs/initialization. It includes the autorun 
macros 'Auto_Open' and 'Auto_Close' to replace the workbook 'Open' and 
'BeforeClose' events. It's also where I def all globals and their 
initialization via sub 'InitGlobals'. (This also serves to 
re-initialize during runtime if required)

I use a global event handler named 'cAppEvents' to manage all other 
events, which gets switched on at startup. Event handling switches are 
'StartEventHandling', 'StopEventHandling' and 'ResetEventHandling' 
which are located in m_OpenClose.

Setting all this up is a 'per project' thing mostly, and so I start a 
new project with a blank template and go from there.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#109753

> Event handling switches are 'StartEventHandling', 'StopEventHandling' 
> and 'ResetEventHandling' which are located in m_OpenClose.

I've mis-info'd: The event handler procs are named 
'ResetEventHandling', DisableEventHandling' and 'HandleEvents', and are 
now stored in the module named 'mWorkspace'.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#109743

Hóng
_________________
'*visit*' (https://t.co/QXKkIQ9sVQ)

https://t.co/Xs7fiPD6MB




-- 
DungSports

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web