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.· 15 days agoI know that a jpg loses quality each time it is saved. That's why users are advised to save to a new filename. This could be why people who try using this macro are ending up with blurred pictures. A better approach would be to have two pictures - a large and small of the same photo. The small is placed in the cell, the large in a different sheet, same file. Then use code to show the large picture when the small picture is either clicked or hover over. This way. neither picture gets resized and therefore should not lose quality. I am not sure Kutools is able to do this.
- To post as a guest, your comment is unpublished.· 1 years 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.· 1 years 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.· 1 years 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.· 2 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.· 3 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.· 3 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