A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

FreezePanes is a window property



 
 
Thread Tools Display Modes
  #1  
Old December 9th 16, 10:02 AM posted to microsoft.public.excel.programming
Walter Briscoe
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
Ads
  #2  
Old December 9th 16, 10:24 AM posted to microsoft.public.excel.programming
Claus Busch
external usenet poster
 
Posts: 3,446
Default FreezePanes is a window property

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
  #3  
Old December 9th 16, 01:37 PM posted to microsoft.public.excel.programming
Walter Briscoe
external usenet poster
 
Posts: 279
Default FreezePanes is a window property

In message > of Fri, 9 Dec 2016
10:02:06 in microsoft.public.excel.programming, Walter Briscoe
> 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/lib....tools.excel.w
orksheet_events.aspx>
I also found
<https://msdn.microsoft.com/en-us/lib....tools.excel.w
orkbook_events.aspx>
I can probably find others.
--
Walter Briscoe
  #4  
Old December 9th 16, 01:43 PM posted to microsoft.public.excel.programming
Walter Briscoe
external usenet poster
 
Posts: 279
Default FreezePanes is a window property

In message > 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
  #5  
Old December 9th 16, 02:49 PM
DungSports DungSports is offline
Banned
 
First recorded activity by ExcelBanter: Dec 2016
Posts: 5
Default

Hóng
_________________
visit

https://t.co/Xs7fiPD6MB
  #6  
Old December 10th 16, 01:51 AM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 673
Default FreezePanes is a window property

> In message > of Fri, 9 Dec 2016
> 10:02:06 in microsoft.public.excel.programming, Walter Briscoe
> > 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/lib....tools.excel.w
> orksheet_events.aspx>
> I also found
> <https://msdn.microsoft.com/en-us/lib....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
  #7  
Old December 10th 16, 11:26 AM posted to microsoft.public.excel.programming
Walter Briscoe
external usenet poster
 
Posts: 279
Default FreezePanes is a window property

In message > of Fri, 9 Dec 2016
13:43:20 in microsoft.public.excel.programming, Walter Briscoe
> writes
>In message > 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
  #8  
Old December 10th 16, 11:58 AM posted to microsoft.public.excel.programming
Walter Briscoe
external usenet poster
 
Posts: 279
Default FreezePanes is a window property

In message > of Fri, 9 Dec 2016 20:51:31 in
microsoft.public.excel.programming, GS > writes
>> In message > of Fri, 9 Dec 2016
>> 10:02:06 in microsoft.public.excel.programming, Walter Briscoe
>> > 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/lib....tools.excel.w
>> orksheet_events.aspx>
>> I also found
>> <https://msdn.microsoft.com/en-us/lib....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/lib...ffice.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
  #9  
Old December 11th 16, 12:07 AM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 673
Default FreezePanes is a window property

> 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
  #10  
Old December 11th 16, 12:42 AM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 673
Default FreezePanes is a window property

> 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Freezepanes Russ Excel Programming 6 September 8th 08 09:17 PM
Resize Property Window in VBA Gordon[_15_] Excel Programming 0 November 28th 06 02:07 AM
Property window - VBA Editor Gordon[_15_] Excel Programming 1 November 27th 06 06:47 PM
FreezePanes from VBA? G Lykos Excel Programming 8 September 23rd 06 04:06 PM
freezepanes Rob Excel Programming 1 January 6th 06 04:17 PM


All times are GMT +1. The time now is 11:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.