Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Allow users to browse for and insert picture

This is a discussion on Allow users to browse for and insert picture within the Excel Questions forums, part of the Question Forums category; dear excel masters Greetings,,, I made a form in an excel sheet and request from users to fill it. this ...

  1. #1
    New Member
    Join Date
    May 2016
    Location
    KSA
    Posts
    5

    Exclamation Allow users to browse for and insert picture

    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

  2. #2
    MrExcel MVP RickXL's Avatar
    Join Date
    Sep 2013
    Location
    UK North Midlands
    Posts
    4,128

    Default re: Allow users to browse for and insert picture

    Hi,

    This should be close ...
    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.
    RickXL

    Excel 2013 and Windows 10

  3. #3
    yky
    yky is offline
    Board Regular
    Join Date
    Jun 2011
    Posts
    956

    Default re: Allow users to browse for and insert picture

    Quote Originally Posted by RickXL View Post
    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?
    Excel 2010, Windows 7

  4. #4
    MrExcel MVP RickXL's Avatar
    Join Date
    Sep 2013
    Location
    UK North Midlands
    Posts
    4,128

    Default re: Allow users to browse for and insert picture

    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:
    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,
    RickXL

    Excel 2013 and Windows 10

  5. #5
    yky
    yky is offline
    Board Regular
    Join Date
    Jun 2011
    Posts
    956

    Default re: Allow users to browse for and insert picture

    Quote Originally Posted by RickXL View Post
    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.
    Excel 2010, Windows 7

  6. #6
    New Member
    Join Date
    May 2016
    Location
    KSA
    Posts
    5

    Default re: Allow users to browse for and insert picture

    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

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Allow users to browse for and insert picture

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

  8. #8
    yky
    yky is offline
    Board Regular
    Join Date
    Jun 2011
    Posts
    956

    Default Re: Allow users to browse for and insert picture

    Quote Originally Posted by RoryA View Post
    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.
    Excel 2010, Windows 7

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Allow users to browse for and insert picture

    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).

  10. #10
    yky
    yky is offline
    Board Regular
    Join Date
    Jun 2011
    Posts
    956

    Default Re: Allow users to browse for and insert picture

    Quote Originally Posted by RoryA View Post
    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.
    Excel 2010, Windows 7

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com