How to place a picture into a specified cell?

[OaklandJim]

I have a sub that is 90% there. It copies a picture from sheet 1 and puts it into sheet 2. It sizes the target cell in sheet 2 correctly, based on the cell that the original picture is in. But I cannot put the picture into that target cell in sheet 2. I tried setting the .Left property of the picture based on the .Left property of the target cell. What I am trying is not working. What simple change do I need to make this work?

Sub CopyEmbedPictureIntoCell()
'
    Dim wsFromSheet As Worksheet
    
    Dim wsToSheet As Worksheet
    
'   Cell that the original picture is in.
    Dim rFromCell As Range
    
'   Cell into which the copy of the picture is placed.
    Dim rToCell As Range
        
'   Note that oPicture and oPicture2 are different types.
    Dim oPicture As Shape
    
    Dim oPicture2 As Picture
    
'   Width and height of cells containing pictures.
    Dim dColumnWidth As Double
    Dim dRowHeight As Double
        
    Set wsFromSheet = Worksheets("Sheet1")
    
    Set wsToSheet = Worksheets("Sheet2")
    
'   Specify cell into which the copy of the original picture is placed.
    Set rToCell = wsToSheet.Range("F19")
    
'   Set shape object to the original picture.
    Set oPicture = wsFromSheet.Shapes("TestPicture1")
        
'   Set cell that the original picture is in based on TopLeft property
'   of the original picture.
    Set rFromCell = oPicture.TopLeftCell
    
'   Get width and height of cell containing the original picture.
    dColumnWidth = rFromCell.ColumnWidth
    dRowHeight = rFromCell.RowHeight

'   Size the cell into which the copied picture is placed.
    rToCell.ColumnWidth = dColumnWidth
    rToCell.RowHeight = dRowHeight

'   Copies the original picture (Shape) to the wsToSheet worksheet.
    oPicture.Copy
    wsToSheet.Paste

    Set oPicture2 = Selection

    oPicture2.Name = oPicture.Name & "_copy"
    
    oPicture2.Cut
    wsToSheet.Paste
    
'   This reports the picture's Left position property
    Debug.Print "Picture 2 Left = " & Selection.Left
    
'   Position the copy of the picture in the target cell (rToCell)
'   by setting it's Left property.

'   These fail
    'Selection.Left = rToCell.Left
    'Selection.ShapeRange.Left = rToCell.Left

End Sub

 

[rlv01]

Instead of trying to use Selection to set the picture's position, use the picture object itself:

Sub CopyEmbedPictureIntoCell()
    Dim wsFromSheet As Worksheet
    Dim wsToSheet As Worksheet
    Dim rFromCell As Range                            '   Cell that the original picture is in.
    Dim rToCell As Range                              '   Cell into which the copy of the picture is placed.
    Dim oPicture As Shape                             '   Note that oPicture and oPicture2 are different types.
    Dim oPicture2 As Picture
    Dim dColumnWidth As Double                        '   Width and height of cells containing pictures.
    Dim dRowHeight As Double
    Dim PCnt As Long                                  '   picture count

    Set wsFromSheet = Worksheets("Sheet1")
    Set wsToSheet = Worksheets("Sheet2")

    '   Specify cell into which the copy of the original picture is placed.
    Set rToCell = wsToSheet.Range("F19")

    '   Set shape object to the original picture.
    Set oPicture = wsFromSheet.Shapes("TestPicture1")

    '   Set cell that the original picture is in based on TopLeft property of the original picture.
    Set rFromCell = oPicture.TopLeftCell

    '   Get width and height of cell containing the original picture.
    dColumnWidth = rFromCell.ColumnWidth
    dRowHeight = rFromCell.RowHeight

    '   Size the cell into which the copied picture is placed.
    rToCell.ColumnWidth = dColumnWidth
    rToCell.RowHeight = dRowHeight

    '   Copies the original picture (Shape) to the wsToSheet worksheet.
    PCnt = wsToSheet.Pictures.Count
    oPicture.Copy
    wsToSheet.Paste
    If wsToSheet.Pictures.Count = PCnt + 1 Then
        Set oPicture2 = wsToSheet.Pictures(PCnt + 1)
        oPicture2.Name = oPicture.Name & "_copy"

        '    oPicture2.Cut
        '    wsToSheet.Paste

        '   This reports the picture's Left position property
        Debug.Print "Picture 2 Left = " & oPicture2.Left

        '   Position the copy of the picture in the target cell (rToCell)
        '   by setting it's Left property.
        oPicture2.Left = rToCell.Left
        oPicture2.Top = rToCell.Top
    End If
End Sub

 

[OaklandJim]

After experimenting and Googling I just cannot place the picture into the specified cell

'   This does not place the picture into the specified cell
    Selection.Left = rToCell.Left

'   This does not place the picture into the specified cell
    Selection.ShapeRange.Left = rToCell.Left
   
'   This does not place the picture into the specified cell.
    Selection.TopLeftCell = rToCell

I did find that if I activate the target cell in code the picture is placed into the activecell in the target worksheet. I guess that is what I'll have to do because I cannot do it another way. But using that approach is not satisfying.

 

[OaklandJim]

rlv01. You did it, you showed me what is wrong with my code: I did not set the picture's .Top property! I just did not think of that. So I learned something thanks to your post...you have to set a picture's .Top and .Left properties to place it where needed! Thank you!

 

[rlv01]

After experimenting and Googling I just cannot place the picture into the specified cell

'   This does not place the picture into the specified cell
    Selection.Left = rToCell.Left

'   This does not place the picture into the specified cell
    Selection.ShapeRange.Left = rToCell.Left
  
'   This does not place the picture into the specified cell.
C

I did find that if I activate the target cell in code the picture is placed into the activecell in the target worksheet. I guess that is what I'll have to do because I cannot do it another way. But using that approach is not satisfying.

If you inspect the code I posted you will see that I do not use "Selection" (e.g. Selection.Left = rToCell.Left, Selection.ShapeRange.Left = rToCell.Left, Selection.ShapeRange.Left = rToCell.Left ) as it much more efficient to just use the picture object directly.

 

[smozgur]

rlv01. You did it, you showed me what is wrong with my code: I did not set the picture's .Top property! I just did not think of that. So I learned something thanks to your post...you have to set a picture's .Top and .Left properties to place it where needed! Thank you!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[OaklandJim]

I suppose. But I came to the solution as a result of the post that you marked. It did not answer my question directly. My question was about positioning the picture and why .Left (only) was not positioning it. The code posted made me realize that one needs to set the .Left AND .Top properties of a picture to position it. The "solution" was merely my recognition of that fact. So the code posted was not the solution per se. In fact, I did not use it as-is. I did think about future viewers of the post when I marked mine. I meant well.

 

[smozgur]

        oPicture2.Left = rToCell.Left
        oPicture2.Top = rToCell.Top

The .Top property has been used in the marked solution as it should be.

Also, it does not matter if the provided sample codes are used as they are. They often simply serve as a guide to solve the specific problem. Everybody can find elements in the code that suit their own needs and address the issue that brought them to this question. The provided sample is a well-written and commented code that can be helpful for future readers. It offers a generic solution to the question of "How to place a picture into a specified cell?" Moreover, it provides valuable information about working with objects in VBA, which can be particularly useful for new users as a life-saving resource.

And:

rlv01. You did it, you showed me what is wrong with my code: I did not set the picture's .Top property! I just did not think of that. So I learned something thanks to your post...you have to set a picture's .Top and .Left properties to place it where needed! Thank you!

As a result, the marked solution has been adjusted accordingly to help future readers.

 

[OaklandJim]

smozgur. Which post should be marked as solution in this post? How do I convert a picture's .Width to .Column.Width so I can embed it into a cell whose .ColumnWidth must be set?