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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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.
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,672
Office Version
2010
Platform
Windows
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?
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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,
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,672
Office Version
2010
Platform
Windows
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

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,046
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You need to use Shapes.AddPicture rather than Pictures.Insert
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,672
Office Version
2010
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,046
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Well-known Member
Joined
Jun 7, 2011
Messages
1,672
Office Version
2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,897
Messages
5,471,367
Members
406,758
Latest member
Antsnetswprk

This Week's Hot Topics

Top