Results 1 to 4 of 4

restore a resized worksheet Picture to original size with vba

This is a discussion on restore a resized worksheet Picture to original size with vba within the Excel Questions forums, part of the Question Forums category; I've inserted some Pictures onto a worksheet and need to periodically resize them. After resizing, I'd like to later restore ...

  1. #1
    New Member
    Join Date
    Dec 2009
    Posts
    17

    Default restore a resized worksheet Picture to original size with vba

    I've inserted some Pictures onto a worksheet and need to periodically resize them. After resizing, I'd like to later restore the Pictures to their original size. There's a button (Format Picture/Size/Original Size/Reset) that does this, but when I use the Recorder to try to duplicate this function, the code produced uses the saved height and width numbers from when the Picture was originally inserted.

    I don't want to have to keep track of these numbers (especially since Excel already does this by itself automatically) and would like to have the functionality of that Reset button in code. Can I do this?

  2. #2
    Board Regular id107's Avatar
    Join Date
    Apr 2008
    Location
    Aberdeen, UK
    Posts
    578

    Default Re: restore a resized worksheet Picture to original size with vba

    This should reset the pic back to the default size. Edit name(s) as required.
    Code:
    Sub PicReset()
     
        ActiveSheet.Shapes("Picture 1").ScaleHeight 1#, msoScaleFromTopLeft
        ActiveSheet.Shapes("Picture 1").ScaleWidth 1#, msoScaleFromTopLeft
     
    End Sub
    - Craig

    Excel 2007
    the IT department FINALLY let me upgrade...

  3. #3
    New Member
    Join Date
    Dec 2009
    Posts
    17

    Default Re: restore a resized worksheet Picture to original size with vba

    Thanks, Craig. That looks like it should work, but...

    Here's what I've got. It runs ok and picks out all the right Pictures to work on but it doesn't change the Picture size.

    Code:
    Sub resetPictureSize()
      Dim mapsheet As Worksheet
      Set mapsheet = Sheets("MOSmap")
      Dim shp As Shape
      For Each shp In mapsheet.Shapes
        With shp
          If .Name Like "*Picture*" Then
        '       .Select
            .ScaleHeight 1#, msoScaleFromTopLeft
            .ScaleWidth 1#, msoScaleFromTopLeft
          End If
        End With
      Next shp

  4. #4
    New Member
    Join Date
    Dec 2009
    Posts
    17

    Default Re: restore a resized worksheet Picture to original size with vba

    Ok, found it. I forgot to make the scaling relative to original size. This works.

    Code:
    Sub resetPictureSize()
      Dim mapsheet As Worksheet
      Set mapsheet = Sheets("MOSmap")
      Dim shp As Shape
      For Each shp In mapsheet.Shapes
        With shp
          If .Name Like "*Picture*" Then
            .ScaleHeight 1#, True, msoScaleFromTopLeft
            .ScaleWidth 1#, True, msoScaleFromTopLeft
          End If
        End With
      Next shp
    End Sub
    Thanks again.

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