vba 从工作表中获取图片到 Excel 用户表单

Get Picture from Worksheet into Excel Userform

提问by CaptainABC

I am looking to view an image from the worksheet in an Image control on a userform.

我希望在用户窗体上的图像控件中查看工作表中的图像。

This image will change based on the value on a combobox. I have inserted (Using: Insert -> Pictures) a couple of images into "Sheet1" of my workbook and named them "Picture1" & "Picture2".

此图像将根据组合框上的值进行更改。我已经插入（使用：插入 -> 图片）几个图像到我的工作簿的“Sheet1”中，并将它们命名为“Picture1”和“Picture2”。

I have created the below UserForm:

我创建了以下用户窗体：

Form http://im56.gulfup.com/msKyqi.png

表格 http://im56.gulfup.com/msKyqi.png

And this is the code that I am trying to use in order to load the images from the sheet, but unfortunately, this is not working at the moment.

这是我为了从工作表加载图像而尝试使用的代码，但不幸的是，目前这不起作用。

Private Sub ComboBox1_Change()

UserForm1.Image1.Picture = LoadPicture(Worksheets("Sheet1").Shapes(ComboBox1.Value))

End Sub

Private Sub UserForm_Initialize()

UserForm1.ComboBox1.Clear
UserForm1.ComboBox1.AddItem "Picture1"
UserForm1.ComboBox1.AddItem "Picture2"

UserForm1.ComboBox1.Value = "Picture1"

UserForm1.Image1.Picture = LoadPicture(Worksheets("Sheet1").Shapes(ComboBox1.Value))

End Sub

Every time I run this code I get the below error:

每次运行此代码时，都会出现以下错误：

Error http://im43.gulfup.com/YoWvTp.png

错误 http://im43.gulfup.com/YoWvTp.png

Please advise.

请指教。

回答by CaptainABC

I figured it out!

我想到了！

As I am using a UserForm there is a workaround to the issue.

当我使用 UserForm 时，有一个解决方法。

Instead of having the images in the worksheet to then try and load them in the form I tried having them in the UserForm in the first place, here is how.

不是在工作表中放置图像然后尝试以表单加载它们，我首先尝试将它们放置在用户表单中，这里是如何。

Create a frame on your userform: Frame http://im88.gulfup.com/Moy8I6.png

在您的用户表单上创建一个框架： 框架 http://im88.gulfup.com/Moy8I6.png

Set the visible property of the frame to "False": Visible http://im88.gulfup.com/sAIQqh.png

将框架的可见属性设置为“ False”： 可见 http://im88.gulfup.com/sAIQqh.png

Insert your images by adding a picture control and loading the images, you can add as many images as you need: Images http://im88.gulfup.com/oas0EQ.png

通过添加图片控件并加载图片来插入图片，您可以根据需要添加任意数量的图片： 图片 http://im88.gulfup.com/oas0EQ.png

Name the images: Name http://im88.gulfup.com/cIO317.png

命名图像： 命名 http://im88.gulfup.com/cIO317.png

Drag all the images one over the other into the frame, (you can then move the frame into a corner so it doesn't bother you:

将所有图像一个接一个地拖到框架中，（然后您可以将框架移到一个角落，这样它就不会打扰您：

Drag http://im88.gulfup.com/1fOSut.pngMove Away http://im88.gulfup.com/Q1fzKd.png

拖动 http://im88.gulfup.com/1fOSut.png移开 http://im88.gulfup.com/Q1fzKd.png

Next create a picture control, this is what you will use to display the picture based on a selection:

接下来创建一个图片控件，这是您将用于根据选择显示图片的内容：

Form View http://im88.gulfup.com/X1UVRB.png

表单视图 http://im88.gulfup.com/X1UVRB.png

In this example, I am going to use a combobox for the selection. Now insert the below code in to the form which is pretty straight forward:

在此示例中，我将使用组合框进行选择。现在将以下代码插入到非常简单的表单中：

    Private Sub ComboBox1_Change()

    ' Image1 is the name of the created picture control
    UserForm3.Controls.Item("Image1").Picture = UserForm3.Controls.Item(UserForm3.ComboBox1.Value).Picture 

    End Sub

    Private Sub UserForm_Initialize()

    UserForm3.ComboBox1.AddItem "Argentina"
    UserForm3.ComboBox1.AddItem "Brazil"
    UserForm3.ComboBox1.AddItem "Chile"

    End Sub

As you will see, the frame with the pictures is Hidden, and the image is changing inside the picture control based on a selection:

正如您将看到的，带有图片的框架是隐藏的，并且图片在图片控件内根据选择发生变化：

Result http://im88.gulfup.com/MSqyHF.png

结果 http://im88.gulfup.com/MSqyHF.png

I think it's the better way to go as opposed to exporting the images from the worksheet to a Temp folder and then loading them back into the picture controls.

我认为这是更好的方法，而不是将图像从工作表导出到 Temp 文件夹，然后将它们加载回图片控件。

回答by Verdigris97

The LoadImage() function expects a filename (which can be fully qualified with a drive letter and path). You are getting a type mismatch because it wants a string, and you are giving it an image object.

LoadImage() 函数需要一个文件名（可以用驱动器号和路径完全限定）。你得到一个类型不匹配，因为它需要一个字符串，你给它一个图像对象。

There is, as far as I know, no simple way to put an image that resides in the current application into an image control. The (hackish) workaround that I know about is to export the image to a file, and then import that same file using LoadImage().

据我所知，没有简单的方法可以将驻留在当前应用程序中的图像放入图像控件中。我所知道的（hackish）解决方法是将图像导出到文件，然后使用 LoadImage() 导入相同的文件。

This is the same path you have to go down if you want to embed a chart that updates dynamically into a userform. You export the chart as an image (e.g., a JPEG), and then use LoadImage() to pull the image back into the image control.

如果您想将动态更新的图表嵌入到用户表单中，这与您必须走的路径相同。您将图表导出为图像（例如，JPEG），然后使用 LoadImage() 将图像拉回到图像控件中。

回答by DMac42

I know this post is ancient, but I found my way here. I came up with a slightly different solution to this problem. I have about 30 pictures I need to load based on a combo-box selection. First, all combo-box options (for discussion "XX") are saved to a separate worksheet which is "Very hidden" from the user and loaded into the combobox on userform activation. On the userform a frame was added, and within the frame 30 image-boxes all overlapping perfectly were placed. Each image-box was carefully named "Img_XX" where XX is the simple two-letter identifier.

我知道这篇文章很古老，但我在这里找到了方法。我想出了一个稍微不同的解决方案来解决这个问题。我需要根据组合框选择加载大约 30 张图片。首先，所有组合框选项（用于讨论“XX”）都保存到一个单独的工作表中，该工作表对用户“非常隐藏”，并在用户表单激活时加载到组合框中。在用户表单上添加了一个框架，在框架内放置了 30 个完全重叠的图像框。每个图像框都被小心地命名为“Img_XX”，其中 XX 是简单的两个字母标识符。

With this setup it is now possible to iterate through each "Control" (the image boxes) in the Frame and hide them all, except the one with a name that matches the combo-box value. The code in the userform module, within the Combobox_Change() function, looks something like this:

通过此设置，现在可以遍历框架中的每个“控件”（图像框）并将它们全部隐藏，但名称与组合框值匹配的除外。用户表单模块中的代码，在 Combobox_Change() 函数中，看起来像这样：

 Private Sub ComboBox_Change()
    Dim SearchValue as String
    SearchValue = me.Combobox.value

    Dim Ctrl as Control
    For each Ctrl in Me.TestFrame.Controls
        If Ctrl.Name Like "img_" & SearchValue Then
            Ctrl.visible = True
        else
            Ctrl.Visible = False
        End If
    next Ctrl
 End Sub

I hope this helps, let me know what you think. :)

我希望这会有所帮助，让我知道您的想法。:)