Results 1 to 6 of 6

Resizing a picture in excel using VBA

This is a discussion on Resizing a picture in excel using VBA within the Excel Questions forums, part of the Question Forums category; Hello, As the title goes this is as simple as it gets. The macro worked good when I was using ...

  1. #1
    Board Regular
    Join Date
    Nov 2007
    Posts
    50

    Default Resizing a picture in excel using VBA

    Hello,

    As the title goes this is as simple as it gets. The macro worked good when I was using excel 2003 but now that I have 2007 everything works for except the resizing (Picture.Width & Picture. Height).

    Code:
    Sub AddImage()
    Dim strPath As String
    Dim strFile As String
    Dim Picture As Object
    Dim Response As String
    Dim C As Range
    strPath = "C:\\Photos\"
     
    For Each C In Range("A1", Range("A65536").End(xlUp))
        If C <> 0 Then
            With C.Offset(0, 1)
                Set Picture = Nothing
                On Error Resume Next
                Set Picture = Sheets("Raw Pix").Pictures.Insert(strPath & C.Value & ".jpg")
                Picture.Top = .Top
                Picture.Left = .Left
                Picture.LockAspectRatio = msoTrue
                Picture.Width = 157
                Picture.Height = 138
    
            
        End With
        End If
         
    NextC:
    Next C
    End Sub
    I have tried to record some macros while I am resizing the pictures but to no avail the macros are blank.

    Please help

    Thanks!

  2. #2
    Board Regular
    Join Date
    Apr 2004
    Location
    Still in Kansas
    Posts
    367

    Default Re: Resizing a picture in excel using VBA

    Try changing these lines:

    Code:
                Picture.ShapeRange.LockAspectRatio = msoTrue
                Picture.ShapeRange.Width = 157
                'Picture.ShapeRange.Height = 138
    Note that since you're locking the aspect ratio, you only need to specify just the width or the height.

    HTH!
    Work - 2003 & 2007. Home - 2007.

  3. #3
    Board Regular
    Join Date
    Nov 2007
    Posts
    50

    Default Re: Resizing a picture in excel using VBA

    I am sorry I do not want to lock the apect ratio.

    Does anybody know what the VBA code for "Relative to original picture size" AND "Move and size with cells"

    Thanks!!!

  4. #4
    Board Regular
    Join Date
    Apr 2004
    Location
    Still in Kansas
    Posts
    367

    Default Re: Resizing a picture in excel using VBA

    In that case, try:
    Code:
                Picture.ShapeRange.LockAspectRatio = msoFalse
                Picture.Placement = xlMoveAndSize
                Picture.ShapeRange.Width = 0.5 * Picture.Width
                Picture.ShapeRange.Height = 0.75 * Picture.Height
    And adjust percentages as needed.
    Work - 2003 & 2007. Home - 2007.

  5. #5
    Board Regular
    Join Date
    Nov 2007
    Posts
    50

    Default Re: Resizing a picture in excel using VBA

    SWEEEET!!!!

    I had been tinkering with your code and made some adjustments but this worked..

    Code:
    Picture.Top = .Top
                Picture.Left = .Left
                Picture.ShapeRange.LockAspectRatio = msoFalse
                Picture.Placement = xlMoveAndSize
                Picture.ShapeRange.Width = 157
                Picture.ShapeRange.Height = 138
    Thank you, DSCG, very much for your help!!!!

  6. #6
    Board Regular
    Join Date
    Nov 2007
    Posts
    50

    Default Re: Resizing a picture in excel using VBA

    I that I had but I really don't.

    It seems that the width is works fine but what makes that the height continue to change? I even tried to size some photos to the same size but it seems by the time it gets to the last row its not even with the same row it should be.

    Any picture wizards out there?

    Thanks in advance!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com