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!

 

[Skyybot]

Place this in the Sheet code module of the Sheet the Notes are in.

Sub Macro2()
Dim wb As Workbook, nt As Shape, ws As Worksheet, shps(), i As Integer
Set wb = Workbooks("Book1"): Set ws = wb.Sheets(2)
ReDim shps(ws.Shapes.Count)
i = 0
For Each nt In ws.Shapes
    Set shps(i) = nt
    shps(i).Width = 3
    i = i + 1
Next nt
End Sub

 

[tommyw]

Do I need to edit anything in the code first? I tried to run it but nothing changed.

 

[Skyybot]

Sorry, I copied that from my test Module and didn't edit it for Sheet. Here's the code to put in your Sheet code module.
Option Explicit

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

 

[tommyw]

No worries, thank you so much for the help. I tried to run this and I got a compile error: invalid use of Me keyword.

 

[Skyybot]

No worries, thank you so much for the help. I tried to run this and I got a compile error: invalid use of Me keyword.

You pasted into Sheet code module?

 

[tommyw]

Sorry for the slow reply, but I just got back to work on this. I did it wrong the first time but I just ran the code and it worked perfectly. The only problem is some of the images that were cut off are still cut off now but they're just bigger. I can edit them manually by going edit note - format comment - size and then manually adjust the height. I have to uncheck lock aspect ratio first. So maybe the problem is that the aspect ratios are locked. If so is there a macro I can use to automatically unlock all of them first before running this macro?

 

[Skyybot]

Are all of the pictures the same size?

 

[tommyw]

They're all reasonably similarly sized but no they're not exactly the same. I think unlocking the aspect ratio and then running the macro you provided would be sufficient though.

 

[Skyybot]

Let me know if this works. I think we can play around with the size to get you desired results. I've added a Lock Aspect Ration statement.

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 = 500
    ff.Parent.LockAspectRatio = True
    i = i + 1
Next nt
End Sub