Problem with inserting picture.

[[email protected]]

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???)


"[email protected]" wrote:
>
> 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

[[email protected]]

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 wrote:
> 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???)
>
>
> "[email protected]" wrote:
> >
> > 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

[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")



"[email protected]" wrote:
>
> 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 wrote:
> > 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???)
> >
> >
> > "[email protected]" wrote:
> > >
> > > 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

--

Dave Peterson