Problem with inserting picture.

[Abraham.Olson]

I want to be able to insert a picture in a variety of cells (each cell
is a range object in the object arrayofRanges). Can someone help me
with where I am going wrong? I have a feeling I dont get the whole set
for pictures right.

Sub TestPictureInsert()

'declarations
Dim picQCAnormal As Picture
Set picQCAnormal = _
Worksheet(2).Pictures.Insert("C:\oneCell_normal.bmp")
Dim pic As Picture

'Put a picture the size of each range in each range _
(each range is only one cell)

For each rnge in arrayofRanges
Set pic = picQCAnormal
With pic
.Top = rnge.Top
.Left = rnge.Width
.Width = rnge.Width
.Height = rnge.Height
End With
Next rnge

End Sub

 

[Dave Peterson]

You're only inserting one picture, then moving it to each cell in that range--so
only the last cell in that range has the picture when you're done.

You could import the picture multiple times or import it once and copy it lots
of times.

Option Explicit
Sub TestPictureInsert()

'declarations
Dim ArrayOfRanges As Range
Dim rnge As Range
Dim picQCAnormal As Picture
Dim pic As Picture

'for my testing
With Worksheets(2)
Set ArrayOfRanges = .Range("a1,c3,e5,g7")
End With

Set picQCAnormal = Worksheets(2).Pictures.Insert("C:\oneCell_normal.bmp")

For Each rnge In ArrayOfRanges.Cells
With rnge.Parent 'worksheets(2)
picQCAnormal.Copy
.Paste
Set pic = .Pictures(.Pictures.Count) 'the one just pasted
End With
With pic
.Top = rnge.Top
.Left = rnge.Left
.Width = rnge.Width
.Height = rnge.Height
.Name = "Pic_" & rnge.Address(0, 0)
End With
Next rnge

'clean up the "master picture"
picQCAnormal.Delete

End Sub

And watch your typing. Worksheets(2), not worksheet(2)

Set picQCAnormal = _
Worksheet(2).Pictures.Insert("C:\oneCell_normal.bmp")

And I bet you didn't mean this:
.Left = rnge.Width
(left = width???)

 

[Abraham.Olson]

Thanks Dave, worked perfectly. My typing was a bit sloppy, thanks for
catching that.

Another question, if you happen to know, can one set the transparent
color for the imported picture? Also, if I wanted to make the workbook
that this code is a part of into an add-in, how would I keep the
picture with it?

Thanks again,

-Abe

 

[Dave Peterson]

If you rightclick on the picture, you can choose format picture.

Then experiment with the Fill|color|transparency settings.

(I've never had good luck with that, though.)

Put the picture on one of the sheets in the addin.

Copy it from there.

And still paste it into the worksheet you want.

Set picQCAnormal _
= thisworkbook.Worksheets("pictureSheet").Pictures("PictNameHere")