EXCEL: Inserting multiple pictures in a single row, one at a time, with use of a Macro-enabled button

[robertsonja38]

Hello all,

I am trying to use a macro-enabled button to insert multiple pictures in the same Row. I use a spread sheet for work to capture facility issues, and I want to be able to add multiple pictures of these issues next to the cells which have a text explanation of the issue. Below is what I currently use, this just places one picture in Column G of the Row (Row 5 in this example)


Sub ChangeImage()
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.ButtonName = "Submit"
.Title = "Select an image file"
.Filters.Clear
.Filters.Add "JPG", "*.JPG"
.Filters.Add "JPEG File Interchange Format", "*.JPEG"
.Filters.Add "Graphics Interchange Format", "*.GIF"
.Filters.Add "Portable Network Graphics", "*.PNG"
.Filters.Add "Tag Image File Format", "*.TIFF"
.Filters.Add "All Pictures", "*.*"

If .Show = -1 Then
Dim img As Object
Set img = ActiveSheet.Pictures.Insert(.SelectedItems(1))

'Scale image size
'img.ShapeRange.ScaleWidth 0.75, msoFalse, msoScaleFromTopLeft
'img.ShapeRange.ScaleHeight 0.75, msoFalse, msoScaleFromTopLeft

'Position Image
img.Left = Range("G5").Left
img.Top = Range("G5").Top


'Set image sizes in points (72 point per inch)
img.Width = 350
img.Height = 350
Else
MsgBox ("Cancelled.")
End If
End With
End Sub


I want the macro to be able to realize that pictures have already been added and to place the next picture starting at the RIGHT end of the last uploaded picture. In the above example, I would want the next button-click to upload the selected picture into H5.

I am using Microsoft Office Standard 2013 on Windows 7.

Thank you in advance,

 

[John_w]

Welcome to MrExcel forums.

What determines that the picture is inserted into row 5? Ignoring this question for now, the following code will insert the selected picture in the next cell in row 5.

Replace your "Position image" lines with:

            'Position Image
            Dim pictureColumn As Long
            pictureColumn = Next_Picture_Column(5)
            img.Left = Cells(5, pictureColumn).Left
            img.Top = Cells(5, pictureColumn).Top

and add the following function below your code:

Private Function Next_Picture_Column(pictureRow As Long) As Long

    'For the specified row on the active sheet, returns the column number where the next picture
    'should be inserted
    
    Dim pic As Picture
    
    Next_Picture_Column = 0
    
    For Each pic In ActiveSheet.Pictures
        If pic.TopLeftCell.Row = pictureRow And pic.TopLeftCell.Column > Next_Picture_Column Then
            Next_Picture_Column = pic.TopLeftCell.Column
        End If
    Next
    
    Next_Picture_Column = Next_Picture_Column + 1

End Function

PS - please use CODE tags by selecting the VBA code and clicking the "#" icon in the message box.