Insert multiple photos into a column of comment boxes

[Aidworker17]

Dear All,

I was wondering if anyone knows a way to insert multiple photos into a column of comment boxes. I have 13,000 and I would insert 300-500 at a time

If it's possible, I would also like them to appear screen-size when I run the curser over the top right corner

Cheers,
Jon

 

[GraH - Guido]

Hi Jon,

As far as I know, you cannot insert pictures into comments. You can choose a picture as Fill Effect in the format comment options. But that would only be one picture and not 500. Or you make a giant picture that is made out of these 500 first, and use that one as fill effect.
Honestly, I struggle to see a use case. What are you trying to build?

 

[p45cal]

This little macro will add pictures to cells in a column starting with the active cell before you run it. It will ask you for the picture(s) you want to insert:

Sub PicturesIntoComments()
Set StartCell = ActiveCell    'before running select the starting cell.
With Application.FileDialog(msoFileDialogFilePicker)
  .AllowMultiSelect = True
  .InitialFileName = CurDir
  .Filters.Clear
  .Filters.Add Description:="Images", Extensions:="*.jpg", Position:=1
  .Title = "Choose image(s)"
  If .Show = -1 Then
    Set strFileNames = .SelectedItems
    Set CurrentCell = StartCell
    For Each strFileName In strFileNames
      If CurrentCell.Comment Is Nothing Then CurrentCell.AddComment
      Set cmt = CurrentCell.Comment.Shape
      Set shpPic = ActiveSheet.Shapes.AddPicture(strFileName, False, True, 0, 0, -1, -1)
      With shpPic
        dblRatio = .Width / .Height    'aspect ratio
        .Delete
      End With
      With cmt
        .LockAspectRatio = msoFalse
        .Width = .Height * dblRatio    'Set ratio same as picture ratio
        .LockAspectRatio = msoTrue
        .Width = 400    'hard coded width
        .Fill.UserPicture (strFileName)
      End With
      Set CurrentCell = CurrentCell.Offset(1)
    Next strFileName
    Range(StartCell, CurrentCell.Offset(-1)).Select
    MsgBox "Pictures inserted into comments in the selected cells"
  End If
End With
End Sub

Currently each comment box is a fixed width, hard coded; I'll have a look at how they might be made full screen(ish) but realise that this might depend on the user's screen size/resoluition and perhaps whether Excel is maximised or not. The size is determined by the macro at the time of insertion, but won't adjust if the file is opened on a different machine with different screen setttings/size.

 

[GraH - Guido]

Hi p45cal, nice little macro indeed to automate what I explained (tried to anyway). Funny to see it actually changes the "shape" in the macro. Never would have guessed that works with pictures.
Still it inserts a single picture in the cell comments, right?
If I understand the OP correctly, he wants multiple pictures in a single comment, though starting to doubt after a re-read.

 

[p45cal]

I read it as multiple checkboxes ('column of checkboxes'), 1 picture in each - hopefully we'll hear from the OP soon.

 

[Aidworker17]

Dear All,

Thank you so much for your responses.

I would like 1 photo for each comment box - ie in the Fill Effect in the format comment options.

The idea is that I will have an information line for each of the 9,500 beneficiaries (previous figure of 13,000 has been revised). Below my sign off is a sample, and below that is the code. I have also attached a screen shot for further clarity.

Each line has the following columns : District, Sub-county, Parish Name, Parish No., Surname, First name, Photo Taken (Yes / No), Photo File Name, Full Name

The first letter of the District, plus the first letter of the Sub-county, plus the Parish Name, plus the Parish no., are used to generate a unique file name, designed to match the name of the actual photo file of the beneficiary.

The names manually given to the actual photo files are based on Windows Explorer multi-rename - that can be done in one 'hit'.

If the beneficiary was not available to have their photo taken, a photo of an empty space was still taken, and the words "Not Captured" were added to the file name; and the word "No" was entered into the "Photo Taken" column.

The Full Name column is an actual hyperlink to the photo. Currently, this is the system I am using to look at the beneficiary's photo from the workbook. However, this requires the photos and the workbook to be stored in specific locations - which has negative repercussions.

That's why I would like the photo in the workbook. Also, I need it screen size so that it can be compared to the person standing in front of the computer with the workbook open.

If you have any other ideas, that's great. Otherwise, can you please explain how to make the macro happen. To help you help me, here's some info about my knowledge / experience :

I am reasonably experienced with Excel, and I am aware of Visual Basic - though I know next to nothing about using it. Even though I don't know the actual language, I can copy and past code if directed to do so. I graduated as a programmer in 1985 (Pascal was all the rage then).

Kind regards,
Jon

Sample plus code :

District Sub-county Parish Name Parish No. Surname First name Photo Taken (Yes / No) Photo File Name Full Name
USA California Los Angeles 1 Smith John Yes UCLos Angeles (1) Smith John
USA California Los Angeles 2 Jones Helen No UCLos Angeles (2) Not Captured Jones Helen


The code for the Photo Name column is : =IF(AND(Q2="Yes", R2<>""), LEFT(A2,1)&LEFT(B2,1) & C2 &" ("& I2 &")",LEFT(A2,1)&LEFT(B2,1) & C2 &" ("& I2 &")" & " Not Captured")

The code for the Full Name column is : =HYPERLINK('Drop Downs '!B$15&S2&".jpg",J2&" "&K2)

 

[p45cal]

The macro below needs to be run after you have selected cells in Column S containing the file names. Because you said you'd be doing this in batches, this will allow you to control which rows to process.
The appropriate sheet needs to be the active sheet.
The size of the images is dependent on the size of the Excel application at the time the macro is run. It is designed to make the pictures fit within the application window; if you have a tall image, it will fit within the application window, if you have a wide image, likewise. The aspect ratio of the image is maintained.
There are 2 lines in the code:
AppWidth = .UsableWidth * 0.9
AppHeight = .UsableHeight * 0.8
You can adjust the 0.9 and 0.8 as you see fit.

Comments will be added to column U. If there are existing comments, their text will not be changed (although you might find it difficult to read depending what's in the image). Later you can delete column T and column U will become the new column T. If you don't like the size of the comments it won't matter if you run the macro again (but the image files need to be on that computer).

If there's 'Not Captured' somewhere in the cell in Column S no changes will be made to any comments, no comments added.

this requires the photos and the workbook to be stored in specific locations - which has negative repercussions.

There is a major drawback to storing the images within the Excel file; size. If you're adding 9.5k images the ensuing file has the potential to be massive. How big is each image?

What to do with the code? Paste it into a standard code-module. See https://chandoo.org/forum/threads/help-where-do-i-put-the-code.12106/

Sub blah()
With ActiveSheet.Application
  AppWidth = .UsableWidth * 0.9
  AppHeight = .UsableHeight * 0.8
End With
AppRatio = AppWidth / AppHeight
For Each cll In Selection.Cells
  If InStr(cll.Value, "Not Captured") = 0 Then
    strFileName = Sheets("Drop downs ").Range("B15") & cll.Value & ".jpg"
    With ActiveSheet.Cells(cll.Row, "U")
      If .Comment Is Nothing Then .AddComment
      Set cmt = .Comment.Shape
      Set shpPic = ActiveSheet.Shapes.AddPicture(strFileName, False, True, 0, 0, -1, -1)
      With shpPic
        dblRatio = .Width / .Height    'aspect ratio
        .Delete
      End With
      With cmt
        .LockAspectRatio = msoFalse
        .Width = .Height * dblRatio    'Set ratio same as picture ratio
        .LockAspectRatio = msoTrue
        If AppRatio < dblRatio Then .Width = AppWidth Else .Height = AppHeight
        .Fill.UserPicture (strFileName)
      End With
    End With
  End If
Next cll
End Sub

It's much better to supply an Excel file than a picture of one.

 

[Aidworker17]

To p45cal,

You have certainly given me things to think about - especially the size of the file.

For the moment I will stick with the hyperlinks, and think about how your ideas might be workable in practice.

To you and the others, thank you very much again for taking the time to help.

Kind regards,
Jonathan