vba - 添加图像作为评论 VBA

Question

我发现此代码可将图像插入到 excel 2013 中，但图像比它们要进入的单元格大。我认为将图像加载为评论的最佳选择。

有人可以在下面修改此 VBA 以将其添加为评论吗？

Sub URLPictureInsert()
Dim cell, shp As Shape, target As Range
    Set rng = ActiveSheet.Range("R2:R5") ' range with URLs
    For Each cell In rng
       filenam = cell
       ActiveSheet.Pictures.Insert(filenam).Select

  Set shp = Selection.ShapeRange.Item(1)
   With shp
      .LockAspectRatio = msoTrue
      .Width = 50
      .Height = 50
      .Cut
   End With
   Cells(cell.Row, cell.Column + 5).PasteSpecial

Next

End Sub

Answer 1

我相信以下链接有你正在寻找的东西

http://en.kioskea.net/faq/8619-excel-a-macro-to-automatically-insert-image-in-a-comment-box

Sub Img_in_Commentbox()  
With Application.FileDialog(msoFileDialogFilePicker)  
         .AllowMultiSelect = False          'Only one file   
         .InitialFileName = CurDir         'directory to open the window  
         .Filters.Clear                    'Cancel the filter  
         .Filters.Add Description:="Images", Extensions:="*.jpg", Position:=1  
         .Title = "Choose image"  

         If .Show = -1 Then TheFile = .SelectedItems(1) Else TheFile = 0  
    End With  
'No file selected  
If TheFile = 0 Then  
MsgBox ("No image selected")  
Exit Sub  
End If  
Range("A1").AddComment  
    Range("A1").Comment.Visible = True  
[A1].Comment.Shape.Fill.UserPicture TheFile  
End Sub