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

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/22355537/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-12 02:18:06  来源:igfitidea点击:

Excel VBA - Insert image (using dialog box) into a certain cell

excelvba

提问by AllysonL

So I've extensively searched through here and the various websites on the interwebs, but I'm having difficulty finding an answer. I'm also not the most VBA-savvy user.

因此,我在此处和互联网上的各种网站上进行了广泛搜索,但我很难找到答案。我也不是最精通 VBA 的用户。

Basically, what I need: On a button click, the "Insert Image" dialog box pops up, the user selects a single image file, and the image should be inserted into cell B2. Ideally I would like to size this image so that it isn't any longer than X, and no taller than Y.

基本上,我需要的是:单击按钮,弹出“插入图像”对话框,用户选择单个图像文件,并将图像插入单元格 B2。理想情况下,我想调整此图像的大小,使其不长于 X,也不高于 Y。

This is my code so far (which gives me a 'Run-time error 424' and points to the TextBox1.Value line). Any suggestions/improvements are always greatly appreciated!

到目前为止,这是我的代码(它给了我一个“运行时错误 424”并指向 TextBox1.Value 行)。任何建议/改进总是非常感谢!

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

Thanks!

谢谢!

-A

-一种

采纳答案by Portland Runner

Here is one way of doing this with a different type of insertion. Example also show how to position and scale the image.

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

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