+ Reply to Thread
Results 1 to 6 of 6

VBA Code/Macro for Sizing A Picture to a Range of Cells

  1. #1
    Registered User
    Join Date
    02-24-2012
    Location
    South Dakota
    MS-Off Ver
    Excel 2010
    Posts
    3

    Question VBA Code/Macro for Sizing A Picture to a Range of Cells

    Problem Description:

    How do I have my VBA "measure the size of my active cell" then size the picture to fit the size of that cell.

    I have a form used for creating work instructions. inside the form I have a macro to insert a picture. The issue I am having, is that I have the macro set to size the picture to a fixed size. Unfortionatly when other people use the form on their computers the picture no longer fits the cells like it does on mine.

    Current State Description:

    The macro inserts the picture into the merged cell that you have selected when you trigger the macro.

    for example if I am in the top row of my form and want to insert a picture I would click into the merged section of 8B:8G and hit control-Z to trigger the macro. if I want to insert the picture into the second row of my form I would click into 9B:9G. etc.

    Here is the current code I am using.

    Sub Add_Picture()

    'Chris Swier

    'Unprotect sheet and turn screen updating off
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect

    'Find picture
    Picture1 = Application.GetOpenFilename("Picture,*.JPG,Picture,*.JPEG,Picture,*.GIF,Picture,*.BMP")
    'edit "("Picture,*.*")" section to add or change visible file types

    'Insert picture into active cell
    ActiveSheet.Pictures.Insert(Picture1).Select
    Selection.ShapeRange.LockAspectRatio = msoFalse
    Selection.ShapeRange.Height = 183
    Selection.ShapeRange.Width = 265

    'Remove picture link and insert picture file
    Selection.CopyPicture
    Selection.Delete
    ActiveSheet.Paste

    'Reprotect sheet and turn screen updating back on
    Application.ScreenUpdating = True
    ActiveSheet.Protect

    End Sub

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: VBA Code/Macro for Sizing A Picture to a Range of Cells

    try
    Please Login or Register  to view this content.
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Registered User
    Join Date
    02-24-2012
    Location
    South Dakota
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VBA Code/Macro for Sizing A Picture to a Range of Cells

    Thanks Tehnexus, the code works well,

    The only thing I changed was the logic for picture sizing so that the picture sizes to the exact borders of the cell regaurdless of the initial size.

    .LockAspectRatio = msoFalse
    If .Width > .Height Then
    .Width = xlRng.Width
    If .Height <> xlRng.Height Then .Height = xlRng.Height
    Else
    .Height = xlRng.Height
    If .Width <> xlRng.Width Then .Width = xlRng.Width
    End If

    What is the purpose of the two following lines?

    .Placement = xlMoveAndSize
    .PrintObject = True
    Thanks,

    CS

  4. #4
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: VBA Code/Macro for Sizing A Picture to a Range of Cells

    Quote Originally Posted by Swierchris View Post
    What is the purpose of the two following lines?

    .Placement = xlMoveAndSize
    .PrintObject = True
    .Placement: When the cell/range is resized or moved the picture will resize/move too.
    .PrintObject: In case you will print the sheet the picture will be printed too

  5. #5
    Forum Contributor
    Join Date
    08-22-2009
    Location
    Manitoba
    MS-Off Ver
    Office 2010
    Posts
    517

    Re: VBA Code/Macro for Sizing A Picture to a Range of Cells

    Been looking for something like this for a while and works 99%! One question, when the window pops up to select a picture from folders and you decide to cancel, the macro throws up and error at "ActiveSheet.Pictures.Insert(Picture1).Select". I am drawing a blank tonight as to how to prevent that from happening. Need an out, if you could help or someone out there? Thanks!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,240

    Re: VBA Code/Macro for Sizing A Picture to a Range of Cells

    thecdnmole, Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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