excel insert comment with photo macro

M

marshalxxl

New Member
Joined
Nov 17, 2018
Messages
12
Hello i have to make a long list where i have to fill a lot of text cells whit one particullary photo for each cell commentary like in this video:
https://www.youtube.com/watch?v=Er-mjd4WOLU
All i need is a macro for example when i press Ctrl+h to make the macro to insert comment delete the first row of text in the comment then format the comment ->select fill efects->picture and then i can select the picture (minute 2:28)
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Re: excel insert comment whit photo macro

First thing is a code to insert a picture into a comment. Then we can work on the other part of deciding what picture to insert.

Change the picture file to the one you are working with.
Code: 
Sub InsertComment()
    With Range("D1")
        .ClearComments
        .AddComment
        With .Comment
            .Visible = False
            .Text Text:="" & Chr(10) & ""
            .Shape.Fill.UserPicture ("C:\Users\Dave\SkyDrive\Pictures\67873_463975655282_1273954_n.jpg")
            .Shape.ScaleHeight 6, msoFalse, msoScaleFormTopLeft
            .Shape.ScaleWidth 4.8, msoFalse, msoScaleFromTopLeft
        End With
    End With

End Sub
 
Upvote 0
Re: excel insert comment whit photo macro

So you want the text in the cell as a comment without the first row? First row you mean your name or the first row of the text in the cell?
If you mean your name, then this should work

Code: 
Sub PictureInComment()
[COLOR=#008000]'Window to select a picture[/COLOR]
With Application.FileDialog(msoFileDialogFilePicker)
         .AllowMultiSelect = False          'Only one file
         .InitialFileName = CurDir         'directory to open the window
         .Filters.Clear                   [COLOR=#008000] 'Cancel the filter[/COLOR]
         '.Filters.Add Description:="Images", Extensions:="*.jpg", Position:=1
         .Title = "Choose image"
         If .Show = -1 Then TheFile = .SelectedItems(1) Else TheFile = 0
    End With
[COLOR=#008000]'No file selected, exit macro[/COLOR]
If TheFile = 0 Then
MsgBox ("No image selected")
Exit Sub
End If

[COLOR=#008000]'Add a comment to selected cell[/COLOR]
ActiveCell.AddComment
ActiveCell.Comment.Text Text:=ActiveCell.Value
ActiveCell.Comment.Visible = True [COLOR=#008000]'Put to false if you don't want to see it[/COLOR]
ActiveCell.Comment.Shape.Fill.UserPicture TheFile
End Sub
 
Last edited:
Upvote 0
Re: excel insert comment whit photo macro

Kamolga said:
So you want the text in the cell as a comment without the first row? First row you mean your name or the first row of the text in the cell?
If you mean your name, then this should work

Code: 
Sub PictureInComment()
[COLOR=#008000]'Window to select a picture[/COLOR]
With Application.FileDialog(msoFileDialogFilePicker)
         .AllowMultiSelect = False          'Only one file
         .InitialFileName = CurDir         'directory to open the window
         .Filters.Clear                   [COLOR=#008000] 'Cancel the filter[/COLOR]
         '.Filters.Add Description:="Images", Extensions:="*.jpg", Position:=1
         .Title = "Choose image"
         If .Show = -1 Then TheFile = .SelectedItems(1) Else TheFile = 0
    End With
[COLOR=#008000]'No file selected, exit macro[/COLOR]
If TheFile = 0 Then
MsgBox ("No image selected")
Exit Sub
End If

[COLOR=#008000]'Add a comment to selected cell[/COLOR]
ActiveCell.AddComment
ActiveCell.Comment.Text Text:=ActiveCell.Value
ActiveCell.Comment.Visible = True [COLOR=#008000]'Put to false if you don't want to see it[/COLOR]
ActiveCell.Comment.Shape.Fill.UserPicture TheFile
End Sub
Click to expand...
Works like a charm by first row i mean that i don t want any kind of text on the comment i just want the photo itself.I tried to put ActiveCell.Comment.Visible = false but the text still apears could you help me whit this?
 
Upvote 0
Re: excel insert comment whit photo macro

i don t want any kind of text on the comment
Click to expand...
Code: 
Sub PictureInComment()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


ActiveCell.AddComment
ActiveCell.Comment.Text Text:=""
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Shape.Fill.UserPicture TheFile


End Sub

Visible=true means the comment is visible when you click another cell. If you put it to false, it his hidden and you need to move your mouse to red rectangle to see it
 
Upvote 0
Re: excel insert comment whit photo macro

Kamolga said:
Code: 
Sub PictureInComment()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


ActiveCell.AddComment
ActiveCell.Comment.Text Text:=""
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Shape.Fill.UserPicture TheFile


End Sub

Visible=true means the comment is visible when you click another cell. If you put it to false, it his hidden and you need to move your mouse to red rectangle to see it
Click to expand...
Thanks man works so good.One last favor can you write a preset resolution to the comment i mean to preset the lenght and width of the comment so i don t have to stretch it every time.
Thanks a lot!
 
Upvote 0
Re: excel insert comment whit photo macro

When there is text, I would normally autosize it around my text, but it seems that with my picture, the autosize is very small. I guess it depends on the picture?
Code: 
Sub PictureInCommentAuto()
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
ActiveCell.AddComment
ActiveCell.Comment.Text Text:=""
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Shape.Fill.UserPicture TheFile
[COLOR=#0000cd]ActiveCell.Comment.Shape.TextFrame.AutoSize = True[/COLOR]
End Sub
With my test (a screenshot), a scale of 2 is good but you can change it
Code: 
Sub PictureInCommentSize()
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
'Make comment
ActiveCell.AddComment
ActiveCell.Comment.Text Text:=""
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Shape.Fill.UserPicture TheFile
[COLOR=#0000cd]ActiveCell.Comment.Shape.ScaleWidth 2, msoFalse, msoScaleFromTopLeft[/COLOR]
[COLOR=#0000cd]ActiveCell.Comment.Shape.ScaleHeight 2, msoFalse, msoScaleFromTopLeft[/COLOR]
End Sub
 
Upvote 0
Re: excel insert comment whit photo macro

Yep all good works well i want to develop this thing for example if i have saved into 1 folder 3 photos A,B,C and the cell that i want to insert the commentphoto is named B,the macro to take from the specific folder the B photo you know what i mean ?
 
Upvote 0
Kamolga said:
Re: excel insert comment whit photo macro


Code: 
Sub PictureInComment()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


ActiveCell.AddComment
ActiveCell.Comment.Text Text:=""
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Shape.Fill.UserPicture TheFile


End Sub

Visible=true means the comment is visible when you click another cell. If you put it to false, it his hidden and you need to move your mouse to red rectangle to see it
Click to expand...
and how to use the picture name as cell value,

if i want to insert picture with file name frog.jpg, then the picture inserted to comment and the name was the value of the commented cell.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Macro for extracting selected text and comment from Word to Excel on Mac
Replies
4
Views
84
aminchicago
A
S
  • Question
VBA Code- finding #Spill and moving the formula to the next column
Replies
0
Views
396
stromnoexcel
S
M
  • Question
Excel Macro to insert picture into excel from SharePoint or website !
Replies
0
Views
278
MAlhash
M
R
  • Question
Embedding Interactable Excel worksheet (with macros that ONLY return all fields to starting values) using specifically Excel 2007 on Existing website
Replies
5
Views
289
Renarian
R
O
  • Question
Excel equivalent for a 1-2-3 range selection keyboard shortcut?
2
Replies
11
Views
455
RoryA
RoryA

Forum statistics

Threads
1,214,429
Messages
6,119,428
Members
448,896
Latest member
MadMarty

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top