Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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
    22nd 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:
    Code:
    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 06: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.
    [vba] With Range("C3:E10")
    ActiveSheet.Shapes.AddPicture "C:\temp\happy.bmp", True, True, .Left, .Top, .Width, .Height
    End With
    [/vba]

    Change the filename and range of cells you want the image inserted over.

    Cheers
    Andy


  3. #3
    Join Date
    22nd 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...
    Code:
    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 22: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.

    [vba]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 [/vba]

    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
    22nd 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 23: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
    22nd July 2006
    Location
    Florida, USA
    Posts
    186

    Re: Insert Image - Center & Zoom

    A curiosity;
    Whenever I type or paste the following,
    Code:
    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
    22nd 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.
    Code:
    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
    22nd 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.
    Code:
        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

    [vba]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[/vba]

    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, 05:03
  2. Insert Image Prompt
    By ecuevas in forum EXCEL HELP
    Replies: 2
    Last Post: June 29th, 2007, 05:19
  3. Insert Image On Button
    By chathabox in forum EXCEL HELP
    Replies: 5
    Last Post: August 16th, 2006, 18:54
  4. center on zoom in a frame
    By STibbetts in forum EXCEL HELP
    Replies: 2
    Last Post: July 17th, 2006, 22: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, 21: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