Excel 2007 Resize Event

Is there a vba event that will be fire whenever the window is resized?

For example, I have an excel that is showing a report.  When I maximize the window my data does not resize to fill the larger area.  I have used Workbook_Open and Workbook_SheetActivate event, but they do fire when the wondow size changes.
KenZawAsked:
Who is Participating?
 
andrewssd3Commented:
The code
Dim X As New EventClassModule
Sub InitializeApp()
    Set X.App = Application
End Sub

Open in new window

needs to go into your ThisWorkbook module, and be called from the Workbook_Open event code.  This seems confusing, but what happens is when your spreadsheet opens, you need to create an instance of the app event handler class, and assign the Excel Application to it so it can start handling application level events.
0
 
viralypatelCommented:
0
 
KenZawAuthor Commented:
I used the following code, but it did not fire.

Private Sub Workbook_WindowResize(ByVal Wn As Excel.Window)
'    Application.StatusBar = Wn.Caption & " resized"
    MsgBox "Will Resize"
End Sub

Any thoughts?

Could you give me the conditions that would fire it?
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
viralypatelCommented:
here are some steps on Using Events with the Application Object:

http://msdn.microsoft.com/en-us/library/aa221584(v=office.11).aspx
0
 
StephenJRCommented:
The code needs to go in the ThisWorkbook module.
0
 
andrewssd3Commented:
It works for me, but only when resizing a window inside the main excel window, so it does not work when the document window is maximised in the Excel app, and I drag the boundaries of the main Excel window - it only works when the document window isn't maximised.
0
 
andrewssd3Commented:
Just to clarify - to get changes in the window size when the sub windows are maximised, you need to use the Application Window_Resize event, which needs to be set up using the techniques viray and stephen referred to.  You were originally using the Workbook WindowResize event.
0
 
KenZawAuthor Commented:
andrewssd3:

I am missing something.  I created a Class Module as follows in the class module named EventClassModule:

------------------------------------------------------------------------------------------

Public WithEvents App As Application

Dim X As New EventClassModule
Sub InitializeApp()
    Set X.App = Application
End Sub

Private Sub App_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)

    MsgBox "Event from App"

End Sub

---------------------------------------------------------------------------------------

I don't get a msgbox when I maximize or minimize at the application level (Excel frame).  
0
 
KenZawAuthor Commented:
I got the sheet resizing and the sheetactivate events to fire.  I don't see how the resizing from the Window level.  I want to be able to fire an event when the Window frame changes, maximizes, and unmaximizes.

 Please advise.
0
 
Rory ArchibaldCommented:
It does not fire for the main Excel window - there is no exposed event for that, as far as I know. The only thing you might be able to do is to subclass the window, and trap the relevant windows messages. Difficult to do in a stable fashion in VBA though.
0
 
KenZawAuthor Commented:
I concure that it is too code.  I will change the design.

Great work.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.