How to enlarge image when click on it in Excel?
Supposing you have inserted a list of pictures into a column and matched the picture sizes with the cell sizes as below screenshot shown. As the inserted picture looks small, you may need to enlarge it for some purposes. This article will show you method of enlarging a picture when click on it in Excel, and recovering to the original size by the second click.
The following VBA code can help you to enlarge an image when click on it in Excel. Please do as follows.
1. Right click the picture which you need to enlarge by just clicking on it, then click Assign Macro from the context menu. See screenshot:
2. In the Assign Macro dialog box, please click the New button.
3. In the popping up Microsoft Visual Basic for Applications window, please copy and paste the below VBA code between the Sub and End Sub codes. See screenshot:
VBA code: Enlarge image by click on it in Excel
Dim shp As Shape Dim big As Single, small As Single Dim shpDouH As Double, shpDouOriH As Double big = 3 small = 1 On Error Resume Next Set shp = ActiveSheet.Shapes(Application.Caller) With shp shpDouH = .Height .ScaleHeight 1, msoTrue, msoScaleFromTopLeft shpDouOriH = .Height If Round(shpDouH / shpDouOriH, 2) = big Then .ScaleHeight small, msoTrue, msoScaleFromTopLeft .ScaleWidth small, msoTrue, msoScaleFromTopLeft .ZOrder msoSendToBack Else .ScaleHeight big, msoTrue, msoScaleFromTopLeft .ScaleWidth big, msoTrue, msoScaleFromTopLeft .ZOrder msoBringToFront End If End With
Note: In the code, you can assign the large sizes of the picture in the code big = 3.
4. Then press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.
From now on, when click on the picture, it will be enlarged to the size you specified, and click it again will recover to the original size as below screenshots shown.
- How to lock picture/image to or inside cell in Excel?
- How to insert image or picture dynamically in cell based on cell value in Excel?
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.· 3 months agoThe Macro works, the problem is that when you resize the the picture in the macro, if you go bellow 1, and save, the picture will lose it's quality :(
To post as a guest, your comment is unpublished.· 3 months agoHello, I'm trying to modify the the big (1) and small (0.1) values but the macro doesn't work properly. After some click the picture doesn't modify :(
To post as a guest, your comment is unpublished.· 7 months agoTHis is what i am looking for but it won't work with my excel 2016. I get an error message Application.caller = Error 2023
To post as a guest, your comment is unpublished.· 1 years agowhen u have inserted the code and press save the pics gets smaller and smaller... great idea but does not work i´m afraid...
To post as a guest, your comment is unpublished.· 2 years agoThis code works well
But when you open the workbook a second time, the images come out blurred
To post as a guest, your comment is unpublished.· 2 years agoMaravilloso funciona perfecto!!!.
Hay forma que la imagen quede en el cento de la pantalla, solo es una oncesion, nada mas, la macro anda muy bien