macros to insert picture in active cell, but picture appears more shifted out of position the higher the row

needhelp9009

New Member
Joined
Mar 30, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
I am using a macros to insert pictures into the active cell.
i.e. click B3, run macros and choose picture from the file. this works well in row 3. However, as I continue adding pictures going down the rows. example B90, the picture appears shifted out of the box..
Capture6.JPG
Capture7.JPG


This is my code:
VBA Code:
Sub test()
Dim myPic As String, Pic As Shape, Rng As Range
Dim a As Double, shp As Shape, x As Long, j As Long, jj As Long


    For Each shp In ActiveSheet.Shapes
        If Left(shp.Name, 7) = "Picture" Then x = x + 1
    Next shp
   
myPic = Application.GetOpenFilename( _
    FileFilter:="Images (*.jpg;*.gif;*.png),*.jpg;*.gif;*.png", _
        Title:="Please select an image...")
    If myPic = "False" Then Exit Sub

Set Pic = Application.ActiveSheet.Shapes.AddPicture(myPic, False, True, 0, 0, -1, -1)

j = ActiveCell.Row
jj = ActiveCell.Column


With Pic
    .Name = "Picture" & x + 1
    .LockAspectRatio = False
    .Left = Cells(j, jj).Left
    .Top = Cells(j, jj).Top - (j * 1 / 100)
    .Height = ActiveCell.Height
    .Width = ActiveCell.Width
   
End With

End Sub
 
Last edited by a moderator:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You should use code tags and mention that you have the same question on another forum.
 
Upvote 0
You should use code tags and mention that you have the same question on another forum.
how to do code tags? as this computer is not mine i cannot download the XL2BB.
for referring to another forum, do i put the whole link into my question?
Thanks for helping to point this out. I am still new with the excel forum.
 
Upvote 0
The same way as you did on the other site.
Highlight your code and click on the sign as below.

1617172017887.png


Copy the path to your thread and paste it here.
 
Upvote 0
how to edit the thread? or do i have to redo? So i report myself to remove this post and redo?
 
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you have posted the question on any other sites, please supply links to those posts.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Hi Fluff, I would like to correct my mistake and add in the link to my question in the other forum. i do not see any edit button. if there is a need to delete and re-ask the question. please can you help me delete and i will redo my question.
 
Upvote 0
You just need to reply to the thread & include the link in that reply.
 
Upvote 0
I am using a macros to insert pictures into the active cell.
i.e. click B3, run macros and choose picture from the file. this works well in row 3. However, as I continue adding pictures going down the rows. example B90, the picture appears shifted out of the box..
View attachment 35638View attachment 35639

This is my code:
VBA Code:
Sub test()
Dim myPic As String, Pic As Shape, Rng As Range
Dim a As Double, shp As Shape, x As Long, j As Long, jj As Long


    For Each shp In ActiveSheet.Shapes
        If Left(shp.Name, 7) = "Picture" Then x = x + 1
    Next shp
  
myPic = Application.GetOpenFilename( _
    FileFilter:="Images (*.jpg;*.gif;*.png),*.jpg;*.gif;*.png", _
        Title:="Please select an image...")
    If myPic = "False" Then Exit Sub

Set Pic = Application.ActiveSheet.Shapes.AddPicture(myPic, False, True, 0, 0, -1, -1)

j = ActiveCell.Row
jj = ActiveCell.Column


With Pic
    .Name = "Picture" & x + 1
    .LockAspectRatio = False
    .Left = Cells(j, jj).Left
    .Top = Cells(j, jj).Top - (j * 1 / 100)
    .Height = ActiveCell.Height
    .Width = ActiveCell.Width
  
End With

End Sub
- this is the link to the same question i asked in another forum.
 
Upvote 0
Were the pictures that you insert rotated and saved? (Right click on Picture, select "Size and Properties" and "Rotation")
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top