?
Solved

Excel 2007 Resize Event

Posted on 2011-10-28
11
Medium Priority
?
1,670 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:KenZaw
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 12

Assisted Solution

by:viralypatel
viralypatel earned 200 total points
ID: 37044110
0
 

Author Comment

by:KenZaw
ID: 37044164
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
 
LVL 12

Expert Comment

by:viralypatel
ID: 37044171
here are some steps on Using Events with the Application Object:

http://msdn.microsoft.com/en-us/library/aa221584(v=office.11).aspx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 24

Expert Comment

by:StephenJR
ID: 37044175
The code needs to go in the ThisWorkbook module.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37044187
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37044202
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
 

Author Comment

by:KenZaw
ID: 37044512
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
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 700 total points
ID: 37044556
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
 

Author Comment

by:KenZaw
ID: 37047843
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 100 total points
ID: 37055543
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
 

Author Closing Comment

by:KenZaw
ID: 37062072
I concure that it is too code.  I will change the design.

Great work.  Thank you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing many questions for JRNL_WRAP_ERROR for replication failure, I thought it would be useful to write this article.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question