Resize Image To Excel Window Size And Scale To Fit Automatically

[jjfletcher]

Hello,

Wanted to know if it's possible to use a Macro to Scale the image on a sheet to automatically resize when the excel program is large or small?

John

[Excel Fox]

Welcome to ExcelFox jjfletcher

What you have asked for is very much possible. There may be ways to hook the window using APIs, however if you want to do this using native VBA features in Excel, you will need to use Excel 2013 or above. The code below is just a sample to show you how this can be done in a rudimentary yet very effective way.

Private Sub Workbook_WindowResize(ByVal Wn As Window)
    
    With ThisWorkbook.Sheets(1).Shapes(1)
        .Width = Wn.VisibleRange.Width
        .Height = Wn.VisibleRange.Height
    End With
    
End Sub

[jjfletcher]

Excellent - This works perfect... Thanks.... This is ok fro all Excel programs over 2013 - will need to focus on a solution for under 2013 versions.

Thanks Again....

Best Regards,

John

[Excel Fox]

Share with us if you find anything for lesser versions of Excel.

[jjfletcher]

Thanks Excel Fox,

I most certainly will send up if I find anything for lesser versions of Excel

John

[Rick Rothstein]

Welcome to ExcelFox jjfletcher

What you have asked for is very much possible. There may be ways to hook the window using APIs, however if you want to do this using native VBA features in Excel, you will need to use Excel 2013 or above. The code below is just a sample to show you how this can be done in a rudimentary yet very effective way.

Private Sub Workbook_WindowResize(ByVal Wn As Window)
    
    With ThisWorkbook.Sheets(1).Shapes(1)
        .Width = Wn.VisibleRange.Width
        .Height = Wn.VisibleRange.Height
    End With
    
End Sub

Actually, your code works fine down to, at minimum, XL2003 (the earliest version I own)... sort of. There is a WindowsResize event in XL2003 and above, but the event only fires when the worksheet window is not maximized and your code works only when you resize a non-maximized worksheet window. Apparently, when the worksheet is maximized, it is a fixed size and resizing the workbook only exposes or hides portions of that maximized window.