This post demonstrates how to update image of an image control in Excel with VBA (Visual Basic for Applications).
It is required to update the image of an image control in Excel without manually loading the image. This post teaches you how to do this dynamically with the help of VBA code.
In Excel Ribbon select Developer tab -> Click on Insert -> In ActiveX controls -> Select Image Control.
You can update the image with the properties of previous image or you can set the new properties of image. The properties include top, bottom, height and width of an image.
MsgBox "Image Updated", vbOKOnly, "Updated"
Line 8: Report is the worksheet name and initializing the shp object to the existing image control. If the application fail to find the given image, you will receive an error message.
From line 10 to 15: Capturing the properties of an existing image. In case if you want to set the new properties of an image, edit the values of top, length, height, width.
Line 17: After capturing the properties, deleting the shape which is not required.
Line 19: To add a image control in the worksheet named "Report" with the captured properties with the given name. AddPicture requires following inputs.
Line 20 & 21: Set the original height and width relative to the new image. Comment these lines if you don't want to re-size the image with respect to new image.
This way you can change the image control dynamically with the help of VBA.