Insert picture in comment box (through fill effects → picture → insert picture)

[albertan]

I was able to insert picture in the past in my comments through insert comment → fill effects → picture → insert picture. But not anymore: the system is spooling and it tells me that I can do it through "work offline". I click offline and nothing happens. Anyone experienced this?

I have excel 2016 for Office 365 (16.0.11425.20200) 64 bit

 

[sijpie]

I cannot access fill effects while in a comment box, I can only edit the font. So can't replicate

 

[albertan]

I cannot access fill effects while in a comment box, I can only edit the font. So can't replicate

You need to click Edit Comment, then click on the border of the comment and then right click and then choose Format Comment. It is there, trust me

 

[sijpie]

Ok I will need to test that. But it’ll have to wait till Monday. For me the weekend has started

 

[sijpie]

Albertan, you were right.

Through code I can even replicate the error that you are getting!

In the following code there are three subs. The first two work fine on my Excel, the last one gives me the screen that you discribe.

I suggest you use version 2.

If you use this often, you can add the macro to your personal workbook (which Excel opens on starting) and add the macro to your shortcut bar.

Option Explicit


Sub InsertPictureComment1()
'Fill comment with fixed file
    Dim sFileName As String
    
    sFileName = "\\onedrive.ez.cloud-wp.nl@SSL\DavWWWRoot\personal\SijpJ\documents\Pictures\eon.jpg"    ' "C:\Users\xxx\OneDrive\Pictures\bear.jpg"
    With Range("E7").Comment.Shape.Fill
        .Visible = True
        .UserPicture sFileName
    End With
End Sub


'or


Sub InsertPictureComment2()
'Fill comment with image to be picked
    Dim vDlg As Variant
    Dim x As Dialog
    Dim sFileName As String
    
    Set vDlg = Application.FileDialog(FileDialogType:=1)
    vDlg.Show
    sFileName = vDlg.SelectedItems(1)
    With Range("E7").Comment.Shape.Fill
       .Visible = True
       .UserPicture sFileName
    End With
End Sub




'or


Sub InsertPictureComment3()
' Gives a waiting screen...
    Dim oDialog As Dialog
    Dim strName As String
     
    Set oDialog = Application.Dialogs(xlDialogInsertPicture)
    With oDialog
        .Show
        
         'Insert Shape Picture if the Name property (Filepath) <> ""
        If .Name <> "" Then
            With Range("E7").Comment.Shape.Fill
                .Visible = True
                .UserPicture oDialog.Name
            End With
        End If
    End With

 

[albertan]

Thanks for coming back to me on this. Unfortunately the code for property Comment.Shape.Fill is not working for me even though I specify my own range within my worksheet. Have you tried to test this code on your worksheet? none of the codes worked. thanks

 

[sijpie]

Yes the first two worked fine in my version of Excel which I think is also office 365. On a Windows PC.

 

[albertan]

I modified the code and I want to pick the cell automatically where I want to put the comment. So here's the code I modified and it works

Sub Img_in_CommentboxAdd()


On Error Resume Next


Dim ThisRng As Range
Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8)




With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.InitialFileName = CurDir
.Filters.Clear
.Filters.Add Description:="Images", Extensions:="*.jpg", Position:=1
.Title = "Choose image"


If .Show = -1 Then TheFile = .SelectedItems(1) Else TheFile = 0
End With


If TheFile = 0 Then
MsgBox ("No image selected")
Exit Sub




End If
ThisRng.AddComment
ThisRng.Comment.Visible = True
ThisRng.Comment.Shape.Fill.UserPicture TheFile


End Sub