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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
You should use code tags and mention that you have the same question on another forum.
 

needhelp9009

New Member
Joined
Mar 30, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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.
 

needhelp9009

New Member
Joined
Mar 30, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

how to edit the thread? or do i have to redo? So i report myself to remove this post and redo?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
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.
 

needhelp9009

New Member
Joined
Mar 30, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
You just need to reply to the thread & include the link in that reply.
 

needhelp9009

New Member
Joined
Mar 30, 2021
Messages
7
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..
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.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Were the pictures that you insert rotated and saved? (Right click on Picture, select "Size and Properties" and "Rotation")
 

Forum statistics

Threads
1,141,068
Messages
5,704,094
Members
421,327
Latest member
Msh

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
Top