Application.ActiveWindow Property in VBA

Today we will learn the usage of the ActiveWindow property in VBA. It is used along with the Application object. Let us study some of the essential properties of ActiveWindow in VBA.

ActiveWindow in VBA

The ActiveWindow property when used with the Application object returns a Window Object that corresponds to the Active Window.

Application.ActiveWindow

If no window is actively opened then the ActiveWindow property in VBA returns Nothing. This property is Read Only.

Caption of ActiveWindow

The caption refers to the Text in the Title Bar on the top of the Active Window. We can print the caption of the Active Window with the help of the Msgbox function in VBA.

For instance, you can run the following Sub procedure to print the active window’s caption.

Sub printCaptionActiveWindow()
'print the caption of active window
Msgbox "The Caption of active window : " & Application.ActiveWindow.Caption
End Sub
print the caption of active window in VBA excel
activewindow in excel vba infographics

Changing the Windows State of ActiveWindow

The Active Window can have three possible states Minimized, Maximized, or Normal state. You can change the state of the active window with the help of the WindowState property. You can set the value of the property based on the required state.

'Changing state to Maximized
Application.ActiveWindow.WindowState = xlMaximized
'***********************************************

'Changing state to Minimized
Application.ActiveWindow.WindowState = xlMinimized
'***********************************************

'Changing state to Normal
Application.ActiveWindow.WindowState = xlNormal
'***********************************************

Locating the ActiveWindow on the Screen

We can change the location of the active window on the computer’s screen by adjusting the distance of the top left corner of the screen from the active window. We have two properties named Top and Left using which we change the location of the active window in Excel.

Note that you can change both properties when the window state is set to normal.

  • Top – This is the distance between the top boundary of your computer’s screen and the active window.
  • Left – This is the distance between the left boundary of your computer’s screen and the active window.

You can run the following Sub procedure to change the value of these properties.

Sub ChangeActiveWindowLocation()
'chamge the top and left property of active

With ActiveWindow
.WindowState = xlNormal
.Left = 200
.Top = 300
End With

End Sub
change the location of active window in VBA

Change the Height and Width of the Active Window

We can use VBA’s Height and Width property to resize the active window. You can change the height and width only in Normal Windows State. For example, the following Sub Procedure will reduce the height and width of the active window to half of current value.

Sub ChangeHeightWidthActiveWindow()
'reducing window's sze to half
With ActiveWindow
  .WindowState = xlNormal
  .Height = .Height \ 2
  .Width = .Width \ 2
End With
End Sub

This will reduce the window size to half.

Change the Color of Grid Lines using VBA

We can change the color of gridlines using VBA in Excel. This applies to Active Window. We are using the GridlineColor property of ActiveWindow and set a color to it with the help of RGB Function.

For example, you can use the following code to generate random colored gridlines each time you run the macro.

Sub changeGridlineColor()
'get random colored gridlines

ActiveWindow.GridlineColor = RGB(Rnd * 255, Rnd * 255, _
Rnd * 255)
End Sub

Here Rnd returns a value between 0 and 1

We will use the command button to run the above macro.

change the color of grid lines using VBA

This brings us to an end.

Thank you for reading. ❤

Leave a Comment