Microsoft Office Forums - View Single Post

Logit · 08-31-2020, 08:17 PM

This macro will allow you to select the desired image and then paste it to the ACTIVECELL.

You could modify the macro slightly for your purposes (currently it is restricted to L5), then call the macro from your USERFORM. The user will need to select the cell where the image shall be pasted prior to running the macro.

Code:

Option Explicit

Sub ChangeImage()
Application.ScreenUpdating = False

With Range("L5")
    ActiveSheet.Pictures.Delete
End With


    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .ButtonName = "Submit"
        .Title = "Select an image file"
        .Filters.Clear
        .Filters.Add "JPG", "*.JPG"
        .Filters.Add "JPEG File Interchange Format", "*.JPEG"
        .Filters.Add "Graphics Interchange Format", "*.GIF"
        .Filters.Add "Portable Network Graphics", "*.PNG"
        .Filters.Add "Tag Image File Format", "*.TIFF"
        .Filters.Add "All Pictures", "*.*"
        
        If .Show = -1 Then
            Dim img As Object
            Set img = ActiveSheet.Pictures.Insert(.SelectedItems(1))
            With Range("L5")
               
                    img.Top = .Top
                    img.Width = 90
                    img.Height = 90
                    img.Left = .Left
                    img.Placement = xlMoveAndSize
                End With
            
            Else
            MsgBox ("Cancelled.")
        End If
    End With
Application.ScreenUpdating = True
End Sub