+ Reply to Thread
Results 1 to 65 of 65

to insert picture and cropped to fit in the boxes

  1. #1
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    to insert picture and cropped to fit in the boxes

    I am trying to insert pictures on active cells to create a picture report. however, as I go down the activesheet,i.e. row number increase, the picture keeps moving lower from the active cell I am trying to add the picture in. it doesn't also fit nicely in the boxes.
    In the picture report, I click on the green cell(picture1) and run the below code to insert the picture into the outlined box as per picture 2. the photos does not keep inside the outlined for the 2nd and 3rd picture, it is shifted down.

    Please Login or Register  to view this content.
    Capture2.JPG
    Capture.JPG
    Last edited by needhelp9009; 03-08-2021 at 02:32 AM.

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: inserted picture keeps moving down

    At the top, click on "Forum Rules" and read about code tags. I think it is #2. Change your Post #1 accordingly.
    You set the Range (Rng) for two cells (ActiveCell, ActiveCell.Offset(1, 1)), but the picture shows 14 Rows!
    Also, there is no "Active Cells". One Cell is the Active Cell. The Top Left Cell of a (selected) range.

    Explain what you want to do. How many rows per picture, how many columns per picture, how many rows between bottom and top of next picture.
    Last edited by jolivanes; 03-08-2021 at 02:13 AM.

  3. #3
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: inserted picture keeps moving down

    Hi Sorry I didn't read the rules clearly before posting. hope this is easier to understand. I have a sheet consisting of 3 boxes down and 2 boxes across. I want to fill them with pictures. the top most picture more or less fits into the box, but as we move downwards to the 2nd or 3rd, the picture also somehow gets offset downwards.

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    I did not receive notice about your 2nd post (Post #3) until just now.
    It looks like your "boxes" are borders around a Range.
    Select a top left cell in a "Box" and run the macro. Select another top left cell and run the macro again. There should be no other Shapes named as a Picture in the sheet. Just the pictures you bring in.
    Try it and let us know.
    If you want to do it without selecting a cell, which would be better, you have to let us know.

  6. #6
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    yes. the boxes are borders around a range. i made just color adjustments to explain better. each page have 6 picture. on the left side, i have run the code 3 times, each selecting the green box to fill the orange box. for the actual item, the pictures are not the same. the picture does not site perfectly in the "boxes" which i have made the border.

    Thank you so much for the help. i really appreciate it. I am new to macros and have been scratching my head and trying to read up the past week.

    Attachment 722307

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    That is not the right way to attach a file. At the bottom right hand side, click on "Go Advanced" and follow the popups.
    Are you satisfied with the result or are more changes needed?
    If this is it, mark the thread as solved.

  8. #8
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi, the problem is still not solved. the picture keeps shifting down still.
    will reattached the attachment.
    Capture3.JPG
    I clicked on "go advanced" and reattached the picture.

    by clicking on the green cell, it makes it the active cell. I then have a button to run the macro(the blue button at the top right)
    the top most picture does not fit in the box perfectly. the bottom juts out and the right is a tiny bit too small.
    when I run the macros to add the middle picture, you can see that the picture is out of the box. it is shifted down slightly.
    for the bottom picture, it shift downwards even more. is there anyway for the pictures to fit in the boxes perfectly? either though resize or crop?

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Attach your workbook, not pictures.
    What did you change in the code? Looks like you're using your old code.
    Are you selecting one picture or multiple pictures?
    Last edited by jolivanes; 03-09-2021 at 03:01 AM.

  10. #10
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    attached excel workbook.
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Works as advertised as far as I can see.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi Jolivanes,

    the photos is able to be inserted. the problem I have is that for the top row, the pictures are inside the box. but the 3rd row of pictures they are only inside the box partially. and as you go down the worksheet, the photos keeps getting lower and lower out of the box.
    the problem is the picture not fitting totally and nicely in the box as you add more pictures down the row.

  13. #13
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Did you try the attachment from Post #11?

  14. #14
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    yes. I tried the attachment from post #11. it is still the same.

  15. #15
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Did you add anything to the code like "PicturesCompress"? like you did before?

  16. #16
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    I just tried with both PicturesCompress and without it. Pictures are all lined up perfectly.
    And both files with 27 pictures each are the same size.
    I really cannot see what the problem is. Maybe someone else has seen this before and has an answer.

  17. #17
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi Jolivanes. not sure what happened. but if I change all the range to activecell with no offset in location or offset size. and I change the cell size to be the entire "box" seems like it works.

    Thank you.. the next issue is if I try to put in a portrait picture, it does not get autosized. Should I create a new post and mark this as solved?

  18. #18
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Re: "with no offset in location or offset size". Where does this come from?

    Re: "the next issue is if I try to put in a portrait picture, it does not get autosized"
    I don't know what you mean by that.

  19. #19
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi,

    the problem is back again. not sure why yesterday all the photos were lined up properly. based on post #11, the photos are still not aligned. does it have to do with the excel version?
    Attachment 722827
    Attached Images Attached Images

  20. #20
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    It looks like you're using your old code again and no, it should have nothing to do with the version as far as I can see because it works on cell locations.
    I have tried it on a different computer with a newer excel version and everything works as required.

    I asked you before what this means: "but if I change all the range to activecell with no offset in location or offset size" but you choose not to answer that.
    So what did you change in the code?
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    the picture below is from the excel file you send me.
    i clicked B3 first and clicked the button, selected the image i wanted and clicked open. it seems to be more or less inside the box.
    after that, i clicked on B35 and did the same steps. but somehow the photo shifts downwards.
    please ignore "but if I change all the range to activecell with no offset in location or offset size".
    What i did yesterday was to change the range part of the code. attached the excel.
    Attached Images Attached Images
    Attached Files Attached Files

  22. #22
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    I really don't know why it does not work on your computer.
    If you change this
    Please Login or Register  to view this content.
    to this
    Please Login or Register  to view this content.
    Does that make a difference?

  23. #23
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    it is the same. the error of the picture shifting downwards is still there.
    the funny thing is, yesterday when i was playing around with the excel in post #21. it was suddenly able to work. this morning when i tried it again, the problem resurfaced.

  24. #24
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    I am just trying to figure out why it would do that on your computer.
    If you change the Merge and Center Cells(B17:G17, I17:N17, B33:G33 as well as all the other ranges to Center Across Selection (Format Cells - Alignment - Horizontal - Center Across Selection), does that make a difference on the original code?
    BTW, you should not use "Merge and Center" anyway because it is an accident waiting to happen.
    From Duncan Williamson, Author of eight Excel books. Excel trainer.
    Rule number one: do NOT merge cells in Excel. Yes, you can do it but it is not good practice for various reasons.

  25. #25
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Run this code to Unmerge and change to Center Across Selection.
    Please Login or Register  to view this content.

  26. #26
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    ok. if i do not merge the cells, then i will change the row height and width so the active cell is a single cell.
    removed all the merge and changed the entire worksheet to Center Across Selection. the error is still there.
    Attached Files Attached Files

  27. #27
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    I put pictures in the attachment from the previous Post after changing part of the code to
    Please Login or Register  to view this content.
    and all the pictures lined up perfectly.
    I hope someone that has an answer to this and let us know because I am out of answers with the exception of googling until we find an answer.

    BTW how come you have .xls files. Should you not work with .xlsm type files?

    If you set your Zoom to 100%, does that make a difference?
    Last edited by jolivanes; 03-11-2021 at 01:43 AM.

  28. #28
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    i sent my zoom to 100% but still no difference. Thank you very much for the help. i have been googling for a couple of weeks already.

  29. #29
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Just to try things. Open a new workbook, put this code in it and run this macro.
    Select multiple different photo's or pictures in the pop-up, like 15+, and see if they line up at the top of cells.
    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    done. Now all the photos appear in the same cell. it just overlaps the old picture.

  31. #31
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Do you have access to another computer with another version of excel?
    If so, try the Post #29 suggestion. When the pop-up window is open, hold down the Ctrl key and select a bunch pictures and click on open
    Each picture should be 14 Rows in Height and 6 Columns in Width. There should be two Rows vertically and 1 Column horizontally between pictures

  32. #32
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    The only other thing that I can think of is the settings.
    Mine are like this. See iif that changes things. Are you working on a laptop?

    Attachment 723028

  33. #33
    Registered User
    Join Date
    03-11-2021
    Location
    Houston, TX
    MS-Off Ver
    MS Office 365
    Posts
    4

    Re: to insert picture and cropped to fit in the boxes

    Haha I registered today, specifically to try to find an answer to this question!

    Here's the cause I found: the gridlines in Excel add a tiny bit of spacing between cells that VBA doesn't take into account when calculating the .Top of a row. This becomes more and more noticeable the further down a sheet you go b/c there are more and more gridlines above.

    Here's a snippet of my code where I subtracted a correction factor of .Row * 1/288 as I move down the worksheet: (I scratched out irrelevant parts that had to do with renaming the images copied from another worksheet)
    Attachment 723078

    The 1/288 is an arbitrary value, not exact, I couldn't find any online documentation pointing to the width of a gridline between two cells.
    Last edited by ddalmazan; 03-11-2021 at 05:12 PM.

  34. #34
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi, I cannot see the attachments Jolivanes and ddalmazan.

    @ddalmazan, was the correction factor able to solve the issue?

  35. #35
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Screen resolution settings 1920 x 1080

    @ddalmazan
    How much does it move if you use "Top of Cell" for every picture?
    I don't know where you found this information but I would really like to see the site where you got this interesting info from.

  36. #36
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi jolivanes, are you able to see ddalmazan's attachment? possible to send a screenshot so I can try out his code?

  37. #37
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    No, I can't. An attachment has to be attached going through "Go Advanced" as you know.
    Attached is mine from posts #32.
    Attached Images Attached Images

  38. #38
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    ok thanks. just have to wait for ddal to reply then. so far, changing the resolution doesn't help.

  39. #39
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Regarding the 1/288.
    The difference between the 2 MsgBox values is the difference for 288 gridlines
    Please Login or Register  to view this content.

  40. #40
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    where should I put all these code into the code we discussed previously?

    Please Login or Register  to view this content.

  41. #41
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Re: "where should I put all these code into the code we discussed previously?"
    You don't. Just copy it into Module1 and run it.
    x will give you the sum of the first 288 individual cell heights and the other MsgBox will give you the top of Cell A289 which I say should be the same as the bottom of Cell A288.
    If there is a difference between the two MsgBox values, you can ascribe that to the sum of the gridline thicknesses!!!!!!!!!!!

  42. #42
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    In your Workbook, delete all previous code and copy this into Module1. (You might want to work with a copy of your original)
    Do not add anything to it or change anything.
    After that, save your workbook.
    Open it and run this code.
    Do it with enough pictures that it is easy to see if they line up or not.
    Please Login or Register  to view this content.

  43. #43
    Registered User
    Join Date
    03-11-2021
    Location
    Houston, TX
    MS-Off Ver
    MS Office 365
    Posts
    4

    Re: to insert picture and cropped to fit in the boxes

    Sorry, still not sure how this forum works, I'll see if inserting works now? Otherwise i'll just type it out.

    Capture.PNG

  44. #44
    Registered User
    Join Date
    03-11-2021
    Location
    Houston, TX
    MS-Off Ver
    MS Office 365
    Posts
    4

    Re: to insert picture and cropped to fit in the boxes

    I recall I chose 288 because it was a multiple of 72 pts, which is what Excel converts 1 inch : 72 pts *vertically* (Width is measured 12 point: 1 inch).

    So I kept going -1/72, -1/144, -1/288 til I got was able to decently remove that "drift", it still wasn't exact (I might of needed to keep going & refining to find the exact multiple) but suited my needs.

  45. #45
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    Hi ddalmazan
    Interesting. Never ran across this problem I do have to say. Mind you, I don't have Office 365.
    I am sure you googled till you were blue in the face for a solution. Is this a known 365 problem?
    What I am wondering about, is there a Zoom setting involved?

  46. #46
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: to insert picture and cropped to fit in the boxes

    In the attached you can see what I mean.

  47. #47
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi ddalmazan, how did you code to factor in the adjustments? I am new to coding so I am not sure where or how to add in the code to do the adjustments.

  48. #48
    Registered User
    Join Date
    03-11-2021
    Location
    Houston, TX
    MS-Off Ver
    MS Office 365
    Posts
    4

    Re: to insert picture and cropped to fit in the boxes

    Quote Originally Posted by jolivanes View Post
    Please Login or Register  to view this content.
    On where you would insert my suggestion in the code above:
    .Top = Cells(j, jj).Top - (j * 1/288)
    ^It's basically shifting the picture up slightly, the further down the worksheet you go, as a way to counter balance the gap you get.


    BUT, I just did a test on your first macro, either my settings have changed when I updated my MS 365 or switched screens this year, and now I'm running the first macro with no issues.
    ^I think in my case I was using a loop to insert images in each row down a list, I'll have to test later on my old code whether that issue has cleared up or not.

  49. #49
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi ddal, I tried inserting the code. It still doesn't shift the picture into the box. once you have tested and are able to remove the "Gap" at the top every time I insert a picture, please let me know. The code I am currently using is:
    Don't really understand the activezoom portion.
    Please Login or Register  to view this content.
    Last edited by needhelp9009; 03-29-2021 at 03:01 AM.

  50. #50
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Are there anyone else out there able to help with this question? I am stumped.

  51. #51
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: to insert picture and cropped to fit in the boxes


    Hi,

    which post # contains the appropriate attachment ?
    If none so join a sample workbook which must reflect exactly your real workbook.

    Attach also a picture you want to insert and well elaborate in details where the picture must fit exactly …

  52. #52
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi Marc L. reattached code

    Please Login or Register  to view this content.
    What i would like is for the photo to be sitting in the cell i select when i run the code.
    Currently the code is ok for pictures inserted in cell B3, it sits quite nicely in the cell. however, as the row number increases as shown in the second picture
    Capture7.JPG
    picture 1

    in row B90, the photo seems to offset out of the cell.
    Capture6.JPG
    picture 2

    thank you for taking time to reply to the post. i really appreciate your help.

  53. #53
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: to insert picture and cropped to fit in the boxes


    As I may dislike some codelines of this code - logic issue to well fit pictures - but to be sure
    I need a least one of your picture - w/o I won't waste any time - even if I presume this is a 365 glitch …

    But you could already achieve a test as your code does not use the same method when a picture is loaded manually
    - easy to check yourself just activating the Macro Recorder ! Like in many threads whatever the forum … -
    so just proceed manually to see if the same issue occurs …

    If not there is a hope : just try in your code the same method you can see in the procedure generated by the Macro Recorder …

  54. #54
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi Marc,

    the photos are just taken off the web. the same issues happens for all the pictures.

    how do i do a auto fit into the cell using the macro recorder? however, if i disregard the size and use the macro recorder and the picture i want, the problem with the picture offset is solved.

    I am open to changing the entire code. the main goal is to auto resize and fit the picture into the activecell.

  55. #55
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: to insert picture and cropped to fit in the boxes


    So just replace the method used to add a picture in your code by the one when operating manually
    as you can see in the code generated by the Macro Recorder.

    For further help read the post #51 - but well this time ! - and do the necessary …

  56. #56
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi Marc, I have read the post #51 a couple of times.
    I have attached the code in post #52. to elaborate further, this excel code is meant to assist me to do a report with photos attached.
    i will click cell B3, run the macros and select a photo on my computer which i just taken. the macro will insert the picture and auto resize the picture into the active cell B3.
    When i try to do it manually and record the macros, I do not know how to auto resize the photo. if i do it manually, the next time i run the macro, the photo inserted will just shrink.

    the problem i have now is, when i try to run the macros to insert the picture. it works well for the pictures in row 3. but as i continue to insert pictures in the cells with bigger row numbers i.e. B30, B40. the picture inserted is offset out of the activecell as per picture 2 in post #52.

  57. #57
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    OIPABKSCTAY.jpg
    attached picture

  58. #58
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Last but not least ? Back to post #51 …


    Quote Originally Posted by Marc L View Post
    which post # contains the appropriate attachment ?
    If none so join a sample workbook which must reflect exactly your real workbook.
    And I must know at least which method is used when a picture is loaded manually on your Excel version …
    No answer is like « No arm, no chocolate ! »
    I'm a very beginner about guessing and as this is very not a mind readers forum …
    Again, I won't waste any time if you do not follow my rules …

  59. #59
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi Marc,

    I have attached the excel file.
    sorry i was not clear on what you wanted.
    Thank you for explaining.

    manually insert picture.
    i click on the active cell, then INSERT > pictures > this device.

    The picture will then appear in the active cell. but it will be too big. So i manually adjust it to the correct size to fit into the Activecell.
    then i stop the macro recorder.
    Attached Files Attached Files

  60. #60
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: to insert picture and cropped to fit in the boxes


    Clear enough for any good reader …

    As there is no code generated by the Macro Recorder in your last attachment, as I won't guess
    so again post at least the code generated by the Macro Recorder in order all lights become green
    so I would be able to compare with the usual way like yet published in threads of any forum …

    According to this last attachment the picture does not very well fit the cell
    - the picture is distorted, the homothety is not respected and outside the cell ! -
    so if it was done manually that means that is an issue on your side - Excel and or Windows versions - so game is over ?

  61. #61
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    i did not include the code generated by the marco recorder as it does not work. i cannot get it to auto size into the active cell.
    all i can do is get it to be inserted into the active cell.

    yes, the picture does not fit into the cell. it was not done manually. the photo was taken off google.
    the problem i have with the code is the picture does not fit well in the cell.
    the picture was added in by running the code. I click on an active cell and run the code.
    the excel version i have is 365.

    the code was compiled by looking at different forums and putting the codes together to try to get it to work. I am new to coding and have been just trying to figure out what went wrong after compiling all the different codes.

  62. #62
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Back to post #53 ! …


    Quote Originally Posted by Marc L View Post
    But you could already achieve a test as your code does not use the same method when a picture is loaded manually
    - easy to check yourself just activating the Macro Recorder ! Like in many threads whatever the forum … -
    so just proceed manually to see if the same issue occurs …

    If not there is a hope : just try in your code the same method you can see in the procedure generated by the Macro Recorder


    And to post #55 :

    Quote Originally Posted by Marc L View Post
    So just replace the method used to add a picture in your code by the one when operating manually
    as you can see in the code generated by the Macro Recorder.

    And to post #58 :

    Quote Originally Posted by Marc L View Post
    And I must know at least which method is used when a picture is loaded manually on your Excel version …
    No answer is like « No arm, no chocolate ! »
    I'm a very beginner about guessing and as this is very not a mind readers forum …
    Again, I won't waste any time if you do not follow my rules …

    And to post #60 :

    Quote Originally Posted by Marc L View Post
    As there is no code generated by the Macro Recorder in your last attachment, as I won't guess
    so again post at least the code generated by the Macro Recorder in order all lights become green
    so I would be able to compare with the usual way

  63. #63
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation


    I just see one code of the helper jolivanes uses the procedure used when a picture is manually loaded under Excel
    - not the one used in your post #52 ‼ - so if it's the same used in your Excel version
    - wanna see your generated code as I can't guess ! - it seems there is no VBA solution …
    You can insert a picture manually then try a VBA code to 'fit' it to the cell.
    This is my last answer until you well answer to my expectation …
    Last edited by Marc L; 04-09-2021 at 06:51 AM.

  64. #64
    Registered User
    Join Date
    03-08-2021
    Location
    singapore
    MS-Off Ver
    365
    Posts
    31

    Re: to insert picture and cropped to fit in the boxes

    Hi Marc,

    i am not sure what you are requesting for when you mention manually loaded under excel.
    if it is using the macro recorder to record. i have attached the excel with the manually insert picture code. (However, i do not know how to resize the picture to fit it into the cell. Also it keeps using the same picture. I want to be able to choose the picture every time i insert a picture.)

    regarding
    post#53, I do not understand what is meant by try manually by activating the Macro recorder. - does it mean i on the macro recorder and try to insert a picture into the cell i want?
    post#55, i tried to replace the code. but when i click the button to run the macros, it uses always the same picture and the size does not fit into the cell. it becomes too small.
    post#58, when you ask which method is used manually. I click the active cell, click insert, then from device then choose the picture i want. Then i manually resize the picture and use crop for the picture to fit into the cell. (i am not trying to ignore your rules, but i am really new and do not understand some jargon. I apologize for my ignorance)
    post#60, i added a new excel with the manual recording of macro?
    Attached Files Attached Files
    Last edited by needhelp9009; 04-12-2021 at 11:44 PM.

  65. #65
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    Yes, the usual method to load a picture in a worksheet is still the same in your Excel 365 version
    like jolivanes yet used in post #29 : ActiveSheet.Pictures.Insert

    So now back to post #55, read & apply & test or if post #29 code does not work on your side
    it seems it's specific to your computer so try on another one …

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-08-2021, 10:27 PM
  2. How to remove all inserted picture at one go?
    By Lewis Koh in forum Excel General
    Replies: 3
    Last Post: 02-24-2012, 12:06 PM
  3. [SOLVED] picture replaced when new picture inserted in webpage
    By Webtekr in forum Word Formatting & General
    Replies: 1
    Last Post: 04-25-2009, 06:17 AM
  4. [SOLVED] Inserted picture name
    By DaveJones in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-08-2005, 10:40 AM
  5. Replies: 0
    Last Post: 11-14-2005, 12:25 AM
  6. [SOLVED] picture 34 was inserted by excel?
    By sophy2 in forum Excel General
    Replies: 1
    Last Post: 10-06-2005, 10:05 AM
  7. How to identify an inserted picture within a formula
    By Jeff Melvaine in forum Excel General
    Replies: 9
    Last Post: 01-30-2005, 09:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1