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:
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:
'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
.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)
Set UpperWindow = .Windows(2)
Set LowerWindow = .Windows(1)
'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
UpperWindow.Height = LowerWindow.Top
UpperWindow.Width = LowerWindow.Width
On Error GoTo 0
.EnableEvents = True
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.
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:
- 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.
- 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.
- 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.”
- 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.
- 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:
'Reset Window Position
SideBySideOn = CommandBars.FindControl(ID:=7874).Enabled
Here’s how it looks with the code running:
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.
Here you are my friend.