Wednesday 25 February 2015

Update image of an image control dynamically in Excel - VBA

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:

 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

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.

0 comments:

Post a Comment

 
biz.