Dividing the Screen Unevenly between Two Workbooks

This tip's macros do a fine job but they look rather complicated. I made a simplified version of the second one by removing the "With ... End With" statements and using only one variable instead of five. The result is that my version has only 9 lines of code instead of the 22 lines of this tip's version. In my opinion it is also easier to understand:

Sub UnevenSplit()
If Windows.Count <> 2 Then Exit Sub
Dim Quarter As Integer
Windows.Arrange ArrangeStyle:=xlHorizontal
Quarter = Windows(1).Height / 2
Windows(1).Top = 1
Windows(1).Height = Quarter
Windows(2).Top = Quarter + 3
Windows(2).Height = Quarter * 3
Windows(1).Activate
End Sub

Thanks to Jerry for the tip on workspace!

As Khushnood wrote, the problem is that invisible windows are counted. My solution is close to his, but accounts for the case that the visible windows are not 1 and 2.

Sub UnevenSplit1()
Dim Ht0 As Single
Dim Ht1 As Single
Dim Ht2 As Single
Dim Top2 As Single
Dim VisibleWindowCount As Integer
Dim VisibleWindows(2) As Integer


VisibleWindowCount = 0
For Each win In Windows
If win.Visible Then
VisibleWindowCount = VisibleWindowCount + 1
If VisibleWindowCount > 2 Then Exit Sub
VisibleWindows(VisibleWindowCount) = win.Index
End If
Next

If VisibleWindowCount = 2 Then
With Windows(VisibleWindows(1))
Ht1 = .Height
.WindowState = xlMaximized
Ht0 = .Height
End With
Top2 = Ht1 + 3
Windows.Arrange ArrangeStyle:=xlHorizontal
With Windows(VisibleWindows(1))
.Top = 1
.Height = Ht1
End With
With Windows(VisibleWindows(2))
.Top = Top2
.Height = Ht0 - Ht1 - 22
End With
Windows(VisibleWindows(1)).Activate
End If
End Sub

Bill
Only reason I can think of for macros not running at all is if your macro security setting has been set to 'Disable all macros without notification'. From File ribbon select Options - Trust Center, then Trust Center Settings... - Macro Settings, and check which setting has been selected.

Many thanks to all the contributors for this tip. Lots of food for thought there. I am particularly fond of little macros - you should see my QAT! - so I can feel a new addition coming along. :o))

Regards and thanks,
Dave

A pity they took the Save Workspace functionality away in Excel 2013

I am embarrassed but I have been trying to write macros and I am terrible at it. I have Excel Tips "The Macros Sixth edition" following the instructions but I am missing something because nothing seems to work. I get thru the developer tab and start writing the macros, but there is where I seem to run out of steam. The macros seem to be complete ( I copied one of yours) yet nothing happens. Can you give a dumbass a few tips. R Bill

To: Jerry 07 Mar 2015, 10:52
"Another way to open and resize multiple workbooks on the screen is to create an Excel Workspace"
As far as I know, "Workspace" is not available in version 2013. I used that feature all the time in previous versions and was very disappointed when I found that MS had removed it. If someone knows differently please let me know.

Another way to open and resize multiple workbooks on the screen is to create an Excel Workspace. To do this, open the workbooks and manually resize them. You can also scroll to the columns/rows you want to have on the screen. Then on the View tab, click Save Workspace. Give it a name in the Save Workspace dialog box (for some reason, it always defaults to Resume.xlw!), select the folder where you want to save it, and click Save. The resulting .xlw file contains information about the files that are to be opened, their size and location on the screen, and the starting column/row to be displayed. The workspace file does NOT contain the actual data; that is still stored in the individual files. After the workspace has been created, simply go to File, Open and select the workspace file. It will automatically open and position the files the way they appeared the last time you saved the workspace.
If you often work with multiple files, a handy command button to add to the QAT is "Close All". You can find it in the All Commands section in Customize the Quick Access Toolbar. This will close each of the open files in that instance of Excel and prompt you to save the changes to each file. Saving changes to the files does NOT save the view opened by the Workspace, nor does saving the workspace save the changes to the files.

I tried this macro and it works just fine. Very interesting!

Just one small problem:
the Windows.Count = 2 check also includes hidden workbooks (in my case, PERSONAL.XLSB).

As a workaround, I inserted a few lines in the macro, to check only the visible open workbooks, as under:

...
Dim Top2 As Single
Dim iVisibleWbkCount As Integer, wbk As Window ' (new variables)

'set the count of visible workbooks to 0
iVisibleWbkCount = 0
'cycle thru each workbook and increment the visible workbooks count by 1
'if it is visible
For Each wbk In Application.Windows
If wbk.Visible = True Then iVisibleWbkCount = iVisibleWbkCount + 1
Next wbk

If iVisibleWbkCount = 2 Then
With Windows(1)
...


I would love to hear of a better / more efficient workaround :)