Tweaking View Side by Side

I use View Side by Side a lot. That means I spend a bit of time fiddling with the two windows because I don’t always want each to take up half the space. I often make one smaller and then adjust the second to have it fill the rest of the screen. This weekend I set about tweaking View Side by Side to adjust one window when the other is resized.

Along the way I learned a few interesting things. The most important to this code was figuring out how tell if View Side by Side is activated. I also noticed some issues with View Side by Side in Excel 2013.

Below is a screenshot of what happens when you resize a window in View Side by Side. You can see that it needs to be followed up with some more fiddling to snug the lower window up against the upper one:

regular View Side by Side

The code I came up with is in an application-level event. It uses a function to test whether View Side by Side is active. It’s designed to work in Excel 2010 (and 2007, although untested) as well as 2013. The 2013 tweaks were a little trickier, because Application.Height returned the same thing as Window.Height in 2013. This means the code needs to store the lower windows top location before resizing it.

You’ll also note that I used On Error Resume Next more freely than normally. After all we’re just resizing windows here, and it’s easier than testing for odd things like heights less than zero:

Public WithEvents app As Excel.Application

'application-level event in a class module
Private Sub app_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
Dim UpperWindow As Excel.Window
Dim LowerWindow As Excel.Window
Dim OldLowerWindowTop As Double
Dim LowerWindowChange As Double

'See function below
If SideBySideOn Then
    With Application
        .EnableEvents = False
        'assign the two windows,
        'based on which is uppermost
        If .Windows(1).Top <= .Windows(2).Top Then
            Set UpperWindow = .Windows(1)
            Set LowerWindow = .Windows(2)
        Else
            Set UpperWindow = .Windows(2)
            Set LowerWindow = .Windows(1)
        End If

        'easy way to deal with heights less than zero, etc.
        On Error Resume Next
        'for Excel 2010 could just use Application.Height
        'but in Excel 2013 each window is its own Application
        If ActiveWindow.Caption = UpperWindow.Caption Then
            OldLowerWindowTop = LowerWindow.Top
            LowerWindow.Top = UpperWindow.Top + UpperWindow.Height
            LowerWindowChange = LowerWindow.Top - OldLowerWindowTop
            LowerWindow.Width = UpperWindow.Width
            LowerWindow.Height = LowerWindow.Height - LowerWindowChange
        Else
            UpperWindow.Height = LowerWindow.Top
            UpperWindow.Width = LowerWindow.Width
        End If
        On Error GoTo 0
        .EnableEvents = True
    End With
End If
End Sub

This code only responds to resizes involving the two horizontal borders in the middle, as well as the right-hand borders. So, if you drag the top border of the upper window nothing adjusts.

Application-Level Event

The sample workbook sets an application-level class in its Workbook_Open event. I’ve posted on this before and there’s lots of good info out there, like this Chip Pearson page.

How Do You Tell if View Side by Side is Active?

That’s an interesting question! Here’s what I tried:

  1. First, I looked for some type of setting, like Application.ViewSideBySideEnabled. I can’t find one. Instead there’s two methods: CompareSideBySideWith and the rather drastic-sounding BreakSideBySide.
  2. It would be nice if you could just get the state of the Ribbon’s View Side by Side button. That would tell you whether it’s active or not. But I don’t know of any way to do this.
  3. You can, however, check the state of Excel 2003-style command buttons. So I tried looking at
    Application.CommandBars(1).Controls("Window").Controls("Compare Side By Side With").State

    to check whether it’s pressed. It turns out that control – ID 7698 – has no state. Instead it switches captions from “Compare Side by Side With” (followed by a workbook name if there’s only one choice) to “Close Side By Side.”

  4. Based on #3 I considered getting the state by checking whether the first five letters are “Close” or “Compa.” But of course that wouldn’t work for folks with non-English Excel versions, and I generally shy away from using labels when I can use ID’s.
  5. So, finally I decided to use the state of the “Reset Window Position” button. This button is only enabled when View Side by Side is active.

This leads to a one-line function that checks that button’s status using its ID of 7874. There is one final wrinkle, which is that this button doesn’t appear by default in the Excel 2003 “Window” toolbar. By default it lives only in the “Add Command” toolbar which was the one that appeared in 2003 when you modified a toolbar. No problem. We can find number 7874 wherever it lives by searching through all the commandbars using FindControl. Here’s the function:

Function SideBySideOn() As Boolean
'Reset Window Position
SideBySideOn = CommandBars.FindControl(ID:=7874).Enabled
End Function

Here’s how it looks with the code running:

tweaked View Side by Side

Other Stuff Learned Along the Way

  • If you click the Reset Window Position button with the lower window selected, it moves to the top. I’d never noticed that.
  • There is no Window.Move event
  • View Side by Side in Excel 2013 is harder to use. This makes sense, since one of the biggest changes in Excel 2013 is the change to a Single-Document-Interface, making it easier to separate workbooks to multiple monitors. However it’s hard to grab the right edge of a workbook inside View Side by Side. And, as noted above, Application.Height and Window.Height now seem to be the same thing. This makes sense most of the time, but not when you are in View Side by Side mode and trying to compare the height of one of the workbooks/windows to the overall height.

Download!

Here you are my friend.

One thought on “Tweaking View Side by Side

  1. Cool! I only just discovered the joys of the Windows key, and specifically how Windows plus left/right arrows resize the window to take up exactly half the screen.

Speak Your Mind

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

To post code, do this: <code> your vba here </code>