Allow users to browse for and insert picture

moogeedoo2

New Member
Joined
May 23, 2016
Messages
5
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi,

This should be close ...
Rich (BB code):
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.
 
Upvote 0
Code:
 .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?
 
Upvote 0
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:
Rich (BB code):
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,
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You need to use Shapes.AddPicture rather than Pictures.Insert
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top