Results 1 to 3 of 3

VBA help to move and resize a picture from one cell to another

Threaded View

markharris2004 VBA help to move and resize a... 06-26-2013, 05:47 AM
ragulduy Re: VBA help to move and... 06-26-2013, 06:04 AM
markharris2004 Re: VBA help to move and... 06-26-2013, 07:04 AM
  1. #1
    Registered User
    Join Date
    02-14-2013
    Location
    wales
    MS-Off Ver
    Excel 2010
    Posts
    64

    Exclamation VBA help to move and resize a picture from one cell to another

    I have a macro to insert a photo into a specific area (J11 - M17) which works perfectly. What I need to do now is possibly insert another picture which requires to move , resize and centre to a different cell (G11 - H17). The code to insert the picture from a file is
    Sub Insert_Pict_1()
    Sub PlacePicInPath(Target As Range, Optional Center As Boolean)
        Dim r As Boolean, p As Picture
        Dim ScaleHeight As Single, ScaleWidth As Single
        Dim ScaleFactor As Double
        
        Application.ScreenUpdating = False
        r = Application.Dialogs(xlDialogInsertPicture).Show
        If Not r Then Exit Sub
        If TypeName(Selection) <> "Picture" Then Exit Sub
        
        With Selection
            ScaleHeight = Target.Height / .Height
            ScaleWidth = Target.Width / .Width
            ScaleFactor = IIf(ScaleHeight > ScaleWidth, ScaleWidth, ScaleHeight)
            .Left = Target.Left
            .Top = Target.Top
            .ShapeRange.ScaleWidth ScaleFactor, msoTrue
            .ShapeRange.ScaleHeight ScaleFactor, msoTrue
        
            If Center Then
                .Left = CSng(.Left + (Target.Width - .Width) / 2)
                .Top = CSng(.Top + (Target.Height - .Height) / 2)
            End If
        End With
        Selection.Name = "Picture1"
    End Sub
    I tried this the code below which sort of works but depending on which ratio of picture is used, it resizes outside of the range required
    Sub Select_Pict1_Then_Move()
    '
    ' Select Picture 1, Resize and Move
    ' Unprotect Sheet
    Sheets("MASTER").Select
    ActiveSheet.Unprotect
    Dim ws As Worksheet
    Dim rng As Range
    Set ws = Sheets("MASTER")
    Set rng = ws.Range("J11:H17")
    
    With ws.Shapes("Picture1")
    .LockAspectRatio = msoTrue
    .Top = rng.Top
    .Left = rng.Left
    .Height = rng.Height
    .Width = rng.Width
    End With
    End Sub
    Some advise would be appreciated
    Last edited by arlu1201; 06-26-2013 at 05:59 AM. Reason: Use code tags in future.

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