insert picture the same size and location of existing picture

Hi,

I am trying to develop code that will help users create reports formatted however they one. One aspect of this that I want to implement is for the user to determine where they want certain pictures to appear on a worksheet and what size they want those pictures. What I am thinking is that the user could insert a placeholder picture anywhere on the worksheet, and then when my macro runs it would replace that picture with an existing picture.

So, what I am looking for is a button that I can click on a worksheet that replaces a placeholder picture with an existing picture. I'm not too concerned how that existing picture is selected, but perhaps a dialog could pop up to select a picture.


The most important part of this is that the new picture would have to be resized to the exact dimensions as the placeholder (even if it skews the aspect ratio), and placed in the exact location of the placeholder.

One thought: the placeholder doesn't necessarily have to be a picture -- if it is easier to have it as shape or something else, it doesn't matter, just something so the user can define where and what size pictures will appear in their reports.

Thanks!

Take a look at this website:

http://www.mcgimpsey.com/excel/lookuppics.html

And if that code doesn't suffice, try this code:

'Private Sub Worksheet_Calculate()

'Calls the Named Ranges needed for the code to run
Dim myCell As Range
Dim mySel As Range
'Sets dimension to center pictures within cell
Dim myR As Range
Dim myP As Shape


Set mySel = Selection

With Application
   .ScreenUpdating = False

'Checks first to see if a cell within the named range is blank, skips if it is, and calls the appropriate image if not
On Error Resume Next
   For Each myCell In Range("KeyCells")
         If myCell <> "" Then
                 'Deletes old image and replaces it with the called new one
                 ActiveSheet.Shapes(myCell.Address & "Final").Delete
                 ActiveSheet.Shapes(myCell.Value).Select
                 Selection.Copy
                 myCell.Offset(0, 0).Select
                 ActiveSheet.Paste
                 Selection.Name = myCell.Address & "Final"
                 Selection.ShapeRange.ZOrder msoSendToBack
                 'Centers pictures within cell
                 With Selection
                 Set myR = .TopLeftCell
                 .Left = myR.Left + (myR.Width - .Width) / 2
                 .Top = myR.Top + (myR.Height - .Height) / 2
                 End With
          Else: ActiveSheet.Shapes(myCell.Address & "Final").Delete
          GoTo Skip
    End If
Skip:
Next myCell
mySel.Select

   .ScreenUpdating = True

End With

End Sub

The size of the image will be the same size that you set the initial image to.

Thanks, mcmahobt!

I am familiar with the website that you pasted, but that is not exactly what I am looking for. That does not help me resize and place apicture based on an existing image (or at least I don't think it does).

Concerning your code -- I can't get this to work, so am not sure if it can accomplish the task. How do I call the new image? How does the code identify the existing pictures location and size? . Thanks for the input!

In case it helps, let me be a little more clear about my intent: I have a "backend" excel document which houses the locations of pictures for different inspections (so imagine cell A1 = C:\user\mydocuments\pic1.jpg, and A2 = C:\user\mydocuments\pic2.jpg and so forth). When the user runs my macro, they will be creating hundred of reports based on a "report template" that they will create. That is why I need to define a picture to a certain location -- so the macro will know where exactly to paste pictures for subsequent reports (what I'm thinking is the macro will look for "picture1" or something and replace it with the picture from the backend).

I'm not too VBA savy, but I was able (with the help of others on the forum) to alter the McGimpsey code to fit my needs for a previous project of mine. I have a similar situation for scheduling. I wanted an image to appear for multiple dates when data from another input changed.

My code fundamentally works the same way McGimpsey's does, but with a few tweaks.

1) The code works for more than one image.
2) The code does not copy/replace every image every time. Just ones that Excel has recognized has either changed value or size/shape.