Problem with images moving using VBA Excel 2010

[piashaw]

Hi

Am trying to insert images into a sheet. And tie the picture to the top left of a cell.

It does this without a problem, however when I insert the next image in rows further down, the first image is shifted to the right. It's kept it's top position with the original cell, but lost the left information.

Each time I add a new image all the images above will shift to the right so they are all staggered.

Attached is the code I am using. I tried the various .placement options with no difference. .XlMove but XlMove ANd Size is not appropriate as I am sizing the images to the column width

variables defined elsewhere in the code are: image = image name from other file and f is the row number I am currently inserting the image on.

So my code is as follows:

'image insertion
If image <> "" Then
Dim myPic As Object
Set myPic = ActiveSheet.Pictures.Insert("D:\images\" + image)
With myPic
ActiveSheet.Pictures.Top = ActiveSheet.Cells(f, 2).Top
ActiveSheet.Pictures.Left = ActiveSheet.Cells(f, 2).Left
ActiveSheet.Pictures.ShapeRange.LockAspectRatio = msoTrue
'****.ShapeRange.Height = ws.[a1].RowHeight
ActiveSheet.Pictures.ShapeRange.Width = ActiveSheet.Cells(f, 2).Width
'ActiveSheet.Pictures.Placement = xlFreeFloating
End With
Set myPic = Nothing
End If


Suggestions greatly appreciated. I'm going insane

ps. Please be gentle as I am a novice at VBA. Thanks

 

[Domenic]

Try...

[font=Courier New]    [color=darkblue]Dim[/color] myPic [color=darkblue]As[/color] Picture
    
    [color=green]'[/color]
    '
    '
    [color=green]'[/color]
    
    
    [color=darkblue]If[/color] image <> "" [color=darkblue]Then[/color]
        [color=darkblue]Set[/color] myPic = ActiveSheet.Pictures.Insert("D:\images\" & image)
        [color=darkblue]With[/color] myPic
            .Top = Cells(f, 2).Top
            .Left = Cells(f, 2).Left
            .ShapeRange.LockAspectRatio = msoTrue
            .ShapeRange.Width = Cells(f, 2).Width
            .Placement = xlFreeFloating
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]

    [color=green]'[/color]
    '
    '
    [/font]

 

[piashaw]

Thank you Dominic,

Appears to be working perfectly!!

Can you explain to me, why mine didn't work so I can learn for next time?

Many thanks

Peter

 

[Domenic]

Thank you Dominic,

Appears to be working perfectly!!

You're very welcome!

Can you explain to me, why mine didn't work so I can learn for next time?

That's because in your code you're setting each property (Top, Left, etc.) for the collection of pictures, instead of an individual picture. In other words, ActiveSheet.Pictures refers to the collection of pictures, not just an individual or current one.

 

[piashaw]

Thank you Domenic (spelt correctly this time!)

At least I now ALSO know how to select values for ALL the images, which is very useful too!!

Regards

Peter

 

[Domenic]

Thank you Domenic (spelt correctly this time!)

You're very welcome!

 

[Soldinjo]

Domenic do you know of a way to prevent picture(used as tiled fill in a shape) to tile horizontaly&verticaly(which is a default option - completly stupid if you ask me) when the whole process is done with VBA (Excel 2010) ?