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
Posted in Uncategorized

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.