Inserting Images into Excel Comments Using VBA

I use Excel for basic statistical analysis of manufacturing and field return data as part of daily routine. I also use Excel for department budgeting. During these tasks, I frequently need to add information to my spreadsheets about my information sources (see Wikipedia entry on data provenance). Since much of my information comes to me in the form of email or web pages, a quick and easy way to document this information is to take a screenshot of the information and insert the information into an Excel comment.

For years I have included images in Excel comments using the manual procedure described here. This approach works well but is slow. I finally decided that there has to be a better way and I wrote a Visual Basic for Applications (VBA) macro that handles all the ugly details. Here is a link to this spreadsheet. The spreadsheet does use a VBA macro. If you are concerned about security, just look in the macro and see what it does.

To see a sample image (Saturn), just mouse over the cell with the little red triangle in the upper right corner.

A little discussion on how to use the macro is in order:

  • Put an image into your clipboard (I use PicPick)
  • Select the cell where you want to put the image
  • Click the button labeled "Press"
  • Your selected cell will now have little red triangle in the upper right corner. You will see your image when you mouse over this cell.
  • I used a button for this example, but in my work I assign both a Quick Access toolbar icon and a keyboard shortcut to this macro.

Many thanks to the folks at these two web sites for posting code and clues that helped me put this macro together.

 
This entry was posted in Management, software. Bookmark the permalink.

6 Responses to Inserting Images into Excel Comments Using VBA

  1. Sam says:

    Im looking for this a long time now, It works but I keep getting an error afterwards..."The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute."

     
    • mathscinotes says:

      Hi Sam,

      Thanks for catching this. I will update the code this weekend. Again, appreciate the help.

      mark

       
  2. Thanks Mark, I was at the point of giving up.
    So what I really need is a macro that put or paste the image which is residing in the clipboard into the comment of the active cell, or a macro that cuts the pre-pasted image which is lying temporarily on a cell, into the comment of any desired cell

     
    • mathscinotes says:

      Could you tell me what version of Excel you have? It is working on all my versions. I am currently running Office 365 Version 1609 at work and home, however the routine ran with 2007 (32 bit) and 2010 (32 bit). What version are you running? Look under File/Account.

      mark

       
  3. Hi Mark
    You might be absolutely right on the compatibility. And that will be great news for me, as I need it for my Work computer, not my MacBook Air below. Sorry I should have told you upfront that I encountered this problem at home with my Mac, not at my work which has Office 10. I will let you know by this coming week when I tried it out at Work.

    My version of Excel for my MacBook Air (Mid 2012) ,
    macOS Sierra is .....

    Microsoft Excel for Mac
    version 15.32 (170309)
    Product ID: xxxxxxxxxxxxxxxx
    License: Office 365 Subscription
    2017 Microsoft

     
  4. Hi Mark,
    Sorry for the lapse, yes it works smooth and flawlessly on my job's pc. Thanks for your masterpiece. i wish it could work same way on my mac too. Hope you fix that someday.

     

Leave a Reply

Your email address will not be published. Required fields are marked *