PDA

View Full Version : code for review - Excel "new window" substitute



TheAntiGates
07-06-2007, 05:04 PM
Corrections, suggestions, etc. sought for code below.

The impetus for this is when you go alt-W, then N ("New Window"). Alt-W,N is how you get additional "views" or "instances" of a workbook, which you can identify by a :2 (or higher) in the caption (e.g. COST2007.XLS:2). These New Windows (which are not the same as fresh new workbooks, which are instead from alt-F,N) are swell in that you can alt-tab between separate workbook pages, making you bodaciously faster in analyzing references. E.g., in the New Window you can double click that precedent or dependent arrow that points to another sheet, and alt-tab happily back and forth. (It would be even better if it preserved the active cell in the new window, so as soon as you created it, you could do that.) Sometimes I use this simply to alt tab between two cells on one sheet - which sounds crazy at first, but is monumentally faster than arrowing or mousing back and forth with a cell dozens of rows/columns away.

Now if you are someone who uses these you may be familiar with a side effect which can be nasty to devastating. There is a "feature" (at least through XL03) that the New Window loses zoom, window freeze, and other things (including active cell). The devastation occurs if you close :1 (the original workbook view) before :2, :3, :4, or anything besides :1 (any New Window). Guess what: you suddenly realize that you have 43 sheets with all their freeze settings gone - and I do mean permanently. Your only hope at that point is closing without saving, unless you don't mind manually re-entering everything for every sheet.

The code below is to make a :2 clone that keeps the salient window properties of :1, and thus won't leave you high and dry if you close :1 before saving. I think of this code as "a better alt-W,N" ... or at least I do until it gets torn a new one by greater minds. :cool: I already know that the first suggestion out of the box will be to toggle off screen updates to deflicker it, and the second will be to address the Great Depression of Comments. Anyway, I look forward to that, functional suggestions and failure reports if found. Thanks. :yes Sub pm_CloneWindow() 'Use this instead of alt-W-N. It gets about all properties *I* find essential :)
Dim sStartBook As String, sNewBook As String
sStartBook = ActiveWindow.Caption
ActiveWindow.NewWindow 'creates the clone "instance" or "view" (e.g. :2)
sNewBook = ActiveWindow.Caption
Windows(sStartBook).Activate
Call zzpm_cloneWindow(sNewBook) 'end up in the clone
End Sub
Sub pm_CloneWorkbook() 'do pm_CloneWindow for entire workbook
Dim sStartBook As String, sNewBook As String, iMyStartSheet As Long, i As Long

iMyStartSheet = ActiveSheet.Index
sStartBook = ActiveWindow.Caption
'If InStr(1, sStartBook, ":") < 1 Then sStartBook = sStartBook & ":1"
If Len(sStartBook) = 1 Or Right(sStartBook, 2) <> ":1" Then sStartBook = sStartBook & ":1"

ActiveWindow.NewWindow
sNewBook = ActiveWindow.Caption
For i = 1 To Worksheets.Count
Worksheets(i).Activate
Windows(sStartBook).Activate
Worksheets(i).Activate
Call zzpm_cloneWindow(sNewBook)
Next i
Windows(sStartBook).Activate
Worksheets(iMyStartSheet).Activate 'in original
Windows(sNewBook).Activate 'end up in the clone
Worksheets(iMyStartSheet).Activate ' in clone
End Sub
Sub zzpm_cloneWindow(sNewBook As String)
Dim iMyNumProperties(10) As Long, bMyBoolProperties(2) As Boolean
'Debug.Print "SplitVertical ", ActiveWindow.SplitVertical 'etc.
iMyNumProperties(1) = ActiveWindow.View
iMyNumProperties(2) = ActiveWindow.Zoom

iMyNumProperties(5) = ActiveWindow.SplitRow
iMyNumProperties(6) = ActiveWindow.SplitColumn
bMyBoolProperties(1) = ActiveWindow.Split
bMyBoolProperties(2) = ActiveWindow.FreezePanes
iMyNumProperties(7) = ActiveWindow.ScrollRow
iMyNumProperties(8) = ActiveWindow.ScrollColumn
iMyNumProperties(9) = ActiveCell.Row
iMyNumProperties(10) = ActiveCell.Column

Windows(sNewBook).Activate
ActiveWindow.View = iMyNumProperties(1)
ActiveWindow.Zoom = iMyNumProperties(2)

ActiveWindow.SplitRow = iMyNumProperties(5)
ActiveWindow.SplitColumn = iMyNumProperties(6)
ActiveWindow.Split = bMyBoolProperties(1) 'Do these AFTER setting split columns, and BEFORE setting scroll !!
ActiveWindow.FreezePanes = bMyBoolProperties(2) 'Do these AFTER setting split columns, and BEFORE setting scroll !!
ActiveWindow.ScrollRow = iMyNumProperties(7)
ActiveWindow.ScrollColumn = iMyNumProperties(8)
Range("A1").Offset(iMyNumProperties(9) - 1, iMyNumProperties(10) - 1).Activate
End Sub
'pm prefix for personal.xls macros; zz....'s are only callable from code

TheAntiGates
07-06-2007, 06:30 PM
I have noticed that the first sub should have the IF, which could be shortened to
If Right(sStartBook, 2) <> ":1" Then sStartBook = sStartBook & ":1"