2

I have an image in a sheet1 in a merged cell range S1:V8.

I don't know the name of this picture because we paste different pictures in the area each time we create a new Excel file from a template.

I want to copy the picture in this range in this workbook or another workbook, to a sheet called "Database" into cell A6.

I've found several examples but we must know the picture name.

I want the first picture. I imagine I can check if the range has pictures. Then select the first picture and paste it in the other workbook.

The only thing I can't do is to select the picture inside the range for copying.

Bonus features if possible:
- After that I want to resize it to the cell dimension, but maintaining the aspect ratio
- After I want to compress it to 96 dpi
- This file will have several pictures. Is there a way to resize them all to each cell were they are in case of someone modifies the pictures dimension? (there will be a picture in Cell A6, another in cell A7,..., and someone could change the size of the picture in A20 for example)

This post is also in the MrExcel forum.

enter image description here

Community
  • 1
  • 1
Horácio Silva
  • 33
  • 1
  • 6

1 Answers1

1

It's not clear whether the picture you are referring to is "in the cell" or floating above the cells. As far as I know, the only way to have a picture "in a cell" is to have it within the comment of the cell (actually, intended as a background picture for comment text). In either case, the key to manipulating it using VBA would be to get a reference for it. The following code will identify the presence of either of the above cases if you first select the region of cells, as shown in the animated gif. Unfortunately, as far as I know, all you can do is add background pictures to comments via VBA. enter image description here

Option Explicit
Sub testForPicturesOrComments()
Dim p As Picture, r As Range, hasComment As Boolean
For Each p In ActiveSheet.Pictures
  MsgBox ("There's a picture called " & p.Name)
Next p
For Each r In Selection
  On Error Resume Next
  hasComment = r.Comment.Parent.Address = r.Address
  'Reset Run Time Errors
  On Error GoTo 0
  If hasComment Then
  MsgBox ("There's a comment in " & r.Address _
  & " with a shape.ID = " & r.Comment.Shape.ID)
    hasComment = False
  End If
Next r
End Sub
Tony M
  • 1,694
  • 2
  • 17
  • 33
  • Hello Tony. The picture is over the merged range S1:V8 not in a comment. This range does not change. Can I change ActiveSheet. pictures to Activesheet.Range("S1:V8).Pictures ? – Horácio Silva Jul 22 '17 at 20:14
  • If you want to locate the cell(s) the picture is near you can use a property of the picture object like .TopLeftCell or .BottomRightCell -- for example, you can replace the first MsgBox in my code with MsgBox ("There's a picture called " & p.Name & " at " & p.TopLeftCell.Address) -- please mark the question as answered, if you've been helped -- Thanks. – Tony M Jul 22 '17 at 21:45