+ Reply to Thread
Results 1 to 7 of 7

EXCEL, PICTURE SHAPE, VBA: Fit Picture Proportionately Inside a Fixed Sized Picture Shape

  1. #1
    Registered User
    Join Date
    04-12-2011
    Location
    San Jose, California
    MS-Off Ver
    Excel 2003
    Posts
    17

    EXCEL, PICTURE SHAPE, VBA: Fit Picture Proportionately Inside a Fixed Sized Picture Shape

    I am building a dashboard that allows for dynamic input and this includes adding and changing a signature image (gif format with transparent background)
    that will be used on multiple worksheets throughout the workbook using picture shapes. All picture shapes, including the primary on the dashboard
    (first sheet) are added using the following utility code at the time of constructing the form. Once the picture shape is added, the utility code is no
    longer necessary.

    Private Sub btnInitPicShape_Click()
    'THIS APPROACH IS USED INSTEAD OF SIMPLY ALLOWING THE USER TO LOAD THEIR FIRST PREFERRED SIGNATURE
    'IMAGE BECAUSE ***FOR SOME REASON*** THE FIRST IMAGE SELECTED AND LOADED INTO A PICTURE SHAPE REMAINS
    'AS A TOP LAYERED IMAGE NO MATTER WHAT SUBSEQUENT IMAGE IS SELECTED AND LOADED. UNTIL I FIND ANOTHER
    'SOLUTION, I HAVE OPTED TO ADD THIS UTILITY CODE AND ITS HOST BUTTON (LATER HIDDEN) TO EACH PERTINENT
    'SHEET. WHERE NECESSARY THIS CODE IS RUN TO ADD A SIGNATURE IMAGE.
    ActiveSheet.Shapes.AddPicture "C:\{path}\EmptyTransparent.gif", True, True, 100, 100, 215, 65
    End Sub



    The following code is what I use, for now, to allow the user to select and load their preferred signature image. It indeed does the job of loading the
    selected image however, and this is my problem with which I need help: I cannot find a way to size the signature image to fit within the picture shape
    (retaining the shape's size) but without distorting the image's in either the horizontal or vertical directions. In other words, I need the signatures
    to fit proportionately inside the statically sized and placed pictures shapes. Similar to the fmPictureSizeModeZoom setting for the PictureSizeMode for
    an ActiveX control.


    Sub Picture1_Click() ' ActiveSheet.'PICTURE 1' is the primary signature image
    Dim strFileToOpen
    strFileToOpen = Application.GetOpenFilename(Title:="Select GIF Signature File", FileFilter:="GIF Files *.gif* (*.gif*),")

    '(ADD FILE VALISATION ERROR HANDLING CODE HERE)

    'GET SIGNATURE FILE (TRANSPARENT GIF)
    With ActiveSheet.Shapes("Picture 1").Fill
    .Visible = msoTrue
    .UserPicture strFileToOpen
    .TextureTile = msoFalse
    End With
    'POPULATE SIGNATURE IMAGE ON SAME SHEET
    With ActiveSheet.Shapes("Picture 2").Fill
    .Visible = msoTrue
    .UserPicture strFileToOpen
    .TextureTile = msoFalse
    End With
    'POPULATE SIGNATURE IMAGE ON A DIFFERENT SHEET
    With Sheets("Sheet2").Shapes("Picture 1").Fill
    .Visible = msoTrue
    .UserPicture strFileToOpen
    .TextureTile = msoFalse
    End With

    End Sub


    I would very much appreciate any assistance or advise in regards to this issue.

    Regards,
    Last edited by lhasha; 06-18-2016 at 08:12 PM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: EXCEL, PICTURE SHAPE, VBA: Fit Picture Proportionately Inside a Fixed Sized Picture Sh

    Can I ask why don't you use ActiveX Image controls instead of Picture objects?
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    04-12-2011
    Location
    San Jose, California
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: EXCEL, PICTURE SHAPE, VBA: Fit Picture Proportionately Inside a Fixed Sized Picture Sh

    AlphaFrog:

    Thanks for responding. I have been trying to but I encounter a very odd behavior doing so. Please look at my thread post: EXCEL, VBA, Image: Image loses transparency after saving woorkbook

    Thanks

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: EXCEL, PICTURE SHAPE, VBA: Fit Picture Proportionately Inside a Fixed Sized Picture Sh

    That would be impossible. The best you can do is to fit height or width and proportionally fit the other dimension.

  5. #5
    Registered User
    Join Date
    04-12-2011
    Location
    San Jose, California
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: EXCEL, PICTURE SHAPE, VBA: Fit Picture Proportionately Inside a Fixed Sized Picture Sh

    Kenneth:

    Thank you. I was pretty certain that is the case. Did you by chance look at my thread post: EXCEL, VBA, Image: Image loses transparency after saving woorkbook
    Perhaps you have some idea?

  6. #6
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: EXCEL, PICTURE SHAPE, VBA: Fit Picture Proportionately Inside a Fixed Sized Picture Sh

    For the aspect ratio fit issue, see this: Fit a picture to a cell. http://www.vbaexpress.com/forum/show...-in-Excel-2007

  7. #7
    Registered User
    Join Date
    04-12-2011
    Location
    San Jose, California
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: EXCEL, PICTURE SHAPE, VBA: Fit Picture Proportionately Inside a Fixed Sized Picture Sh

    Kenneth et al:

    Thank you for your help. Attached find my solution:

    Please Login or Register  to view this content.
    Last edited by lhasha; 06-20-2016 at 10:03 PM. Reason: typo

+ 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. Vba excel to change shape fill from existing picture in another worksheet
    By cookiedesire in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-11-2016, 02:47 AM
  2. VBA code to set PICTURE shape attribute other than .name
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2015, 10:48 AM
  3. Changing a Picture's Shape
    By Shleifer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-12-2014, 07:25 PM
  4. Changing a Picture's Shape
    By Shleifer in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 09-10-2014, 02:18 PM
  5. [SOLVED] deleting picture (shape)
    By TimlmiT in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-30-2014, 10:13 AM
  6. Adding Picture to Shape
    By ando185 in forum Excel General
    Replies: 0
    Last Post: 06-25-2013, 03:27 PM
  7. Shape/picture name in a worksheet
    By neta in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-29-2006, 11:00 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