Doing Things with Windows

When you work with several Excel workbooks and need to compare or consolidate data or when you want to see different parts of the same worksheet, you are likely to use the options available from the Microsoft Excel Window menu: New Window and Arrange.

Let's see how you can arrange your screen with Visual Basic for Applications.

To render this into VBA:

Enter this in the Immediate window:

Show the active workbook in a new window.

Display all open workbooks on screen.

Activate the second window.

Find out the title of the active window.

ActiveWorkbook.NewWindow

Windows.Arrange

Windows(2).Activate

?ActiveWindow.Caption

Change the active window's title to "My ActiveWindow.Caption = "My Window" Window."

When you display windows on screen, you can decide how to arrange them. The Arrange method has many arguments. The argument that allows you to control the way the windows are positioned on your screen is called ArrangeStyle. If you omit the ArrangeStyle argument, all windows are tiled.

Constant

Value

Description xlArrangeStyleTiled 1

xlArrangeStyleCascade 7

xlArrangeStyleHorizontal 2

xlArrangeStyleVertical 3

Windows are tiled (the default value). Windows are cascaded. Windows are arranged horizontally. Windows are arranged vertically.

Instead of the names of constants, you can use the value equivalents shown above.

To cascade all windows, write the following VBA instruction: Windows.Arrange ArrangeStyle:=xlArrangeStyleCascade

Or simply:

Windows.Arrange ArrangeStyle:=7

0 0

Post a comment