Right now I’m in the middle of closing out the month. That’s accounting lingo for reconciling the accounts and preparing financial statements. Most of my reconciling is done in one workbook with a worksheet for each account. The last sheet, titled AJE, is a list of my adjusting journal entries. (Oh boy! This sounds like an accounting lesson. Fun!)
Many of my entries are the same from month to month, except for the dollar amounts, so I have the skeleton of the entry on the AJE sheet. The reconciliation sheet more or less creates the entry, or at a minimum, has the information to create the entry. I’m always switching back and forth between the AJE sheet and the reconciliation sheet. I can’t seem to remember three numbers long enough to type the entry, so I wanted to make the AJE sheet active but still be able to see the reconciliation sheet.
Now I can type in my AJE and still see the numbers I’m supposed to be typing. When I’m done, Cntl+F4 to close the window and Cntl+F10 to re-maximize the main window. Here’s the code:
Dim wnParent As Window
Dim wnChild As Window
‘make a reference to be used later
Set wnParent = ActiveWindow
‘create a new window that will be the smaller window
wnParent.NewWindow
Set wnChild = ActiveWindow
‘change the size of the windows
With Application
wnChild.WindowState = xlNormal
wnChild.Top = .Top + (.Height * 0.2)
wnChild.Height = .Height * 0.4
wnChild.Left = .Left + .Width * 0.6
wnChild.Width = .Width * 0.35
wnParent.Top = 1
wnParent.Left = 1
wnParent.Height = .Height * 0.85
wnParent.Width = .Width * 0.95
End With
End Sub
Hi Dick,
This is pretty cool, but if you have say 3 or more windows open and you only want to look at two, what would you do?
Ron
Nice trick:
Would be even better if we could fix it to stay “always on top”. That way you could type into the main sheet without losing sight of the floating window.
OK, of course you could type into the child window but that just “feels” wrong :o)
M
Ron: Are you saying you have three windows of the same workbook already open and you want to arrange two of them?
M: That’s exactly what I do and I agree that it feels wrong. I wonder how we could get something to be always on top. I’d even be satisfied with a picture of a selected range, i.e. I select range, take a “picture”, and that picture is always on top.
Dick,
Play around with a modeless userform, the Spreadsheet control of the OWC-package and the standard events of the sheet object :)
Dim wbBook As Workbook
Dim wsSource As Worksheet
Dim rnData As Range
Set wbBook = ActiveWorkbook
Set wsSource = wbBook.Worksheets(3)
With wsSource
Set rnData = .UsedRange
End With
rnData.Copy
With Me.Spreadsheet1
.Range(“A1?).Paste
.ViewOnlyMode = True
End With
Application.CutCopyMode = False
Kind regards,
Dennis
Dick,
<Iíd even be satisfied with a picture of a selected range, i.e. I select range, take a “picture”, and that picture is always on top.>
Snapshot of an Excel Range
Cheers,
Rob