In the UI, you can arrange the open windows using Windows>Arrange. In VBA, the same effect is achieved using the Arrange method of the Windows collection object.
Application.Windows.Arrange xlArrangeStyleTiled
The argument is an xlArrangeStyle constant. What I haven’t been able to do is determine the current state of the windows. That is, there’s no ArrangeStyle property to read.
To cycle through some different arrangements, you can store the current ArrangeStyle in a variable and use that variable to switch to another style. In this example, the windows are cycled through normal maximized view, tiled arrangement, and horizontal arrangement.
Dim mlWndState As Long ‘Module level variable
Sub SwitchView()
‘Cycle through Maximized, Tiled, and Horizontal
Select Case mlWndState
Case xlMaximized
Application.Windows.Arrange xlArrangeStyleTiled
mlWndState = xlArrangeStyleTiled
Case xlArrangeStyleTiled
Application.Windows.Arrange xlArrangeStyleHorizontal
mlWndState = xlArrangeStyleHorizontal
Case Else
Application.ActiveWindow.WindowState = xlMaximized
mlWndState = xlMaximized
End Select
End Sub
Sub SwitchView()
‘Cycle through Maximized, Tiled, and Horizontal
Select Case mlWndState
Case xlMaximized
Application.Windows.Arrange xlArrangeStyleTiled
mlWndState = xlArrangeStyleTiled
Case xlArrangeStyleTiled
Application.Windows.Arrange xlArrangeStyleHorizontal
mlWndState = xlArrangeStyleHorizontal
Case Else
Application.ActiveWindow.WindowState = xlMaximized
mlWndState = xlMaximized
End Select
End Sub
Posting code? Use <pre> tags for VBA and <code> tags for inline.