Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Freezepanes | Excel Programming | |||
Resize Property Window in VBA | Excel Programming | |||
Property window - VBA Editor | Excel Programming | |||
FreezePanes from VBA? | Excel Programming | |||
freezepanes | Excel Programming |