VBA - setting uniform size of comment boxes w/ images

[tommyw]

I have a spreadsheet with a bunch of notes that have images appear when you hover over the cell. A bunch of them are getting cut off for some reason and I want to use a macro to set all of them to the same size, something like 5.0" height and 9.1" width. I've tried using other macros I've searched for but they only seem to work for text notes and mine don't have any text. LMK if you can help or if you need more details. Thank you for reading!

 

[tommyw]

Yesss, that worked like a charm! I'm extremely grateful for the help because there are around 1000 images and the spreadsheet has a bit of lag so I was really dreading having to do this manually. The bottom and right corners of some of the images are getting cut off a little bit because the sizes aren't all uniform. If you know of a way to just expand the comment box a bit so everything shows up and some of the smaller images just have some blank space that would be awesome - but it's usable enough as is that I'm going to mark this as solved. Thank you again for all your help!

 

[tommyw]

Oh, I forgot to say that I played around with the width/height a bunch but wasn't able to solve the corners getting cut off that way.

 

[Skyybot]

I'm messing around with my own pictures so I don't know how yours look, but see if this makes a difference

Sub ResizeNotes()
Dim nt As Shape, shps(), i As Integer, ff
ReDim shps(Me.Shapes.Count)
i = 0
For Each nt In Me.Shapes
    Set shps(i) = nt
    Set ff = shps(i).Fill
    shps(i).Width = 500
    shps(i).Height = 300
    ff.Parent.LockAspectRatio = False
    i = i + 1
Next nt
End Sub

 

[Skyybot]

Oh, I forgot to say that I played around with the width/height a bunch but wasn't able to solve the corners getting cut off that way.

That's why I keep messing around with this. I noticed my pics were getting cut off too.