VBSCRIPT 10
VBA Code To Insert An Image Into A Cell Comment By mike on 5th November 2021 03:24:07 AM
  1. 'VBA Code #1:
  2. 'This solution will fit the image into the desired size of your comment box by using a Scale command (either ScaleWidth or ScaleHeight).
  3.  
  4. Sub InsertPictureComment()
  5. 'PURPOSE: Insert an Image into the ActiveCell's Comment
  6. 'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
  7.  
  8. Dim PicturePath As String
  9. Dim CommentBox As Comment
  10.  
  11. '[OPTION 1] Explicitly Call Out The Image File Path
  12.  'PicturePath = "C:\Users\chris\Desktop\Image1.png"
  13.  
  14. '[OPTION 2] Pick A File to Add via Dialog (PNG or JPG)
  15.   With Application.FileDialog(msoFileDialogFilePicker)
  16.     .AllowMultiSelect = True
  17.     .Title = "Select Comment Image"
  18.     .ButtonName = "Insert Image"
  19.     .Filters.Clear
  20.     .Filters.Add "Images", "*.png; *.jpg"
  21.     .Show
  22.    
  23.     'Store Selected File Path
  24.      On Error GoTo UserCancelled
  25.         PicturePath = .SelectedItems(1)
  26.       On Error GoTo 0
  27.     End With
  28.  
  29. 'Clear Any Existing Comment
  30.  Application.ActiveCell.ClearComments
  31.  
  32. 'Create a New Cell Comment
  33. Set CommentBox = Application.ActiveCell.AddComment
  34.  
  35. 'Remove Any Default Comment Text
  36.  CommentBox.Text Text:=""
  37.  
  38. 'Insert The Image and Resize
  39.  CommentBox.Shape.Fill.UserPicture (PicturePath)
  40.   CommentBox.Shape.ScaleHeight 6, msoFalse, msoScaleFromTopLeft
  41.   CommentBox.Shape.ScaleWidth 4.8, msoFalse, msoScaleFromTopLeft
  42.  
  43. 'Ensure Comment is Hidden (Swith to TRUE if you want visible)
  44.  CommentBox.Visible = False
  45.  
  46. Exit Sub
  47.  
  48. 'ERROR HANDLERS
  49. UserCancelled:
  50.  
  51. End Sub
  52.  
  53.  
  54.  
  55.  
  56.  
  57. 'VBA Code #2:
  58. '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.
  59.  
  60. Sub InsertPictureComment()
  61. 'PURPOSE: Insert an Image into the ActiveCell's Comment
  62. 'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
  63.  
  64. Dim PicturePath As String
  65. Dim CommentBox As Comment
  66. Dim ScaleValue As Integer
  67.  
  68.  
  69. 'Scale the comment image by a factor of...
  70.  ScaleValue = 4 '4x the size
  71.  
  72. '[OPTION 1] Explicitly Call Out The Image File Path
  73.  'PicturePath = "C:\Users\chris\Desktop\Image1.png"
  74.  
  75. '[OPTION 2] Pick A File to Add via Dialog (PNG or JPG)
  76.   With Application.FileDialog(msoFileDialogFilePicker)
  77.     .AllowMultiSelect = True
  78.     .Title = "Select Comment Image"
  79.     .ButtonName = "Insert Image"
  80.     .Filters.Clear
  81.     .Filters.Add "Images", "*.png; *.jpg"
  82.     .Show
  83.  
  84.     'Store Selected File Path
  85.      On Error GoTo UserCancelled
  86.         PicturePath = .SelectedItems(1)
  87.       On Error GoTo 0
  88.     End With
  89.  
  90. 'Clear Any Existing Comment
  91.  Application.ActiveCell.ClearComments
  92.  
  93. 'Create a New Cell Comment
  94. Set CommentBox = Application.ActiveCell.AddComment
  95.  
  96. 'Remove Any Default Comment Text
  97.  CommentBox.Text Text:=""
  98.  
  99. 'Insert The Image and Resize
  100.  CommentBox.Shape.Fill.UserPicture (PicturePath)
  101.   CommentBox.Shape.LockAspectRatio = True
  102.   CommentBox.Shape.Width = ScaleValue * CommentBox.Shape.Width
  103.  
  104. 'Ensure Comment is Hidden (Swith to TRUE if you want visible)
  105.  CommentBox.Visible = False
  106.  
  107. Exit Sub
  108.  
  109. 'ERROR HANDLERS
  110. UserCancelled:
  111.  
  112. End Sub

Hasta la pasta! is for source code and general debugging text.

Login or Register to edit, delete and keep track of your pastes and more.

Raw Paste

Login or Register to edit or fork this paste. It's free.