Problem with picture size when hiding columns

[klarowe]

Right now I have teardown reports that have a simple "insert picture" button to make things easier. It works great with all columns unhidden. However when I try to hide all outside columns to clean up the report a bit, it is throwing the picture sizes all off.
This is what I have now (working fine):

This is what happens when I hide the outside columns:

And here is the code I have:

Sub LoadMultipleImages()
    Dim arrSelected()   As String
    Dim i               As Long
    Dim rngDest         As Range
    Dim objPic          As Object
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .Filters.Add "Images", "*.gif; *.jpg; *.jpeg"
        .InitialFileName = CurDir
        .FilterIndex = 2
        If .Show = -1 Then
            For i = 1 To .SelectedItems.Count
                ReDim Preserve arrSelected(1 To i)
                arrSelected(i) = .SelectedItems(i)
            Next
        Else
            Exit Sub
        End If
    End With
    Set rngDest = Range("A65536").End(xlUp).Offset(4, 0)
    For i = 1 To UBound(arrSelected)
        Set objPic = ActiveSheet.Pictures.Insert(arrSelected(i))
        With objPic
            .ShapeRange.LockAspectRatio = msoTrue
            .ShapeRange.Width = 215#
        End With
        objPic.Cut
        ActiveSheet.PasteSpecial Format:="Picture (GIF)", Link:=False, DisplayAsIcon:=False
        Selection.Left = rngDest.Left
        Selection.Top = rngDest.Top
        Set rngDest = rngDest.Offset(1 + (Selection.Height \ 15))
    Next
End Sub

So how can I alter the size so that it does not take the hidden columns into affect. I have to leave the aspect ratio locked because some pictures will be taken horizontally, and some will be taken vertically. So I can't just lock in a set width and height.

Any help is appreciated.

 

[Gary McMaster]

Just a guess ...

Try adding the line shown in red below.

Gary

Set objPic = ActiveSheet.Pictures.Insert(arrSelected(i))
    
    [COLOR=Red]objPic.Placement = xlFreeFloating 'or =xlMove[/COLOR]

    With objPic
        .ShapeRange.LockAspectRatio = msoTrue
        .ShapeRange.Width = 215#
    End With

 

[klarowe]

unfortunately i guess it won't be that easy... lol. Neither xlFreeFloating nor xlMove worked.

If I were to take a guess, I would say that the current code uses a ratio based sizing setup with relation to the columns. Is there a way to just set a size based off a measurment rather than ratio??? Hopefully that makes sense.

Or the other possibility is that it is somehow losing its "locked aspect ratio". Comparing the 2 examples, it looks as if the width remains the same, but the aspect ratio is lost and the picture becomes elongated...

 

[klarowe]

any ideas?

 

[klarowe]

Holy crap this place is blowing up lately.... only 1/2 a day goes by and I'm already back on page 18... lol.
Any one have some ideas???

 

[klarowe]

One more thing I just noticed that is really throwing me off is that the width of the picture alters with which column a cell is selected. Ie. if a cell in column K is selected the picture will insert smaller than if a cell in column B is selected?????
I'm completely lost.

 

[Andrew Poulsom]

I don't understand how your picture can become elongated when you hide. Maybe when you insert rows yes. But it's size won't change if its Placement is set to xlMove (Move but don't size with cells)

 

[klarowe]

I agree with you 100%... which is why I'm so confused... lol.
Maybe I have it inserted in the wrong part of the code???
I tried it like this:

        Set objPic = ActiveSheet.Pictures.Insert(arrSelected(i))
        With objPic
            .ShapeRange.LockAspectRatio = msoTrue
            .ShapeRange.Height = 161.25
            .ShapeRange.Width = 215.25
            '.Placement = xlFreeFloating
            .Placement = xlMove

And like this:

        Set objPic = ActiveSheet.Pictures.Insert(arrSelected(i))
        With objPic
            '.Placement = xlFreeFloating
            .Placement = xlMove
            .ShapeRange.LockAspectRatio = msoTrue
            .ShapeRange.Height = 161.25
            .ShapeRange.Width = 215.25

And neither one make a difference. And so its clear. When I say I'm hiding columns I am hiding ALL columns from K onward (anything off the first page).

 

[Gary McMaster]

Another guess ...

Maybe get rid of .ShapeRange. That looks like a leftover from the macro recorder.

Your set statement implies a single shape and .ShapeRange implies a group of shapes. Setting properties for a ShapeRange causes all shapes in the group to adopt the change.

Gary

 

[klarowe]

A couple things...
1) The macro is sometimes used to load multiple images at a time... wouldn't that require ShapeRange in order to apply to all images selected?

2) What would I put in place of .ShapeRange? I tried just plain removing it and it gives me an error "Object doesn't support this property or method"

Thanks!