BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Change Picture or Image Location



Query Source   : Excel Macros Google Group
Solution Type   : VBA Macro
Query by         : Carlos Pedro (Sister Ray)
Solution by       : Ashish Jain (MCAS; MCA; Lead Trainer, Success Electrons)


Query / Problem:
I'm trying to change a Picture object that is included in a Excel Worsheet, since I haven't had much sucess I was wondering if it is possible to change the image source (the file that is being displayed) using VBA.

Solution :

1. Try this VBA code and assign it to a Button as shown in Example below.

Code:
Sub Change_Picture_Location()
'Error Handler
On Error GoTo ErrH:

'Declaration of Variables
Dim flag As Boolean
Dim myShape As Shape
Dim cTop As Double
Dim cLeft As Double
Dim cHeight As Double
Dim cWidth As Double
' +-+- Check whether Selection is Shape or Range -+-+
flag = False
For Each myShape In ActiveSheet.Shapes
    If myShape.Name = Selection.Name Then
        flag = True
        Exit For
    Else
        flag = False
    End If
Next
ErrH:
On Error GoTo endH
' +-+- Checking Over -+-+
If flag = True Then
'Saving Selected Image Attributes
    cTop = Selection.ShapeRange.Top
    cLeft = Selection.ShapeRange.Left
    cHeight = Selection.ShapeRange.Height
    cWidth = Selection.ShapeRange.Width
'Delete Selected Image
    Selection.Delete
   
'Insert New Image
    ActiveSheet.Pictures.Insert(Application.GetOpenFilename).Select
   
'Applying Old attributes to the New image
    Selection.ShapeRange.Top = cTop
    Selection.ShapeRange.Left = cLeft
    Selection.ShapeRange.Height = cHeight
    Selection.ShapeRange.Width = cWidth
Else
    MsgBox "Please select a picture not the range."
End If
endH:
End Sub

Example: