Add pictures to a comment! (Excel 2000 >)

Topic

New Reply

That’s a very original use of comments!

Reply | Quote

Replies

Hi all,
Ever had a list in excel, ie Employees, or Products list, and you want to display a photo of the employee or product next to the record without taking too much space or setting up links to other programs.
Ever considered using hidden comments!!!
Well it really works great for lists, just move over the comment cell to see the pic!
Here is how to do it!
1. Select a cell that contains a Comment, right-click, and from the shortcut menu, select Show Comment.
2. Select the edge of the Comment so that the Comment is surrounded by dots, not by slashes.
3. Right-click, and from the shortcut menu, select Format Comment, Colours and Lines, Fill.
4. Open the Colour box. Choose Fill Effects. Click the Picture tab, and click Select Picture.
5. Select a picture and click OK. Resize the Comment as appropriate.

If you find this tip useful, let me know!!

Reply | Quote

An EXCEL-lent tip

Steve

Reply | Quote

Is it possible do do the above by means of a vba macro?? would save a lot of time – I just want to select a cell – run the macro and be able to choose a picture file from a standard popup file selection folder.
Thanx
smbs

Reply | Quote

Here’s a simple version. You can add error handling, more graphics file types, etc.

Sub AddPictures()
Dim i As Long
Dim cmt As Comment
Dim oCell As Range
Dim varFile As Variant
For i = ActiveSheet.Comments.Count To 1 Step -1
Set cmt = ActiveSheet.Comments(i)
If Not Intersect(cmt.Parent, Selection) Is Nothing Then
cmt.Delete
End If
Next i
For Each oCell In Selection.Cells
varFile = Application.GetOpenFilename(“Images,*.bmp;*.gif;*.jpg;*.png”)
If varFile = False Then
‘ skip this cell
Else
With oCell.AddComment
.Visible = False
.Shape.Fill.UserPicture PictureFile:=varFile
End With
End If
Next oCell
Set cmt = Nothing
Set oCell = Nothing
End Sub

Reply | Quote

Many thanx
Smbs

Reply | Quote

for this addition Hans! As Steve said before – EXCEL-lent!

Reply | Quote

Hi, just came across this and its a great idea and use of Excel I have to say!

Question : If I save as a webpage how come the comments are very narrow and not displaying the images? These is a folder with all the images saved in created, but no joy. Any ideas?

Cheers

Reply | Quote

Although the source code of the HTML file refers to the image, it is not displayed. I don’t know much about HTML and Javascript, so I cannot tell what is wrong, but perhaps someone who is familiar with web page design can help.

Reply | Quote

An EXCEL-lent tip

Steve

Reply | Quote

Yes I’ve done this before too, it’s quite cool. You can also change the shape of the comment to one of the AutoShapes figures as well. Comments don’t have to be dull white boxes

Deb

Reply | Quote

Hi Jujuraf…
How do you change the shape of the comment to one of the AutoShapes figures and still retain the popup comment and red indicator. Is this something new?
I created an autoshape and added a picture to it, but it is not a comment that pops up when the mouse moves over the cell. Can you convert a autoshape into a comment or can you change the shape of a comment to an autoshape???

Please clarify your statement above!

Reply | Quote

Use VBA:

Range(“A1”).Comment.Shape.AutoShapeType = msoShapeWave

Reply | Quote

Well I never. It is interesting that code exists to do this and it cannot be done in the GUI !

Makes you wonder what other secrets exists by using code to modify excel.

Tx

Reply | Quote

Excel has a “rich” VBA object model, unlike for example Outlook. There is no single recipe for finding out what can be done, you’ll have to learn by experimenting. In this example, I looked up Comment in the Object Browser (press F2 to activate it), noticed that it has a Shape property, etc. You can also start to write code, using explicit declarations:

Dim cmt As Comment

If you then type cmt followed by a period. IntelliSense will pop up a list of properties and methods. Shape is among them. If you select that, then type a period again, IntelliSense will pop up another list, AutoShapeType among them.

Reply | Quote

You can do it in the GUI:
1. Make sure the Drawing toolbar is visible.
2.Show the comment and select it.
3. On the drawing toolbar, choose Draw-Change autoshape and select your preference.
Your comment will now have the selected shape.
HTH.

Reply | Quote

Aha – learned something new! Thanks!

Reply | Quote

Tx Rory…As Hans said…I learned something new!

Thx Deb and Hans for the input too!
Cheers

Reply | Quote

Awesome Tip, and just what I needed. I have a comparison spreadsheet, that has different products. We have our company’s part number on it as well as the manufactures #. Now, by inserting these pictures there wont be any confusion.

Reply | Quote

Try this: http://www.contextures.com/xlcomments02.html%5B/url%5D

I’ve used it before to change the shape of a comment from its usual box look. You already know how to add a picture to a comment and it’s easy enough to change it background color to a pattern too (one of the built-in ones or using any graphic file you may have).

The thing that’s annoying though is even if I use the “set autoshape defaults” after changing anything about a comment, any future comments I add still revert back to the white box. This works for non-comment shapes, however.

Deb

Reply | Quote

Hi Deb:

To get round that annoyance, you can Copy the cell with the required comment-shape, then Paste Special / Comments to another cell. You would then need to edit the contents of the comment, but at least the shape is what you want!

Tony.

Reply | Quote

Hi Rudi

This is a great tip, I can find several uses for this. and to be able to change the shape makes it even more interesting.

Thanks for starting a great thread.

Braddy.

Reply | Quote

Hi John and Braddy
Thaxn for the feedback. Its nice to know that tips turn out to be useful and can assist in making tasks easier.

Reply | Quote

This is useful, but what I am wondering is whether or not you can insert a picture into a particular cell on a spreadsheet in order that it could be used in a lookup situation on another sheet. From what I can see inserted pictures are not tied to a paraticular cell.

Basically what I want is a Name category and a picture of employees on one sheet, and then use a vlookup on another sheet which will look up the name and picture into a phone directory.

Thanks.

Reply | Quote

Does the example in post 291,936 get you started? If it is just 1 changing lookup, you would not have to name all the pics, you could use 1 name with a dynamic range using OFFSET to get the matching value.

Another option is to save the pics to a drive and then use the hyperlink function to have them open up the approriat picture via a click. The lookup would only pull the picture name and location.

Steve

Reply | Quote

Tx for the reply to Kiazd, Steve. That question made me think and until I saw your answer, could not come to a solution. I hope this will be an answer for KaiazD.

Reply | Quote

Thanks, Steve. HAven’t had much a chance to play around with your tip as of yet.

Reply | Quote

I would like to go a step further–I have noticed that if I change the row height and select “view comments” the comments do not move only their arrows point to the correct cell row —I want make the cell height the same as the comment picture so as to prevent pictures “stacking up” one on top of the other.Recording a macro manually and manually positioning the comment uses ” Selection.ShapeRange.IncrementLeft xx Selection.ShapeRange.IncrementTop yy#”. I am not sure how to use this in code in order to position the comment location relative to cell–can this be done?? Maybe it is also possible to define the comment width and height thru vba??
Many thanx
Smbs

Reply | Quote

(Edited by sdckapr on 29-Jan-06 13:29. Modified code and added PS)

This will go thru all the comments on a sheet. It will:
Change the width to 200
Set the top of the comment to the top of the cell
Set the height of the row to the ht of the comment.

Steve

Option Explicit
Sub ChangeComments()
  Dim cmt As Comment
  Dim rng As Range
  For Each cmt In ActiveSheet.Comments
    Set rng = cmt.Parent
    With cmt.Shape
      .Width = 200
      .Top = rng.Top
      If .Height > rng.RowHeight Then
        rng.RowHeight = .Height
      End If
    End With
  Next
End Sub

PS I added an IF so that the row will only be set to the comment ht if the comment ht is larger than the current ht. This will prevent later comments in a row from shrinking the row ht narrower than the max needed.

Reply | Quote

As always Steve — Brilliant
Many thanx
Smbs

Reply | Quote

Thanks. Note I added a “tweak” to it (and modified the code) in case you have multiple comments in a row…

Steve

Reply | Quote

Steve
It works great but I still may have to change the actual location of the picture relative to its parent cell — can it be done??
Once again many thanks
Smbs

Reply | Quote

The code demos that. It sets the top of the comment to the top of the parent cell. You can also change the Left property if desired.

What are you trying to set exactly and to what?

Steve

Reply | Quote

Hi Steve
The leftproperty might be what I need . If I change the column width after adding a comment the comment arrows can be seen I would like to hide the comment arrows so when viewing the sheet the picture name appears to be in column 1 and the comment picture itself appears to be in column 2. Your solution works fine initially but the moment I change column width picture locations don’t follow new column widths.
Could u give me an example of using the “Left property” pls
Many thanx for your patience
Smbs

Reply | Quote

Within the With cmt.Shape … End With block, you can add

.Left = rng.Left

to make the left edge of the comment coincide with the left edge of the cell it belongs to, or

.Left = rng.Offset(0, 1).Left

to make the left edge of the comment coincide with the left edge of the cell to the right of the cell it belongs to.

Reply | Quote

Hi Hans,

This doesnt work for me – are you sure the Left property of the Shape for a comment can be set? Am I doing something wrong?

Reply | Quote

What doesn’t work?

Reply | Quote

I am using the following code:

Sub AddPictures()
On Error Resume Next
    Dim cmt As Comment
    Dim oCell As Range
    Dim varFile As Variant
    For Each oCell In Range("Parts")
    oCell.Comment.Delete
    Next oCell
    For Each oCell In Range("pics")
        varFile = "W:Images" & oCell.Value & ".jpg"
            With oCell.Offset(0, -4).AddComment
                .Visible = False
                .Shape.Left = 50 ' or: oCell.Offset(0, -4).Left
                .Shape.Fill.UserPicture PictureFile:=varFile
                .Shape.Height = 250
                .Shape.Width = 400

            End With
    Next oCell
    Set cmt = Nothing
    Set oCell = Nothing
End Sub

The line ‘.Shape.Left = 50’ has no effect on the position of the comment when I roll over the cell with the mouse – it always appears just to the right of the cell’s right side. I ahve tried numbers such as 10, 250, 500 etc but to no avail.

Reply | Quote

Hmmm. I’m certain I tested it before posting, and several newsgroup posts contain similar code, but now I can’t change the Left (or Top) property of the comment either.

Reply | Quote

I have found that if you choose ‘Show Comment’, then the TOP, and Left property’s reposition the comment, it just seems the auto preview of the comment is set as a default position.

)

Reply | Quote

Thanks!

Reply | Quote

In addition to Hans comment on the Left property you could set the width property to the width of the column to the right instead of a set value:
.Width = rng.Offset(0, 1).Width

Steve

Reply | Quote

Many thanx Hans, Steve
Regards
Smbs

Reply | Quote

Hi all,

Question regarding the comment…..I have have used the picture within a comment, what I have noticed is that with a shared document the picture didn’t show in the comment only on the computer that installed the comment.

Darryl.

Reply | Quote

Hi Darryl,

I notice the picture travels with the file if saved to a shared drive and opened up on another PC. I tried to simulate what you are suggesting here and could not get it right. Hopefully one of the Excel or Network guru’s can assist you in finding a solution.

Reply | Quote

I definitely find the origninal tip useful as well as the great additions from everyone else!

Thanks.
Pooja

Reply | Quote

Hi all,
Ever had a list in excel, ie Employees, or Products list, and you want to display a photo of the employee or product next to the record without taking too much space or setting up links to other programs.
Ever considered using hidden comments!!!
Well it really works great for lists, just move over the comment cell to see the pic!
Here is how to do it!
1. Select a cell that contains a Comment, right-click, and from the shortcut menu, select Show Comment.
2. Select the edge of the Comment so that the Comment is surrounded by dots, not by slashes.
3. Right-click, and from the shortcut menu, select Format Comment, Colours and Lines, Fill.
4. Open the Colour box. Choose Fill Effects. Click the Picture tab, and click Select Picture.
5. Select a picture and click OK. Resize the Comment as appropriate.

If you find this tip useful, let me know!!

Reply | Quote