Adjust Zoom after Resizing the Window

[AAAAndy]

Hi! I'm new to MrExcel.com - it's highly recommended by an associate.
Here’s the question:
I've created a summary report with VBA code that resizes the sheet to fit the size of the window - just like using Zoom to Selection. The code runs whenever that worksheet is selected.
If the Excel window is small, the range automatically zooms to fit the window when the sheet is selected.
But if you then resize the window, the range does not zoom out to fit the window. (Unless you click on another page, then click back to the Summary Page.)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Is there a VB/VBA function that recognizes – as an EVENT – when the window is resized? Then that would trigger the code to zoom to the correct size.
<o> </o>
If not, then I’ll add a button to the screen that will resize the range.

 

[Tom Urtis]

Is there a VB/VBA function that recognizes – as an EVENT – when the window is resized?

Yes there is, curiously enough called "Workbook_WindowResize" with the event code syntax

Private Sub Workbook_WindowResize(ByVal Wn As Window)
 
'your code goes here
 
End Sub

It goes into the workbook module.

 

[Legacy 98055]

Private Sub Workbook_WindowResize(ByVal Wn As Window)

What's up with that? I did not know that existed or I at least forgot that it did.

Unfortunately, this event does not seem to fire when the application window is rezised. Mr. Urtis! What's the option when the app window is rezised? Is there one?

Private Sub Workbook_WindowResize(ByVal Wn As Window)
    If TypeOf ActiveSheet Is Worksheet Then
        If Range("ZoomRange").Parent.Name = ActiveSheet.Name Then
            
            Dim SaveSelection As Range
            
            Application.ScreenUpdating = False
            If Not Intersect(Selection, Range("ZoomRange")) Is Nothing Then
                Set SaveSelection = Selection
            Else
                Set SaveSelection = Cells(1)
            End If
            Application.Goto Range("ZoomRange").Cells(1), True
            Range("ZoomRange").Select
            ActiveWindow.Zoom = True
            SaveSelection.Select
            Application.ScreenUpdating = True
           
        End If
    End If
End Sub

 

[Tom Urtis]

Ha - I took it to mean worksheet window as zoom is a property of the active window, but right you are Tom (you Tom not me Tom).

This link here purports to claim a solution for this
http://www.tech-archive.net/Archive/VB/microsoft.public.vb.general.discussion/2008-02/msg03061.html
and there are a few others out there but when it comes to Office app-wide events like this I do not have 100% faith in their relaibility and stability so I stay away from them.

 

[Legacy 98055]

"I do not have 100% faith in their relaibility and stability so I stay away from them."

Oh... Subclassing. I agree with you. Probably not worth it but don't tell Jaafar that. By the time he is done with Excel, it will be doing his laundry and washing his dishes.