MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Add Image Comment – VBA

with 9 comments

Quite some time ago, summer 2008, I wrote a post about how you could embed an image in a cell comment. It was for the then current version of the product – Excel 2007. Here’s a User-Defined Function (UDF) in VBA to perform that trick that works in Excel 2010/2011. A comment on that older post fed my disappointment that Excel 2011 doesn’t even support the navigation but it does perform it with VBA. This includes the UDF to add an image and an ordinary Excel Macro to remove the image when you want to preserve the text.

Let’s assume you have a list of image files in a directory and that you’ve entered their fully qualified or absolute path values column B of your worksheet. Now you want to load them as comment images in Column A and insert a value in each column A cell that describes the comment image.

Unfortunately, as I’ve explained before you can’t assign the image as a property of the cell (or more precisely, I’ve never found a way to do it). If this is wrong, please post the magic here for all to enjoy without a fee or registration. ;-)

The following UDF takes a string value to describe the image and a cell reference that holds a string value that holds an absolute file name, which is a logical drive letter (C:\), a file path, and file name.

Function InsertCommentImage(title As String, cellAddress As Range)
  Dim commentBox As comment
  ' Clear any comments before attempting to add them.
  Application.ActiveCell.ClearComments
  ' Define the comment as a local variable and assign the file name from the
  ' cellAddress input parameter to the comment of a cell.
  Set commentBox = Application.ActiveCell.AddComment
  With commentBox
    .Text Text:=""
    With .Shape
      .Fill.UserPicture (cellAddress.Value)
      .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
      .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
    End With
    ' Set the visible to True when you always want the image displayed, and
    ' to False when you want it displayed only when you click on the cell.
    .Visible = False
  End With
  InsertCommentImage = title
End Function

A fully qualified address for the cellAddress parameter on a PC would look like this in let’s say cell B1:

C:\Data\Images\WizardChess.png

While it would be like this for the cellAddress parameter on a Mac OS X installation in cell B1:

Macintosh HD:Users:mclaughlinm:Desktop:WizardChess.png

You would call this from a cell like this when the text is provided as a string and fully qualified file name is in cell B1 of a worksheet named ImageSource:

=InsertCommentImage("Wizard Chess",B1)

Alternatively, you rewrite InsertCommentImage() as follows, which takes a string for the cell value and a string for the absolute file name:

Function InsertCommentImage(title As String, absoluteFileName As String)
  Dim commentBox As Comment
  ' Clear any comments before attempting to add them.
  Application.ActiveCell.ClearComments
  ' Define the comment as a local variable and assign the file name from the
  ' cellAddress input parameter to the comment of a cell.
  Set commentBox = Application.ActiveCell.AddComment
  With commentBox
    .Text Text:=""
    With .Shape
      .Fill.UserPicture (absoluteFileName)
      .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
      .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
    End With
    ' Set the visible to True when you always want the image displayed, and
    ' to False when you want it displayed only when you click on the cell.
    .Visible = False
  End With
  InsertCommentImage = title
End Function

With the change of the second parameter, you would call the InsertCommentImage() function with two strings, like:

=InsertCommentImage("Wizard Chess","C:\Data\Images\WizardChess.png")

Here’s how it would look if you’d put the formula in cell A1:

Wizard's Chess Image

This is a simple Excel macro for which you could create a button. You run it when you want to delete only the image comment from a cell. The macro works by highlighting the cell and running it. Naturally, you could wrap it in another Excel macro to navigate through the list and clean up a bunch of comment images at one time.

Sub RemoveComment()
  Application.ActiveCell.ClearComments
End Sub

If you want to allow the macro to detach all comments for a range, you would rewrite it this way:

Sub RemoveComment()
  Application.Selection.ClearComments
End Sub

As always, I hope this helps and furthers sharing information.

Written by maclochlainn

March 8th, 2011 at 12:44 am

9 Responses to 'Add Image Comment – VBA'

Subscribe to comments with RSS or TrackBack to 'Add Image Comment – VBA'.

  1. This example is interesting too.

    Doug

    8 Mar 11 at 10:10 am

  2. Thanks for this. I ran it and auto-filled, but the auto-fill didn’t work properly. The cell text auto-filled but I have to click into the formula bar for each cell and press enter for the comment to be created. Any thoughts?

    Andrew

    14 Sep 12 at 10:38 am

  3. How would I set the size of the photo using absolute measurements instead of a scale? I tried using just .height and the compiler gave me an invalid use of property error.

    Chris

    22 Sep 12 at 1:05 pm

  4. Yes, I haven’t found a way to discover natural dimensions. If you find one, let me know.

    You just have to use another tool and know the dimension before loading the image.

    maclochlainn

    22 Sep 12 at 1:51 pm

  5. For the first example to auto size the pictures to their natural size.

    Add anywhere:

      Set MyImg = LoadPicture(cellAddress)
      Dim H As Long
      Dim W As Long
      H = MyImg.Height
      W = MyImg.Width

    and change:

    .Fill.UserPicture (cellAddress.Value)
          .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
          .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft

    To:

    .Fill.UserPicture (cellAddress.Value)
          .Height = H / 27
          .Width = W / 27

    change the 27 to any ratio you would like to size up or down from actual

    Ivan R

    30 Oct 12 at 12:54 pm

  6. Can you do this with Image URL’s instead of local file paths too?

    Peter

    16 Apr 13 at 7:03 am

  7. If I have the image in a windows server shared drive (path in windows terms \\bshared\images\test1.jpg) how will I represent that in the macro to be used for excel mac 2011? Mine works well for windows, but not on Mac and addpicture does not return anything. I suspect, I am doing the URL wrong. Please let me know. Thanks

    Anitha

    18 Sep 13 at 10:46 am

  8. Anitha,

    I think you’re looking for this? The first one works if you store only the file name in a cell and use VBA. The second one is the literal way to reference a Mac OS X file.

    .Fill.UserPicture ("Macintosh HD:Users:mclaughlinm:Desktop:" + Cstr(ActiveCell.Value) + ".png")

    This is the literal value for a CowJumpingOverMoon.png in the Desktop folder for a user named mclaughlinm:

    Macintosh HD:Users:mclaughlinm:Desktop:CowJumpingOverMoon.png

    Hope this helps,
    Michael

    maclochlainn

    18 Sep 13 at 10:57 pm

  9. Thanks for the reply Michael.
    For some reason, that didn’t work with mine as well.
    I tried this
    ImageFolder = “bshared:images”
    ImagePath = ImageFolder & objcell.value & ImageType

    With ActiveSheet
    Set objImg = ActiveSheet.Shapes.Fill.UserPicture(ImagePath,False,True,objCell.Left,objCell.Top,60,60)
    End With

    I was thinking, if I make the location an URL, with IIS ( http://test/image/abcd.jpg). will it work? Should I be using Shapes.Addpicture ( URL,…). This also works for me in windows , but not in mac. I can access the image with this URL in the mac browser though

    Thanks Again
    Anitha

    Anitha

    23 Sep 13 at 10:46 am

Leave a Reply