Class Window (Excel VBA)

The class Window represents a window.

The main procedures of class Window are Activate and Close

Set

To use a Window class variable it first needs to be instantiated, for example

Dim strIndex As String: strIndex = 
Dim wndWindow As Window
Set wndWindow = Application.Windows(Index:=strIndex)

The following procedures can be used to set variables of type Window: Application.ActiveWindow, NewWindow, Windows.Item, Workbook.NewWindow, Application.Windows, Selection and Workbook.Windows

For Each

Here is an example of processing the Window items in a collection.

Dim wndWindow As Window
For Each wndWindow In Application.Windows
	
Next wndWindow

Themes

Some procedures in this class have been grouped together in themes and are described on separate theme pages

Display/Show with its procedures DisplayFormulas, DisplayGridlines, DisplayHeadings, DisplayHorizontalScrollBar, DisplayRightToLeft, DisplayRuler, DisplayVerticalScrollBar, DisplayWhitespace, DisplayWorkbookTabs and DisplayZeros
Print with its procedures PrintOut and PrintPreview
Column with its procedures ScrollColumn and SplitColumn
Row with its procedures ScrollRow and SplitRow

Methods

These are the main methods of the Window class

Activate - Brings the window to the front of the z-order.

Close - Closes the object.

Other Methods

LargeScroll - Scrolls the contents of the window by pages.

Worksheets("Sheet1").Activate 
ActiveWindow.LargeScroll down:=3

RangeFromPoint - Returns the Shape or Range shape that is positioned at the specified pair of screen coordinates. If there isn't a shape located at the specified coordinates, this method returns Nothing.

Private Function AltText(ByVal intMouseX As Integer, _ 
 ByVal intMouseY as Integer) As String 
 Set objShape = ActiveWindow.RangeFromPoint _ 
 (x:=intMouseX, y:=intMouseY) 
 If Not objShape Is Nothing Then 
 With objShape 
 Select Case .Type 
 Case msoChart, msoLine, msoPicture: 
 AltText = .AlternativeText 
 Case Else: 
 AltText = "" 
 End Select 
 End With 
 Else 
 AltText = "" 
 End If 
End Function

ScrollIntoView - Scrolls the document window so that the contents of a specified rectangular area are displayed in either the upper-left or lower-right corner of the document window or pane (depending on the value of the Start argument).

ActiveWindow.ScrollIntoView _ 
 Left:=50, Top:=20, _ 
 Width:=100, Height:=200

ScrollWorkbookTabs - Scrolls through the workbook tabs at the bottom of the window. Doesn't affect the active sheet in the workbook.

ActiveWindow.ScrollWorkbookTabs position:=xlLast

SmallScroll - Scrolls the contents of the window by rows or columns.

Worksheets("Sheet1").Activate 
ActiveWindow.SmallScroll down:=3

Windows.Arrange - Arranges the windows on the screen.

Application.Windows.Arrange ArrangeStyle:=xlArrangeStyleTiled

Properties

ActiveCell returns a Range object that represents the active cell in the active window (the window on top) or in the specified window. If the window isn't displaying a worksheet, this property fails.

ActiveCell 
Application.ActiveCell 
ActiveWindow.ActiveCell 
Application.ActiveWindow.ActiveCell

ActiveChart returns a Chart object that represents the active chart (either an embedded chart or a chart sheet). An embedded chart is considered active when it's either selected or activated. When no chart is active, this property returns Nothing.

ActiveChart.HasLegend = True

ActivePane returns a Pane object that represents the active pane in the window.

Workbooks("BOOK1.XLS").Activate 
If not ActiveWindow.FreezePanes Then 
 With ActiveWindow 
 i = .ActivePane.Index 
 If i = .Panes.Count Then 
 .Panes(1).Activate 
 Else 
 .Panes(i+1).Activate 
 End If 
 End With 
End If

ActiveSheet returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. Returns Nothing if no sheet is active.

MsgBox "The name of the active sheet is " & ActiveSheet.Name

ActiveSheetView returns an object that represents the view of the active sheet in the specified window.

AutoFilterDateGrouping true if the auto filter for date grouping is currently displayed in the specified window.

Caption returns or sets a Variant value that represents the name that appears in the title bar of the document window.

ActiveWorkbook.Windows(1).Caption = "Consolidated Balance Sheet" 
ActiveWorkbook.Windows("Consolidated Balance Sheet") _ 
 .ActiveSheet.Calculate

DisplayOutline true if outline symbols are displayed.

Workbooks("BOOK1.XLS").Worksheets("Sheet1").Activate 
ActiveWindow.DisplayOutline = True 

EnableResize true if the window can be resized.

ActiveWindow.EnableResize = False

FreezePanes true if split panes are frozen.

Workbooks("BOOK1.XLS").Worksheets("Sheet1").Activate 
ActiveWindow.FreezePanes = True

GridlineColor returns or sets the gridline color as an RGB value.

Workbooks("BOOK1.XLS").Worksheets("Sheet1").Activate 
ActiveWindow.GridlineColor = RGB(255,0,0)

GridlineColorIndex returns or sets the gridline color as an index into the current color palette or as an XlColorIndex constant.

ActiveWindow.GridlineColorIndex = 5

Height returns or sets a Double value that represents the height, in points, of the window.

Hwnd returns a Long that indicates the window handle of the specified window.

Index returns a Long value that represents the index number of the object within the collection of similar objects.

Left returns or sets a Double value that represents the distance, in points, from the left edge of the client area to the left edge of the window.

OnWindow returns or sets the name of the procedure that's run whenever you activate a window.

ThisWorkbook.Windows(1).OnWindow = "WindowActivate"

Panes returns a Panes collection that represents all the panes in the specified window.

Workbooks("BOOK1.XLS").Worksheets("Sheet1").Activate 
MsgBox "There are " & ActiveWindow.Panes.Count & _ 
 " panes in the active window"

Parent returns the parent object for the specified object. Read-only.

RangeSelection returns a Range object that represents the selected cells on the worksheet in the specified window even if a graphic object is active or selected on the worksheet.

MsgBox ActiveWindow.RangeSelection.Address

SelectedSheets returns a Sheets collection that represents all the selected sheets in the specified window.

For Each sh In Workbooks("BOOK1.XLS").Windows(1).SelectedSheets 
 If sh.Name = "Sheet1" Then 
 MsgBox "Sheet1 is selected" 
 Exit For 
 End If 
Next

Selection returns the specified window, for a Windows windows.

Worksheets("Sheet1").Activate 
Selection.Clear

SheetViews returns the SheetViews object for the specified window.

Split true if the window is split.

Workbooks("BOOK1.XLS").Worksheets("Sheet1").Activate 
With ActiveWindow 
 .SplitColumn = 2 
 .SplitRow = 2 
End With

SplitHorizontal returns or sets the location of the horizontal window split, in points.

Workbooks("BOOK1.XLS").Worksheets("Sheet1").Activate 
ActiveWindow.SplitHorizontal = 216

SplitVertical returns or sets the location of the vertical window split, in points.

Workbooks("BOOK1.XLS").Worksheets("Sheet1").Activate 
ActiveWindow.SplitVertical = 216

TabRatio returns or sets the ratio of the width of the workbook's tab area to the width of the window's horizontal scroll bar (as a number between 0 (zero) and 1; the default value is 0.6).

ActiveWindow.TabRatio = 0.5

Top returns or sets a Double value that represents the distance, in points, from the top edge of the window to the top edge of the usable area (below the menus, any toolbars docked at the top, and the formula bar).

Windows.Arrange xlArrangeTiled 
ah = Windows(1).Height ' available height 
aw = Windows(1).Width + Windows(2).Width ' available width 
With Windows(1) 
 .Width = aw 
 .Height = ah / 2 
 .Left = 0 
End With 
With Windows(2) 
 .Width = aw 
 .Height = ah / 2 
 .Top = ah / 2 
 .Left = 0 
End With

Type returns or sets an XlWindowType value that represents the window type.

UsableHeight returns the maximum height of the space that a window can occupy in the application window area, in points.

With ActiveWindow 
 .WindowState = xlNormal 
 .Top = 1 
 .Left = 1 
 .Height = Application.UsableHeight 
 .Width = Application.UsableWidth 
End With

UsableWidth returns the maximum width of the space that a window can occupy in the application window area, in points.

With ActiveWindow 
 .WindowState = xlNormal 
 .Top = 1 
 .Left = 1 
 .Height = Application.UsableHeight 
 .Width = Application.UsableWidth 
End With

View returns or sets the view showing in the window.

ActiveWindow.View = xlPageBreakPreview

Visible returns or sets a Boolean value that determines whether the object is visible.

VisibleRange returns a Range object that represents the range of cells that are visible in the window or pane. If a column or row is partially visible, it's included in the range.

Width returns or sets a Double value that represents the width, in points, of the window.

WindowNumber returns the window number. For example, a window named Book1.xls:2 has 2 as its window number. Most windows have the window number 1.

ActiveWindow.NewWindow 
MsgBox ActiveWindow.WindowNumber

WindowState returns or sets the state of the window.

Application.WindowState = xlMaximized

Zoom returns or sets a value that represents the display size of the window, as a percentage (100 equals normal size, 200 equals double size, and so on).

Windows.Count returns a Long value that represents the number of objects in the collection.

Windows.Item returns a single Window object from the collection.

Windows.Item(1).WindowState = xlMaximized

Windows.Parent returns the parent object for the specified object. Read-only.

Windows.SyncScrollingSideBySide true enables scrolling the contents of windows at the same time when documents are being compared side by side. False disables scrolling the windows at the same time.