Cycling Window States
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
Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].
Leave a comment