Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Insert Image - Center & Zoom

  1. #1
    Join Date
    21st July 2006
    Location
    Florida, USA
    Posts
    186

    Insert Image - Center & Zoom

    I have a worksheet (Covers) that is going to be the front and back covers for a binder.

    The title/text portion is automatically filled from cells elsewhere in the workbook.

    I have a button to bring up a userform that allows one to select the images (3 .jpg images) you wish to use on the cover pages.

    I have code that successfully brings up the userform, and allows image selection and preview within the userform.

    Now I want to insert these selected images into the worksheet, centered on specified cells and sized (zoom) to a maximum width/height (whichever is reached first).

    Can a blank Picture be inserted with its size specified/fixed, then use code (Image1 = LoadPicture?) to change what is displayed?
    Or do I have to calculate the size, center it on the page, and insert the image each time?

    The code (in part) for the UserForm looks like:
    VB:
    Private Sub SelFcvrImg_Click() 
         
        Dim FCpicName As Variant 
        ChDir ("S:\Dan\Builder Logos-Photos\") 
        FCpicName = Application.GetOpenFilename(Title:="Select an Image!", _ 
        fileFilter:="Pictures (*.bmp;*.gif;*.tif;*.jpg),*bmp;*gif;*.tif;*.jpg") 
        If FCpicName <> False Then InsertImgForm.FCoverImgPrvw.Picture = LoadPicture(FCpicName) 
         
    End Sub 
    
    
    The UserForm has an Image (preview) with PictureSizeMode set to zoom.
    I'm really after the same thing embedded in the sheet...
    Any tips on how to best approach this?
    Last edited by Bryan021; January 25th, 2007 at 05:06.
    Bryan
    "Simplicity is the ultimate sophistication." Leonardo da Vinci (1452-1519)

  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Insert Image - Center & Zoom

    Something linke this maybe.
    VB:
    With Range("C3:E10") 
        ActiveSheet.Shapes.AddPicture "C:\temp\happy.bmp", True, True, .Left, .Top, .Width, .Height 
    End With 
    
    
    Change the filename and range of cells you want the image inserted over.

    Cheers
    Andy


  3. #3
    Join Date
    21st July 2006
    Location
    Florida, USA
    Posts
    186

    Re: Insert Image - Center & Zoom

    Andy, I get an "File not found" error, yet the image shows in the preview nicely...
    VB:
    Private Sub SelFcvrImg_Click() 
         'Select Image
        Dim FCpicName As Variant 
        ChDir ("S:\Dan\Builder Logos-Photos\") 
        FCpicName = Application.GetOpenFilename(Title:="Select an Image!", _ 
        fileFilter:="Pictures (*.bmp;*.gif;*.tif;*.jpg),*bmp;*gif;*.tif;*.jpg") 
        If FCpicName <> False Then InsertImgForm.FCoverImgPrvw.Picture = _ 
        LoadPicture(FCpicName) 
         
    End Sub 
     
    Private Sub InsrtAllImgButton_Click() 
         'Insert Image
        With Range("A13:I41") 
            ActiveSheet.Shapes.AddPicture "FCpicName", _ 
            True, True, .Left, .Top, .Width, .Height 
        End With 
         
        InsertImgForm.Hide 
         
    End Sub 
    
    
    As a side, what are the two "True" values for?... visibility, ?
    Last edited by Bryan021; January 25th, 2007 at 21:46.
    Bryan
    "Simplicity is the ultimate sophistication." Leonardo da Vinci (1452-1519)

  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Insert Image - Center & Zoom

    The variable FCpicName is empty and does not contain a filename.

    VB:
    Private Sub SelFcvrImg_Click() 
        Dim FCpicName As Variant 
         'Select Image
        ChDir ("S:\Dan\Builder Logos-Photos\") 
        FCpicName = Application.GetOpenFilename(Title:="Select an Image!", _ 
        fileFilter:="Pictures (*.bmp;*.gif;*.tif;*.jpg),*bmp;*gif;*.tif;*.jpg") 
        If FCpicName <> False Then InsertImgForm.FCoverImgPrvw.Picture = _ 
        LoadPicture(FCpicName) 
         
    End Sub 
     
    Private Sub InsrtAllImgButton_Click() 
        Dim FCpicName As Variant 
         
        FCpicName = Application.GetOpenFilename(Title:="Select an Image!", _ 
        fileFilter:="Pictures (*.bmp;*.gif;*.tif;*.jpg),*bmp;*gif;*.tif;*.jpg") 
        If FCpicName <> False Then 
             'Insert Image
            With Range("A13:I41") 
                ActiveSheet.Shapes.AddPicture FCpicName, _ 
                True, True, .Left, .Top, .Width, .Height 
            End With 
        End If 
        InsertImgForm.Hide 
         
    End Sub 
    
    
    The 2 TRU arguments are;

    LinkToFile Required MsoTriState. The file to link to.
    SaveWithDocument Required MsoTriState. To save the picture with the document.

    Cheers
    Andy


  5. #5
    Join Date
    21st July 2006
    Location
    Florida, USA
    Posts
    186

    Re: Insert Image - Center & Zoom

    Why would it be empty?
    It works with LoadPicture(FCpicName) in the preceding sub.
    Does LoadPicture empty it?
    Last edited by Bryan021; January 25th, 2007 at 22:28.
    Bryan
    "Simplicity is the ultimate sophistication." Leonardo da Vinci (1452-1519)

  6. #6
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Insert Image - Center & Zoom

    The LoadPicture does not clear it.

    The variable is declared within that routine and therefore only exists in that routine when that routine is executed. Search forum for Scope and Lifetime of Variables.

    Cheers
    Andy


  7. #7
    Join Date
    21st July 2006
    Location
    Florida, USA
    Posts
    186

    Re: Insert Image - Center & Zoom

    A curiosity;
    Whenever I type or paste the following,
    VB:
    True, True, .Left, .Top, .Width, .Height 
    
    
    "Top" automatically changes to ".top", yet the others do not change capitalization???
    Bryan
    "Simplicity is the ultimate sophistication." Leonardo da Vinci (1452-1519)

  8. #8
    Join Date
    21st July 2006
    Location
    Florida, USA
    Posts
    186

    Re: Insert Image - Center & Zoom

    Quote Originally Posted by Andy Pope
    The LoadPicture does not clear it.

    The variable is declared within that routine and therefore only exists in that routine when that routine is executed. Search forum for Scope and Lifetime of Variables.
    OK, I'm getting somewhere now...
    I declared them Public, and changed the routines from Private Sub to Sub...
    The selection and insertion now work, so I only have to get the sizing portion sorted...
    The way it is now, it stretches the image to fit the entire Range, whereas I want it to re-size (zoom) the image fit Range, without changing the aspect ratio.
    VB:
    Public FCpicName As Variant 
     
    Sub SelFcvrImg_Click() 
         
         'Select Front Cover Image
         
        ChDir ("S:\Dan\Builder Logos-Photos\") 
        FCpicName = Application.GetOpenFilename(Title:="Select an Image!", _ 
        fileFilter:="Pictures (*.bmp;*.gif;*.tif;*.jpg),*bmp;*gif;*.tif;*.jpg") 
        If FCpicName <> False Then InsertImgForm.FCoverImgPrvw.Picture = _ 
        LoadPicture(FCpicName) 
         
    End Sub 
     
    Sub InsrtAllImgButton_Click() 
         
         'Code to insert pictures in sheet
        With Range("A13:I41") 
            ActiveSheet.Shapes.AddPicture _ 
            (FCpicName, True, True, .Left, .top, .Width, .Height).Name = "FCpic" 
        End With 
         
        InsertImgForm.Hide 
         
    End Sub 
    
    
    Bryan
    "Simplicity is the ultimate sophistication." Leonardo da Vinci (1452-1519)

  9. #9
    Join Date
    21st July 2006
    Location
    Florida, USA
    Posts
    186

    Re: Insert Image - Center & Zoom

    OK, I'm still working on the sizing portion of this.

    Since AddPicture does not seem to allow me to maintain original size or aspect ratio, I figure I should be able to extract the width and height from the image after the GetOpenFilename, and then use those in AddPicture?

    This code does not work, but will serve to give you the idea of what I'm trying to do.
    VB:
    Img1 = Application.GetOpenFilename(Title:="Select an Image!", _ 
    fileFilter:="Pictures (*.bmp;*.gif;*.tif;*.jpg),*bmp;*gif;*.tif;*.jpg") 
     
    ImgW = Img1.Width 
    ImgH = Img1.Height 
     
    With Range("B2:G17") 
        ActiveSheet.Shapes.AddPicture _ 
        (Img1, True, True, .Left, .Top, ImgW, ImgH).Name = "Img1" 
    End With 
    
    
    How should this be coded?
    Bryan
    "Simplicity is the ultimate sophistication." Leonardo da Vinci (1452-1519)

  10. #10
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Insert Image - Center & Zoom

    VB:
    Sub x() 
         
        Dim vntImageFile As Variant 
        Dim shpImage As Shape 
        Dim rngOutput As Range 
         
        vntImageFile = Application.GetOpenFilename(Title:="Select an Image!", _ 
        fileFilter:="Pictures (*.bmp;*.gif;*.tif;*.jpg),*bmp;*gif;*.tif;*.jpg") 
         
        If vntImageFile = False Then Exit Sub 
         
        Set rngOutput = Range("B2:G17") 
         
        ActiveSheet.Pictures.Insert vntImageFile 
        Set shpImage = ActiveSheet.Shapes(ActiveSheet.Shapes.Count) 
        With shpImage 
            .Name = "Img1" 
            .LockAspectRatio = msoTrue 
            .Width = rngOutput.Width 
            If .Height > rngOutput.Height Then .Height = rngOutput.Height 
             
            .Left = rngOutput.Left + ((rngOutput.Width - .Width) / 2) 
            .Top = rngOutput.Top + ((rngOutput.Height - .Height) / 2) 
        End With 
         
    End Sub 
    
    

    Cheers
    Andy


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Center Inserted Image Within Range
    By edelauna in forum EXCEL HELP
    Replies: 2
    Last Post: April 12th, 2008, 04:03
  2. Insert Image Prompt
    By ecuevas in forum EXCEL HELP
    Replies: 2
    Last Post: June 29th, 2007, 04:19
  3. Insert Image On Button
    By chathabox in forum EXCEL HELP
    Replies: 5
    Last Post: August 16th, 2006, 17:54
  4. center on zoom in a frame
    By STibbetts in forum EXCEL HELP
    Replies: 2
    Last Post: July 17th, 2006, 21:49
  5. Pan/scroll across and zoom an image
    By A9192Shark in forum Excel and/or Access Help
    Replies: 2
    Last Post: May 16th, 2005, 20:45

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
  •  
porno