Problem in Picture alignment

[Li Jianyong]

Hello Sirs,

I have programmed following code to insert pictures into my sheet. But I
found there is problem in code line:.Top = atl.Offset(0, 1).Top

My pictures exact position shp.top is bigger than the range
-atl.offset(0,1).top

please check for me and provide help

thanks advancely



Code as below:


Sub insertpic()

Dim shp As Picture


Dim atl As Range

Dim lujing As String

lujing = "G:\Autoline Tensioner"

With Application
.ScreenUpdating = False
.EnableEvents = False
End With



For Each atl In Selection

With atl

.RowHeight = 102.75
With .EntireRow
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End With

atl.Offset(0, 1).ColumnWidth = 22.75

Next

For Each atl In Selection


On Error Resume Next

With atl.Offset(0, 1)

Set shp = .Parent.Pictures.Insert(lujing & "\" & atl.Value & ".jpg")
End With

If Err.Number = 0 Then

With shp

.Left = atl.Offset(0, 1).Left
.Top = atl.Offset(0, 1).Top
.Height = 100
.Width = 133
End With

Else

atl.Offset(0, 1).Value = "No existed picture"
atl.Offset(0, 1).Interior.Color = RGB(255, 0, 0)

End If

On Error GoTo 0




Next

With Application
.ScreenUpdating = True
.EnableEvents = True
End With


End Sub

 

[joel]

from:
.Left = atl.Offset(0, 1).Left
.Top = atl.Offset(0, 1).Top

to:
.Left = atl.Offset(0, 1).Left
.Top = atl.Offset(0, 1).Top
.width = atl.Offset(0, 1).width
.height = atl.Offset(0, 1).height

 

[Li Jianyong]

Dear Joel,

I have tried to change the code as your suggestion. But I have the same
problem in the position of the picture. When i work with few item,like 20
items, it works fine. but when I work on many items,like 200 items. The
problem happened. The problem is:
.top>atl.offset(0,1).top . Not as code instruction: .top=atl.offset(0,1).top

please try to figure out the problem for me

Best regards
Li Jianyong

 

[joel]

Is the problem always with the same picture or same row in the worksheet?
Try placing the picture in a different row or putting a different picture in
the row to determine if the problem is with certain pictures or certain rows.


You may have hidden rows or merged cells that is causing the problem. The
picture may be cropped and that is causing the problem. I don't think the
porblem is with the code itself, just something we are not expecting.

 

[Li Jianyong]

Dear Joel,

Thanks for your message. I have tried to check as your suggestion.

I found it seems that the problem happened to certain pictures. I put the
problematic pictures to whatever rows,it comes with the position problem.

And I found new more question. When I run the macro,and it finished. I copy
the part nos. to other rows, the macro still run. why? what is the problem?

 

[joel]

What do you mean by "Still Run". Do you have the macro started by some event
in the workbook like Worksheet Change? it is possible that you are moving
the same pciture in two diffferent places in your macro so one moves it to
the correct size and then a second moves the picture to the wrong size? I
would also check to see if the bad pictures are cropped. Select the picture
and then go to menu View - toolbars - Picture and see if you can move the
crop lines to make the picture larger. If the macro is still running then
try stopping the macro and see which line of code the macro is running. You
can stop the macro by typing Cntl- Break. You may need to type Cntl Break
from both the VBA window and the worksheet menu to get the code to stop.

 

[Li Jianyong]

I have put this macro into my personal.xlsb .When I select the range atl
which I want to insert pictures, I run the macro by click the
personal.xlsb!insertpic.

The "still run" is likely the macro is triggered by "copy the atl ranges to
somewhere"- like workshet_change events. But I did not put my macro into the
worksheet_change events at all.

On the problematic pictures,when I select it,I can drag the crop line to
make it larger or smaller.

Please check and let me know your further suggestion.

Best regards
Li Jianyong

 

[joel]

Are the good pictures cropped or only the bad pictures? wherre did the macro
stop When you did the break? I'm going to be leaving soon from work. Won't
get back for a few hours.

 

[Li Jianyong]

thanks again. could you please send me a email on (e-mail address removed) . I
would like to send you some of my pictures and the worksheet to you by email.
and you try to check it for me and we discuss .

Best regards
Li Jianyong