Excel VBA - 将图像（使用对话框）插入到某个单元格中 [英] Excel VBA - Insert image (using dialog box) into a certain cell

问题描述

所以我已经在这里和各种网站上进行了广泛的搜索，但是我很难找到一个答案。我也不是VBA最了解的用户。

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.

基本上，我需要的：
点击按钮，弹出插入图像对话框用户选择单个图像文件，并且图像应该插入到单元格B2中。理想情况下，我想为这个图像设置大小，使其不再长于X，而不是高于Y.

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.

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

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!

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

推荐答案

这是一种方法具有不同类型的插入。

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

这篇关于Excel VBA - 将图像（使用对话框）插入到某个单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！