Little Windows

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.

two windows of the same workbook

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:

Sub CreateWindow()
   
    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
Posted in Uncategorized

6 thoughts on “Little Windows

  1. 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

  2. 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

  3. 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.

  4. 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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *