excel - 通过 VBA 用户表单一键将多个 JPG 插入不同的单元格

Question

我正在开发一个 unserform，它允许用户将图片作为附件插入到特定的单元格中。到目前为止，一个按钮负责一个特定单元格的一张图片。我想知道是否可以多次单击一个按钮，将多张图片插入多个单元格，根据已插入的图片数量更改下一张图片的位置。
这是我用于将一张图片插入工作表的单个特定单元格的按钮的代码:

 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

Answer 1

使用静态变量 PicCount跟踪已添加的图片数量。

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