can i insert a picture in an exact cell of a spreadsheet

[Guest]

is there any method or VBA program to insert a picture in an exact cell in an
excel spreadsheet?

 

[Tom Ogilvy]

Sub Macro1()
Range("B9").Select
Set pic = ActiveSheet.Pictures.Insert( _
"C:\Documents and Settings\username\My Documents\My
Pictures\Sample.jpg")
pic.Height = Range("B9").Height
pic.Width = Range("B9").Width

End Sub

should get you started.

 

[Sharad Naik]

Dim Rng As Range
With Worksheets("Sheet2")
Set Rng = .Range("G17")
.Shapes.AddPicture "C:\tph5000.jpg", msoFalse, _
msoTrue, Rng.Left, Rng.Top, Rng.Width, Rng.Height
End With

 

[Ken Macksey]

Hi

If you want to insert a picture in merged cells, you won't get the results
you expect using Toms example. It works great if you resize the cell to
the size you want the picture to be first, but not for merged cells.

Sharad's example will work on merged cells if modified
to include the first and last cell in the merged cells area like this.

Dim Rng As Range
With Worksheets("Sheet1")
Set Rng = .Range("g9:j17")
.Shapes.AddPicture "C:\pictures\Sample.jpg", msoFalse, _
msoTrue, Rng.Left, Rng.Top, Rng.Width, Rng.Height
End With

You can also use a named range by defining a name like "PictureRange"
and then using Toms code modified like this .

Range("PictureRange").Select
Set pic = ActiveSheet.Pictures.Insert("C:\pictures\Sample.jpg")
pic.Height = Range("PictureRange").Height
pic.Width = Range("PictureRange").Width

to place the picture in a merged cell range.

You may find that these options give you greater flexibility in laying out
portions of your worksheet.

HTH

Ken

 

[Sharad Naik]

Now, that's valid point.
When inserting picture in a cell, more chances that
one has merged cells.

Thanks Ken,

Sharad

 

[Tom Ogilvy]

Just to add a slightly different way to handle cells, merged or not.

This works whether the cell is merged or not:

Sub Macro1()
Range("B9").MergeArea.Select
Set pic = ActiveSheet.Pictures.Insert( _
"C:\Documents and Settings\username\My Documents\My
Pictures\Sample.jpg")
pic.Height = Range("B9").MergeArea.Height
pic.Width = Range("B9").MergeArea.Width

End Sub

Similarly Sharad's would be modified like this:

Dim Rng As Range
With Worksheets("Sheet1")
Set Rng = .Range("g9").MergeArea
.Shapes.AddPicture "C:\pictures\Sample.jpg", msoFalse, _
msoTrue, Rng.Left, Rng.Top, Rng.Width, Rng.Height
End With

Then you don't need to know which cells are merged or even if it is merged.

 

[Sharad Naik]

Interesting Tom!
Didn't even know that .MergeArea property exists.

Sharad

 

[Ken Macksey]

Hi Tom

I knew that you would probably have a better way, but that was all I could
come up with that would work. Thanks for the great info as always.

Ken