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
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.
Also Read: How to Use Active Cell in VBA
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 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.
This brings us to an end.
Thank you for reading. ❤