Allow users to browse for and insert picture

[moogeedoo2]

dear excel masters
Greetings,,,
I made a form in an excel sheet and request from users to fill it. this form have a cell for the user photo and what I need exactly is let the user when click on this cell open the file dialog to browse his photo and select it, after that I want it to show in the cell as per the cell dimensions. Please help me and if any inquiry I am here to answer your inquiries and kindly excuse me in any Linguistic mistake
Thank you for your permanent support

 

[RickXL]

Hi,

This should be close ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim strFileSelected  As String
    
    If Target.Count = 1 And Target.Column = 3 Then
        With Application.FileDialog(msoFileDialogFilePicker)
            .AllowMultiSelect = False
            .Filters.Clear
            .Filters.Add "JPEG File", "*.jpg"
            .Filters.Add "PNG File", "*.png"
            .Title = "Select Image"
            .Show
            If .SelectedItems.Count Then
                strFileSelected = .SelectedItems(1)
            Else
                MsgBox "Cancelled by user!"
                Exit Sub
            End If
        End With
        With Me.Pictures.Insert(strFileSelected)
            .Top = Target.Top
            .Left = Target.Left
            .Width = Target.Width
            .Height = Target.Height
        End With
    End If
End Sub

The code needs to be pasted in to the code module for the Sheet (e.g. Sheet1, Sheet2 etc)

Then, when a new cell is selected, the macro checks to see which column has been selected and also how many cells were selected.
If one cell was selected in Column 3 then a FileDialog will appear asking the user to select a picture.
The selected picture will be placed in the selected cell.

If you need it to work in a different column then change the number in red above.

The above settings can be used to select either .jpg files or .png files. Other options are possible.

 

[yky]

 .Top = Target.Top
.Left = Target.Left
.Width = Target.Width
.Height = Target.Height

This code fits the photo to the cell regardless the dimension of the photo. In other words, it is very likely that the photo will be distorted. Anyway not to distort the photo?

 

[RickXL]

Hi yky.

It does not distort the picture when I try it. Rather, it retains the aspect ratio and either fits it to the height or the width.

To make it completely fill the cell I have to use:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim strFileSelected  As String
    
    If Target.Count = 1 And Target.Column = 3 Then
        With Application.FileDialog(msoFileDialogFilePicker)
            .AllowMultiSelect = False
            .Filters.Clear
            .Filters.Add "JPEG File", "*.jpg"
            .Filters.Add "PNG File", "*.png"
            .Title = "Select Image"
            .Show
            If .SelectedItems.Count Then
                strFileSelected = .SelectedItems(1)
            Else
                MsgBox "Cancelled by user!"
                Exit Sub
            End If
        End With
        With Me.Pictures.Insert(strFileSelected)
            .ShapeRange.LockAspectRatio = False
            .Top = Target.Top
            .Left = Target.Left
            .Width = Target.Width
            .Height = Target.Height
        End With
    End If
End Sub

Maybe you need to have a line as above but with False changed to True?

Regards,

 

[yky]

Hi yky.

It does not distort the picture when I try it. Rather, it retains the aspect ratio and either fits it to the height or the width.

Thank you for the reply.

 

[moogeedoo2]

thank you Rickxl and yky for your support, Rickxl your code was useful for me but still the image don't take the dimension of the cell, and also I want to ask like this the Image will store in the workbook or In other words if the user send this workbook that has his photo by email the photo will send with it and i will be able to open it or I have to add some codes to attach the photo with the workbook, thank you again for you MASTERs

 

[RoryA]

You need to use Shapes.AddPicture rather than Pictures.Insert

 

[yky]

You need to use Shapes.AddPicture rather than Pictures.Insert

How to determine the Left, Top, Width, Height parameters in the AddPicture method?

I have a script which first insert a picture using Pictures.Insert. Once the picture is inserted, I can get its dimension. Then, I take the ratio of the width of the picture to the width of the cell and adjust the cell height. So, the picture will be centered in the cell. After that, I delete the picture and use AddPicture to add the picture (again) because I want it to stay with the document.

I wonder if there is a direct way of inserting picture without first knowing the dimension of the picture.

 

[RoryA]

Left and Top are simply where you want the picture located so they are entirely up to you. If you want the picture inserted with default size, specify -1 for height and width (see my blog here).

 

[yky]

Left and Top are simply where you want the picture located so they are entirely up to you. If you want the picture inserted with default size, specify -1 for height and width (see my blog here).

Thank you for the reply. I don't want to insert picture with default size because the default size usually won't fit in the cell. With the info you provided, I can modify my code to do insertion one time only, not two times. Thanks.