(VBA) Insert Image into Comment & Reduce Image File Size

[JamesAs]

Hi,

hoping someone might be able to help with some VBA.

Using the code below to Insert and Image into a comment, works well however could do with being able to compress the Image File Size to ensure the workbook stays as light as possible.

Thanks in Advance!

VBA Code To Insert An Image Into A Cell Comment

This VBA macro allows you to automate insertings an image into a specific cell's comment box.

www.thespreadsheetguru.com

Sub InsertPictureComment()
'PURPOSE: Insert an Image into the ActiveCell's Comment
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim PicturePath As String
Dim CommentBox As Comment

'[OPTION 1] Explicitly Call Out The Image File Path
  'PicturePath = "C:\Users\chris\Desktop\Image1.png"

'[OPTION 2] Pick A File to Add via Dialog (PNG or JPG)
   With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True
    .Title = "Select Comment Image"
    .ButtonName = "Insert Image"
    .Filters.Clear
    .Filters.Add "Images", "*.png; *.jpg"
    .Show
   
    'Store Selected File Path
      On Error GoTo UserCancelled
        PicturePath = .SelectedItems(1)
      On Error GoTo 0
    End With

'Clear Any Existing Comment
  Application.ActiveCell.ClearComments

'Create a New Cell Comment
Set CommentBox = Application.ActiveCell.AddComment

'Remove Any Default Comment Text
  CommentBox.Text Text:=""

'Insert The Image and Resize
  CommentBox.Shape.Fill.UserPicture (PicturePath)
  CommentBox.Shape.ScaleHeight 6, msoFalse, msoScaleFromTopLeft
  CommentBox.Shape.ScaleWidth 4.8, msoFalse, msoScaleFromTopLeft

'Ensure Comment is Hidden (Swith to TRUE if you want visible)
  CommentBox.Visible = False

Exit Sub

'ERROR HANDLERS
UserCancelled:

End Sub