JILBO
12-10-2020, 10:49 AM
Hello,
New to this Forum and hope to pick up some tricks along the way!!
I'm using to great effect the VBA within this post (https://www.thespreadsheetguru.com/the-code-vault/vba-insert-image-into-cell-comment) Details below
I've come unstuck as it can soon make the workbook too large and therefore need a some additional VBA that compresses the image before inserting it into the cell comment
Please Help
Thanks James
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
New to this Forum and hope to pick up some tricks along the way!!
I'm using to great effect the VBA within this post (https://www.thespreadsheetguru.com/the-code-vault/vba-insert-image-into-cell-comment) Details below
I've come unstuck as it can soon make the workbook too large and therefore need a some additional VBA that compresses the image before inserting it into the cell comment
Please Help
Thanks James
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