VBSCRIPT
422
VBA Code To Insert An Image Into A Cell Comment By mike on 5th November 2021 03:24:07 AM
- 'VBA Code #1:
- 'This solution will fit the image into the desired size of your comment box by using a Scale command (either ScaleWidth or ScaleHeight).
- 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
- 'VBA Code #2:
- 'The below VBA code with lock in your images aspect ratio and allow you to increase it’s size by a factor using the variable ScaleValue.
- 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
- Dim ScaleValue As Integer
- 'Scale the comment image by a factor of...
- ScaleValue = 4 '4x the size
- '[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.LockAspectRatio = True
- CommentBox.Shape.Width = ScaleValue * CommentBox.Shape.Width
- 'Ensure Comment is Hidden (Swith to TRUE if you want visible)
- CommentBox.Visible = False
- Exit Sub
- 'ERROR HANDLERS
- UserCancelled:
- End Sub
Recent Pastes