Results 1 to 3 of 3

Resizing Images Brought in Through VBA (Solved)

Threaded View

  1. #1
    Registered User
    Join Date
    06-18-2019
    Location
    Regina, Sask
    MS-Off Ver
    Microsoft Office 365 Business
    Posts
    3

    Resizing Images Brought in Through VBA (Solved)

    I am trying to set it up so that if a user checks a check box, it makes a sheet visible and inserts an image, scaled to fit within a certain area. It works just fine for any image that is landscape oriented where the height is 75% of the width (or less). But when I insert a portrait image I am having issues. At first it was making it too tall, so I adjusted the code to account for the height (and then center the image in the viewport), but now the portrait images lose their aspect ratio and look distorted. I have ShapeRange.LockAspectRatio = msoTrue, so it shouldn't be able to alter the aspect ratio, right?

    Here is the code I have so far.

    Private Sub cbIMG1_Click()
        Dim strFileName As String
        Dim WD As Single
        Dim objPic As Picture
        Dim IMG1 As Shape
        Dim rngDest As Range
        Dim SHT As Worksheet
        On Error Resume Next
            ThisWorkbook.Sheets("Image1").Visible = cbIMG1.Value
            If cbIMG1.Value = True Then
                Set SHT = ThisWorkbook.Sheets("Image1")
                Set IMG1 = SHT.Shapes("IMG1")
                IMG1.Delete
                strFileName = Application.GetOpenFilename( _
                    FileFilter:="Images (*.jpg;*.gif;*.png),*.jpg;*.gif;*.png", _
                    Title:="Please select an image...")
                If strFileName = "False" Then Exit Sub
                objPic.ShapeRange.LockAspectRatio = msoTrue
                Set rngDest = SHT.Range("B5:L27")
                Set objPic = SHT.Pictures.Insert(strFileName)
                With objPic
                    .ShapeRange.LockAspectRatio = msoTrue
                    .Left = rngDest.Left
                    .Top = rngDest.Top
                    .Width = rngDest.Width
                    .Name = "IMG1"
                    If .Height > 345 Then
                        WD = 345 / .Height
                        .Height = 345
                        .Width = objPic.Width * WD
                        .Left = objPic.Left + ((rngDest.Width - objPic.Width) / 2)
                        'SHT.Range("F30").Value = WD *Returns a value for me to confirm the aspect ratio while I run tests
                    End If
                    If .Height < 345 Then
                        .Top = objPic.Top + ((rngDest.Height - objPic.Height) / 2)
                    End If
                End With
            End If
    End Sub
    What am I missing here? Thanks!!
    Last edited by DaveC3BI; 07-02-2019 at 11:07 AM. Reason: Solved the issue

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Data Lookup & Brought Up
    By waheed254 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 01-28-2014, 03:43 AM
  2. Cell value brought back from the end ot a table
    By raphiduani in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-18-2012, 04:10 AM
  3. Data from tables to be brought on sheet
    By beseda2004 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-20-2012, 07:02 PM
  4. Data from tables to be brought on sheet
    By beseda2004 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-04-2012, 08:50 AM
  5. Can a Message box be brought up in the middle of code asking for criteria?
    By duugg in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-04-2009, 11:14 AM
  6. [SOLVED] Inventory Brought Foward
    By jvq in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-16-2006, 01:15 AM
  7. kill formatting brought over from reports
    By widman in forum Excel General
    Replies: 2
    Last Post: 01-06-2006, 12:00 PM
  8. [SOLVED] Comumn Series Brought to Front
    By Phil Hageman in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-24-2005, 02:05 PM

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