Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 9th 16, 11:02 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
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

  #2   Report Post  
Old December 9th 16, 11:24 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,536
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   Report Post  
Old December 9th 16, 02:37 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
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   Report Post  
Old December 9th 16, 02:43 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
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 <[email protected]
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   Report Post  
Old December 9th 16, 03:49 PM
Banned
 
First recorded activity by ExcelBanter: Dec 2016
Posts: 5
Default

Hóng
_________________
visit

https://t.co/Xs7fiPD6MB


  #6   Report Post  
Old December 10th 16, 02:51 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
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   Report Post  
Old December 10th 16, 12:26 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
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 <[email protected]
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   Report Post  
Old December 10th 16, 12:58 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
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   Report Post  
Old December 11th 16, 01:07 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
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   Report Post  
Old December 11th 16, 01:42 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 03:07 AM
Property window - VBA Editor Gordon[_15_] Excel Programming 1 November 27th 06 07:47 PM
FreezePanes from VBA? G Lykos Excel Programming 8 September 23rd 06 04:06 PM
freezepanes Rob Excel Programming 1 January 6th 06 05:17 PM


All times are GMT +1. The time now is 06:54 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017