Cell Resizing with Images

[kpraytor]

Good Morning! Long time reader of the forums, first time posting.

I am having a hard time with resizing the cells in my worksheet based on the image size with VBA. Essentially what I am trying to do is resize the row height based on the height of the image. My problem is when I attempt to get some code from the macro recorder, I see nothing concerning the image height.

All the images are in column A, extending from rows 1 to 2725. Every image is a different size, but all images are contained in their respective cells (don't know how to attach an excel document in a post).

I would truly appreciate some help with this as I do not know how to use VBA to extract the needed image height or how to resize the row based on that image height.

Thanks in advance

PS: If someone could show me how to attach an excel spreadsheet, that might make this process much easier

 

[Andrew Poulsom]

Welcome to MrExcel.

Are these images Pictures or Image Controls?

 

[kpraytor]

Pictures

 

[Andrew Poulsom]

Example:

Sub Test()
    Dim Pic As Picture
    Dim h As Double
    Dim w As Double
    Set Pic = ActiveSheet.Pictures("Picture 1")
    With Pic
        h = .Height
        With .TopLeftCell
            .RowHeight = h
        End With
    End With
End Sub

 

[kpraytor]

Thanks for the snippet of code.

Unfortunately I get Runtime Error '1004: Unable to get the Pictures property of the worksheet class.

Let me add some more information as I'm sure this error is due to my lack of:

The images are contained in "c:\Documents and Settings\praytokl\My Documents\Personal\Stamps\Scott_2007._Standard_Postage_Stamp_Catalogue.vol.1\vol. 1" if that helps at all.

Also, for the names of the images:
-XXX.jpg or -XXX.bmp for any number image under 1000 (eg "-056.jpg")
-XXXX.jpg or -XXXX.bmp for any number image over 1000 (eg "-2150.jpg")

The images are already pasted into the document, so I'm not sure the image location and file naming is of any help.

Please ask if there is any other pertinent information that is needed.

 

[Andrew Poulsom]

Change "Picture 1" to the name of your picture. If you click it its name will appear in the Name box to the left of the Formula bar. Or you could loop around the Pictures collection.

 

[kpraytor]

I could jump up and down that works so perfectly! All I have to do now is loop it for 2,725 times!

Thanks again for the quick responses and simple solutions.

I will continue to stick around and help if I can!

 

[kpraytor]

I'm having some trouble changing the picture name within the loop (incrementing it to the next picture: Picture 1 => Picture 2)

Any ideas?

 

[Andrew Poulsom]

As I said you can loop around the Pictures collection:

Sub Test()
    Dim Pic As Picture
    Dim h As Double
    For Each Pic In ActiveSheet.Pictures
        With Pic
            h = .Height
            With .TopLeftCell
                .RowHeight = h
            End With
        End With
    Next Pic
End Sub

 

[kpraytor]

Thanks again!

Sorry for not reading