Welcome to Logically Proven blog.
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.
To add an image control in a worksheet:
In Excel Ribbon select Developer tab -> Click on Insert -> In ActiveX controls -> Select Image Control.
If you don't see the Developers tab, please check this link which explains how to add-
http://logicallyproven.blogspot.de/2015/02/worksheet-display-name-vs-code-name-in.html
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.
Important note - Please do not debug the code. If you try to debug; application raises an error. This is because we are deleting one of the objects in a worksheet. (error raises at the line where the image control is deleted)
The following is the code to update the image of an image control:
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.
AddPicture(Filename, LinkToFile , SaveWithDocument, Left, Top, width, Height)
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.
Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.
Logically Proven
Learn, Teach, Share
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.
To add an image control in a worksheet:
In Excel Ribbon select Developer tab -> Click on Insert -> In ActiveX controls -> Select Image Control.
If you don't see the Developers tab, please check this link which explains how to add-
http://logicallyproven.blogspot.de/2015/02/worksheet-display-name-vs-code-name-in.html
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.
Important note - Please do not debug the code. If you try to debug; application raises an error. This is because we are deleting one of the objects in a worksheet. (error raises at the line where the image control is deleted)
The following is the code to update the image of an image control:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | Sub UpdateReportImage(strImageName, strImagePath) Dim shp As Shape Dim top, length, height, width As Single On Error GoTo err_handler Set shp = Report.Shapes(strImageName) 'Capture properties of exisitng picture such as location and size With shp top = .top length = .Left height = .height width = .width End With Report.Shapes(strImageName).Delete Set shp = Report.Shapes.AddPicture(strImagePath, msoFalse, msoTrue, length, top, width, height) shp.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue shp.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue shp.Name = strImageName Set shp = Nothing MsgBox "Image Updated", vbOKOnly, "Updated" Exit Sub err_handler: MsgBox Err.Description, vbOKOnly, "Error" End Sub |
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.
AddPicture(Filename, LinkToFile , SaveWithDocument, Left, Top, width, Height)
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.
Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.
Logically Proven
Learn, Teach, Share
Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.
0 comments:
Post a Comment