VBA: ActiveWindow.WindowState

January 8th, 2005 | Categories: VBA | Tags:

There are three different WindowSates that a worksheet can have; Minimized, Maximized, and Normal. You can set the window state with one of these lines of code:

ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal

And of course, once you can program a visual element you can throw in a loop and create an “animated effect”. The following would be an interesting effect possibly when unhiding a worksheet.

This macro will gradually resize a worksheet from small to Maximized, making the worksheet appear to be growing:

Sub SheetGrow()
Dim x As Integer

With ActiveWindow
    .WindowState = xlNormal
    .Top = 1
    .Left = 1
    .Height = 50
    .Width = 50
    For x = 50 To Application.UsableHeight
    .Height = x
    Next x
    For x = 50 To Application.UsableWidth
    .Width = x
    Next x
    .WindowState = xlMaximized
End With

End Sub
  1. Jim Kobzeff
    April 15th, 2005 at 07:42
    Reply | Quote | #1

    Dear Mark:

    My application calls for Application.WindowState=xlMaximized at Open. For some users, however, it does not execute. Moreover, when they run procedures that Unprotect then Protect the WB (to make some forms visible and hide some) Excel quits. I wrote the application in Excel 97 and also use it in Excel XP, but I have never encountered this problem myself. Yet, for the 5% of my users that do encounter it, it’s frustrating. Is there a work around procedure I can write? Or a third-party utility I can recommend to fix their problem? Do you have any idea why they might encounter this problem? Please help me get a handle on this problem if you can. Thank you in advance.

    Jim Kobzeff