Excel VBA - Automatic adjusting of 2 windows

[Geo Siggy]

My Excel application is splitted into two horizontal windows (not
panes), the one above is a diagram with fixed size in height. The one
below is a normal calculation sheet. The problem is how to program in
VBA, that the window below always will fill the remaining space of the
Excel spreadsheet (the remaining space is depending of the monitor,
resolution, the chosen toolbars etc).

Thanx Siggy

 

[John McGimpsey]

one way:

Const nWINDOW1HEIGHT = 300 'change to suit
If Windows.Count <> 2 Then Exit Sub
With Windows(1)
.Top = 0
.Width = Application.UsableWidth
.Height = nWINDOW1HEIGHT
End With
With Windows(2)
.Top = nWINDOW1HEIGHT + 1
.Width = Application.UsableWidth
.Height = Application.UsableHeight - nWINDOW1HEIGHT
End With