Forum Discussion

LizeMarie's avatar
LizeMarie
Brass Contributor
Dec 13, 2022
Solved

Load Image from path into Image ActiveX Control in worksheet

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   ðŸ˜ž

developer
excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Dec 13, 2022

    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
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 13, 2022

    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
    • Muhammadiyya's avatar
      Muhammadiyya
      Brass Contributor
      Jun 28, 2024

      please I want a guide, I'm working on school report card generator with student pictures in excel VBA. I created a drop-down list that contains students reg. No. I have the students pics path in a column and I want to load those picture from it's  path into a shape  with the picture changing as I change the values on the drop-down list.HansVogelaar 

    • LizeMarie's avatar
      LizeMarie
      Brass Contributor
      Dec 14, 2022

      HansVogelaar  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

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Dec 14, 2022

        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

         

    • LizeMarie's avatar
      LizeMarie
      Brass Contributor
      Dec 14, 2022
      Thank you again for your help Hans - wish I had your knowledge

Resources