Menu

Excel - A Macro to automatically insert image in a Comment Box

April 2018




Issue


How to create a macro to automatically insert a backgroung image for the comment box, under Excel?

Solution


Here's an example: 
Sub Img_in_Commentbox()  
With Application.FileDialog(msoFileDialogFilePicker)  
         .AllowMultiSelect = False          'Only one file   
         .InitialFileName = CurDir         'directory to open the window  
         .Filters.Clear                    'Cancel the filter  
         .Filters.Add Description:="Images", Extensions:="*.jpg", Position:=1  
         .Title = "Choose image"  
      
         If .Show = -1 Then TheFile = .SelectedItems(1) Else TheFile = 0  
    End With  
'No file selected  
If TheFile = 0 Then  
MsgBox ("No image selected")  
Exit Sub  
End If  
Range("A1").AddComment  
    Range("A1").Comment.Visible = True  
[A1].Comment.Shape.Fill.UserPicture TheFile  
End Sub


Add in A1 (re-adapt to your needs) a comment box with the image selected

Related

Published by jak58. Latest update on June 6, 2012 at 11:39 AM by jak58.
This document, titled "Excel - A Macro to automatically insert image in a Comment Box," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Outlook - Show week numbers
Importing an Excel table into Access