Excel VBA-将图像(使用对话框)插入某个单元格

Question

因此，我已经在这里和Interwebs上的各个网站进行了广泛的搜索，但是我很难找到答案。我也不是最精通VBA的用户。

基本上，我需要：单击按钮时，"插入图像"对话框弹出，用户选择一个图像文件，并且应将图像插入单元格B2。理想情况下，我想对此图像进行尺寸，以便它不超过x，也不比y高。

这是我到目前为止的代码（这给了我"运行时错误424"，并指向TextBox1.Value Line）。任何建议/改进总是非常感谢！

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
    TextBox1.Value = .SelectedItems(1)
    Image1.PictureSizeMode = fmPictureSizeModeZoom
    Image1.Picture = LoadPicture(.SelectedItems(1))
Else
    MsgBox ("Cancelled.")
End If
End With
End Sub

谢谢！

-a

Answer 1

这是一种使用不同类型的插入来执行此操作的方法。示例还显示了如何定位和缩放图像。

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 = 50
            img.Top = 150
            'Set image sizes in points (72 point per inch)
            img.Width = 150
            img.Height = 150
        Else
            MsgBox ("Cancelled.")
        End If
    End With
End Sub