SOLVED

Load Image from path into Image ActiveX Control in worksheet

Brass Contributor

I have the following code:

 

Private Sub img_Browse_Click()On Error Resume Next
Dim img As String
Dim xCmpPath As String

img = Application.GetOpenFilename
If img <> False Then
Me.img_Photo.Picture = LoadPicture(img)
xCmpPath = img
Sheet1.Range("AE1").FormulaR1C1 = xCmpPath

End If

End Sub

 

It worked perfectly with my userform but sadly I have to change all my coding to work on a Worksheet (Sheet3)

 

I tried to change the code accordingly, but nothing seems to work:

Private Sub img_Browse()

On Error Resume Next
Dim img As String
Dim xCmpPath As String

img = Application.GetOpenFilename
If img <> False Then
Sheet3.Shapes.Range(Array("img_Photo")).Picture = LoadPicture(img)
xCmpPath = img
Sheet1.Range("AE1").FormulaR1C1 = xCmpPath

End If

End Sub

 

Please can someone tell me what is wrong with the above code?

And how can I set it back to "" again when I use a "clear" command button.

I struggle greatly to change all the userform coding to work with the combo boxes, combo list, image etc. on/in my sheet.... And this is only the start of all the changes I have to make   :(

5 Replies
best response confirmed by LizeMarie (Brass Contributor)
Solution

@LizeMarie 

In such situations, you should temporarily comment out the line

On Error Resume Next

because with it, you won't be notified of any errors.

You declare img as a String, but a String cannot be False. The line

    If img <> False Then

causes a Type Mismatch error.

You should declare img as a Variant. That allows it to be any type, whether Boolean (False) or String (a file path).

In the second place, as far as I know, you cannot change the picture on an existing picture shape. You have to delete the existing picture and insert a new one.

Try this version - make sure that there is a shape (any shape) named img_Photo on Sheet3.

Private Sub img_Browse()
    Dim img As Variant
    Dim shp As Shape
    Dim x As Single
    Dim y As Single
    Dim w As Single
    Dim h As Single
    Dim xCmpPath As String
    'On Error Resume Next
    img = Application.GetOpenFilename(FileFilter:= _
        "Pictures (*.jpg;*.png;*.gif;*.bmp),*.jpg;*.png;*.gif;*.bmp")
    If img <> False Then
        Set shp = Sheet3.Shapes("img_Photo")
        x = shp.Left
        y = shp.Top
        w = shp.Width
        h = shp.Height
        shp.Delete
        Set shp = Sheet3.Shapes.AddPicture(img, False, True, x, y, h, w)
        shp.Name = "img_Photo"
        xCmpPath = img
        Sheet1.Range("AE1").FormulaR1C1 = xCmpPath
    End If
End Sub
Thank you again for your help Hans - wish I had your knowledge

@Hans Vogelaar  Hans img_Photo is an ActiveX Control Image is it possible to keep the Picture Alignment as fmPictureAlignmentCentre and PictureSizeMode to fmPictureSizeModeZoom?

 

Thank you

Kind Regards

@LizeMarie 

For an ActiveX image control, it's actually easier:

 

Private Sub img_Browse()
    Dim img As Variant
    'On Error Resume Next
    img = Application.GetOpenFilename(FileFilter:= _
        "Pictures (*.jpg;*.png;*.gif;*.bmp),*.jpg;*.png;*.gif;*.bmp")
    If img <> False Then
        With Sheet3.img_Photo
            .Picture = LoadPicture(img)
            .PictureAlignment = fmPictureAlignmentCenter
            .PictureSizeMode = fmPictureSizeModeZoom
        End With
        Sheet1.Range("AE1").Value = img
    End If
End Sub

 

:) You really helped me allot this past few days and I really appreciate it allot. Thank you
1 best response

Accepted Solutions
best response confirmed by LizeMarie (Brass Contributor)
Solution

@LizeMarie 

In such situations, you should temporarily comment out the line

On Error Resume Next

because with it, you won't be notified of any errors.

You declare img as a String, but a String cannot be False. The line

    If img <> False Then

causes a Type Mismatch error.

You should declare img as a Variant. That allows it to be any type, whether Boolean (False) or String (a file path).

In the second place, as far as I know, you cannot change the picture on an existing picture shape. You have to delete the existing picture and insert a new one.

Try this version - make sure that there is a shape (any shape) named img_Photo on Sheet3.

Private Sub img_Browse()
    Dim img As Variant
    Dim shp As Shape
    Dim x As Single
    Dim y As Single
    Dim w As Single
    Dim h As Single
    Dim xCmpPath As String
    'On Error Resume Next
    img = Application.GetOpenFilename(FileFilter:= _
        "Pictures (*.jpg;*.png;*.gif;*.bmp),*.jpg;*.png;*.gif;*.bmp")
    If img <> False Then
        Set shp = Sheet3.Shapes("img_Photo")
        x = shp.Left
        y = shp.Top
        w = shp.Width
        h = shp.Height
        shp.Delete
        Set shp = Sheet3.Shapes.AddPicture(img, False, True, x, y, h, w)
        shp.Name = "img_Photo"
        xCmpPath = img
        Sheet1.Range("AE1").FormulaR1C1 = xCmpPath
    End If
End Sub

View solution in original post