Announcement

Collapse
No announcement yet.

Insert picture based on cell reference

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Insert picture based on cell reference



    Hello,

    I found the next link that is exactly what I am looking for, except that my photos are in a folder (+500 pictures).
    So, instead to paste my pictures in The Worksheet, I'd rather to insert them directly from my location photo files that is D:\DataPic\

    http://www.ozgrid.com/forum/showthread.php?t=35427

    My range of the photo names listed goes from A2 to A502
    Example:
    A2: Apple
    A3: Watermelon
    A4: Lemon
    .
    .
    A502: Strawberry

    I need to show the photo in the range F3:G7 for the cell referenced in A2:A502 (the photo should be inserted at a fixed size, regardless of its original size).
    Every time I'd select another name, the before picture selected (loaded) should be deleted.
    Please, help me.
    rgds,
    Arnolf.

  • #2
    Re: Insert picture based on cell reference

    Arnolf,

    Have you tried recording a macro? It would be a good place to start. With the recorded macro you would need to change just a couple of things.

    1. You would need to determine how you want the macro to execute.
    2. Maybe a loop to delete all picture shapes on the current page and then it would run the recorded part adding the new picture.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on

    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

    Comment


    • #3
      Re: Insert picture based on cell reference

      How about insert comment pictures ?


      Place this code in cell B2

      ="C:\MyPicture\"&A2&".jpg"


      And run this code:


      Code:
      Option Explicit
      Sub Add_Comments()
      
          Dim myPict As Object
          Dim curWks As Worksheet
          Dim myRng As Range
          Dim myCell As Range
          
          Set curWks = Sheets(1) ' Change to suit
          
          With curWks
            Set myRng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
          End With
          
          curWks.Columns("F").ClearComments
          
          For Each myCell In myRng.Cells
            If Trim(myCell.Value) = "" Then
              'do nothing
            ElseIf Dir(CStr(myCell.Value)) = "" Then
              'picture not there!
            MsgBox myCell.Value & " Doesn't exist!"
            Else
              With myCell.Offset(0, 0) '3 columns to the right of C (F)
                  .AddComment("").Shape.Fill.UserPicture (myCell.Value)
              End With
            End If
          Next myCell
      End Sub
      Last edited by Fin Fang Foom; November 20th, 2005, 06:48.

      Comment


      • #4
        Re: Insert picture based on cell reference

        Thanks for your replies.
        I enclose the file.
        Instead to paste my pictures in the Worksheet, I'd rather to show them directly from my location photo files that is D:\DataPic\
        rgds,
        Arnolf
        Attached Files

        Comment


        • #5
          Re: Insert picture based on cell reference

          Hi,

          Try this ShowPicD UDF by Damon.

          HTH
          Kris

          ExcelFox

          Comment


          • #6
            Re: Insert picture based on cell reference

            I'm intrigued. I like your method by clicking the cells the picture appears, But instead of the picture appears in the cells how can it be modified to have the pictures appear in the image control box ?

            Comment


            • #7


              Re: Insert picture based on cell reference

              Kindly update the macro for the same

              Comment

              Working...
              X