+ Reply to Thread
Results 1 to 11 of 11

Need help with VBA code to exchange one image for another

  1. #1
    Registered User
    Join Date
    02-29-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    21

    Need help with VBA code to exchange one image for another

    Hi all,

    Hoping to get a little help with a VBA issue I'm having. I've looked all over the internet finding a lot of code snippets that are close, but nothing that is workable for my needs.

    Here are the specifics...

    -I have a list of image files with file paths in excel.
    -I need a macro that will insert the first picture in the list and name the object "mypic".
    -Every time, I run the macro, I want it to replace the old pic with the next pic in the list, callilng it "mypic" and preserving the aspect ratio of each new image, but placing them in the same location in the sheet.

    I think I've got the cycling down the list working okay, but deleting and replacing the images is giving me a huge headache and wasting my whole day.

    Many thanks in advance for any help and a happy 2018 to everyone!

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,011

    Re: Need help with VBA code to exchange one image for another

    This should get you close:
    - see attached workbook
    - sheet MyList contains list of images and paths
    - image in sheet "Image" is replaced with next on list
    - message box tells user if image is not found
    - image is tagged in column C when image is used

    To run in attached workbook:
    - add image names including extension in sheet MyList (Column A)
    - insert path in column B
    - run macro with {CTRL} k

    In general module:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Registered User
    Join Date
    02-29-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    21

    Smile Re: Need help with VBA code to exchange one image for another

    That's absolutely awesome Kev.

    I really appreciate your time with it and will definitely be able to use it.

    Many thanks and happy new year.


  4. #4
    Registered User
    Join Date
    02-29-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need help with VBA code to exchange one image for another

    Hi Kev,

    I've just applied your code to my purpose and it works well, but is there a way to specify the image height, and have the width fluid so it keeps the aspect ratio and doesn't distort the image?

    Many thanks again.

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,011

    Re: Need help with VBA code to exchange one image for another

    Yes
    - do you want the height to always be the same value? (with width adjusted automatically to maintain aspect ratio)

  6. #6
    Registered User
    Join Date
    02-29-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need help with VBA code to exchange one image for another

    Hi Kev,

    Yes, I'd like the height of each image to match a setting I enter in the VBA code, and the width automatically adjust so as not to skew the image.

    Many thanks for your ongoing help Kev. You're great.


  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,011

    Re: Need help with VBA code to exchange one image for another

    REPLACE
    Please Login or Register  to view this content.
    WITH
    Please Login or Register  to view this content.
    and amend the 100 to your chosen standard height


    OR to automatically set image height to that of the cell
    Please Login or Register  to view this content.
    Last edited by kev_; 01-05-2018 at 05:44 AM.

  8. #8
    Registered User
    Join Date
    02-29-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need help with VBA code to exchange one image for another

    Thanks Kev.

    That works great.

    Only thing is that images are still being skewed where the width of the image is more than the width of my Excel cell.

    I've adjusted your code a little (I'll insert other code to handle image cycling later), but my end goal is to have each image appear within the confines of the Excel cell, so if the width is more than the width I have, it should shrink the image width but preserve the aspect ratio by shrinking the height too. The same holds true if the height is more than my cell's height.

    I also added a bit of code at the bottom which was intended to centre the image within my cell area, but it isn't working for some reason.

    Any chance I can trouble you for a bit more help?

    Many thanks Kev.

    Const ImageCellRef = "AD25"
    Const CurrentImage = "MyPic"
    Const ImageSheet = "YTShow"
    Const ListSheet = "YTShow"
    Sub show_Button11_Click()

    Dim ImageName As String, ImagePath As String
    Dim sh As Worksheet: Set sh = Sheets(ImageSheet)
    Dim ws As Worksheet: Set ws = Sheets(ListSheet)

    'delete current image
    sh.Activate
    On Error Resume Next
    sh.Shapes(CurrentImage).Delete
    On Error GoTo 0
    sh.Range(ImageCellRef).Select

    'next image (insert my image cycling code here)
    ImagePath = Range("AF66")

    'insert image
    On Error Resume Next
    sh.Pictures.Insert(ImagePath).Select
    With Selection
    .Name = "MyPic"
    .ShapeRange.LockAspectRatio = msoTrue
    .ShapeRange.Height = ActiveCell.Height
    End With
    'Rng.Value = "used"
    sh.Range("AD25").Select

    '============================================

    With Range("AD25")
    MyPic.Left = .Left + ((.Width - MyPic.Width) / 2)
    MyPic.Top = .Top + ((.Height - MyPic.Height) / 2)
    End With


    End Sub

  9. #9
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,011

    Re: Need help with VBA code to exchange one image for another

    It was quickest for me to modify the code I provided in post#2
    - amend to suit your requirements

    The image is made as large as possible to fit in the cell without skewing
    - the image width is set to the cell width
    - if the image is too tall its height is set to the cell height

    The image is centred in the cell
    - the top of the image is set as the top of the cell plus half the difference between the cell height and image height
    - the left of the image is set as the left of the cell plus half the difference between the cell width and image width

    Please Login or Register  to view this content.

    The above code assumes that you want the image as large as possible but still fit inside the cell.
    If you do not want the image to be increased above its original size then
    replace:
    Please Login or Register  to view this content.
    with:
    Please Login or Register  to view this content.
    Last edited by kev_; 01-08-2018 at 02:00 AM.

  10. #10
    Registered User
    Join Date
    02-29-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need help with VBA code to exchange one image for another

    That's awesome Kev.

    I'll try to implement tomorrow and see how it looks.

    I really, really, really appreciate your time and help.

  11. #11
    Registered User
    Join Date
    02-29-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Need help with VBA code to exchange one image for another

    Hi Kev,

    I just used your code and it works perfectly.

    Thanks so much for your help.

    Your support has been amazing and very appreciated.


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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