Inserting multiple JPGs into different cells with one button via VBA userforms

user_15191265 0
Viewed 171

I am working on an unserform, which allows the user to insert pictures into specific cells as attachments. So far, one button is responsible for one picture for one specific cell. I was wondering if it was possible to click one button multiple times, inserting multiple pictures into multiple cells, changing the position of the next picture depending on the number of pictures already inserted.

This is the code I am using for one button to insert one picture into a single specific cell of a worksheet:

 Private Sub CommandButtonUpload_Click()

Dim PicLoad As Variant
PicLoad = Application.GetSaveAsFilename

Dim PicPath As String, Pic As Picture, ImageCell As Range

    PicPath = PicLoad
    Set ImageCell = Worksheets("Example").Range("a62")

    Set Pic = Worksheets("Example").Pictures.Insert(PicPath)
    With Pic
        .ShapeRange.LockAspectRatio = msoTrue
        .Left = ImageCell.Left
        .Top = ImageCell.Top

    End With
End Sub
userform vba excel
1 Answers

Use a static variable PicCount to keep track of the amount of pictures already added.

Private Sub CommandButtonUpload_Click()
    Dim PicLoad As Variant
    PicLoad = Application.GetSaveAsFilename
    
    If PicLoad = False Then Exit Sub 'user pressed cancel so exit

    Dim PicPath As String
    PicPath = PicLoad

    Static PicCount As Long

    Dim ImageCell As Range
    Set ImageCell = Worksheets("Example").Range("A" & cStr(62 + PicCount * 30))
    PicCount = PicCount + 1

    Dim Pic As Picture
    Set Pic = Worksheets("Example").Pictures.Insert(PicPath)
    With Pic
        .ShapeRange.LockAspectRatio = msoTrue
        .Left = ImageCell.Left
        .Top = ImageCell.Top
    End With
End Sub
user_32196130
Related