Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in Picture alignment
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in Picture alignment
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in Picture alignment
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: .topatl.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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in Picture alignment
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" wrote: 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: .topatl.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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in Picture alignment
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" wrote: 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" wrote: 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: .topatl.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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in Picture alignment
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. "joel" wrote: 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" wrote: 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: .topatl.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" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in Picture alignment
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" wrote: 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. "joel" wrote: 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" wrote: 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: .topatl.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" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in Picture alignment
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" wrote: 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" wrote: 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. "joel" wrote: 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" wrote: 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: .topatl.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" wrote: 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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in Picture alignment
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alignment problem with spreadsheet | New Users to Excel | |||
Picture alignment | Excel Programming | |||
Col alignment problem | Excel Programming | |||
Command Button Alignment Problem | Excel Programming | |||
Text format alignment problem | Excel Programming |