- To post as a guest, your comment is unpublished.· 1 years agoI have tested that to save the workbook as macro-enable workbook, reopen the macro-enable workbook, the image is still there, and the code can work as well. Maybe try to save the workbook before you saving it as macro-enable workbook.
How to enlarge or shrink picture with one click in Excel?
Supposing there are multiple pictures in a sheet which are in different sizes. Do you have any ways to enlarge or shrink these pictures in one size with one click? There is no built-in feature to solve it, but in this article, I introduce a VBA code to enlarge or shrink all pictures in the sheet with one click.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Do as below steps to enlarge or shrink pictures with one click.
1. Right click at the sheet tab where you want to resize the pictures, and select View Code from context menu.
2. In the popping Microsoft Visual Basic for Applications window, and paste below code to the blank script.
VBA: Enlarge and shrink pictures with one click
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'UpdatebyEntendoffice20161111 Application.ScreenUpdating = False Dim xRg As Range, sPic As Shape For Each sPic In ActiveSheet.Shapes If Target.Column > 1 Then Set xRg = Target.Offset(, -1) With sPic If TypeName(.OLEFormat.Object) = "Picture" Then If .TopLeftCell.Address = xRg.Address Then .Height = 350 .Width = 250 End If End If End With ElseIf Target.Column = 1 Then With sPic If TypeName(.OLEFormat.Object) = "Picture" Then .Height = 60 .Width = 60 End If End With End If Next sPic Application.ScreenUpdating = True End Sub
3. Save the code and close the window. And when you click at any cell of column A, all pictures will be shrunk. See screenshot:
4. And when you click at the cell which at the right top of the picture, the relative picture will be enlarged. See screenshot:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years agoWhen I save as a Macro-Enabled workbook, close and then re-open, the image integrity has been lost.
- To post as a guest, your comment is unpublished.· 2 years agoDoesn't work
- To post as a guest, your comment is unpublished.· 1 years agoSorry, I did not know why it does not work in your case, but before carrying out this tutorial, I have tested it several times.