Get Picture from Worksheet into Excel Userform

Question

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".

I have created the below UserForm:

Form 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

Please advise.

Answer 1

I think that this is probably the best solution, so far, to the originally posted problem - probably far too late for the original question.

I too have come here looking for a solution that is not here. But, what is here hints towards to a solution. I will explain. Apologies for the length but hopefully there is clarity.

Background:- I have been designing a word game that uses a 10 x 10 grid. I could do this in a UserForm using a grid of 100 labels but that is cumbersome to process. Also, importantly, I want to benefit from the rich cell formatting powers of Excel cells to provide a wider range of visual cues than standard controls.

The solution is to use the Camera tool to create an image inside the workbook that is dynamically updated. The problem with this is that the image exists as a Shape object and, as discussed above and elsewhere, there is no apparent way to programmatically get that image into a UserForm control.

The 'workaround' solution lies in the fact that you can export an image of a Chart, as an image file, then load it as a Control.Picture I have used this previously to provide chart based feedback directly into UserForms in dashboard type applications.

The 'trick' is to create a Chart sheet in the host workbook. Leave the Chart empty/blank - do not populate it with data.

Use the Camera control to make an image of the cell range that you want to reproduce as an image.

Insert the image into a worksheet - drag-insert as if placing a Shape in a Worksheet.

Position and size the image to fit the whole of the Chart Area - this may be a limitation depending on the aspect ratio of your image. Some experimentation may be necessary to get an acceptable result.

Now you can export* the chart as an image (to a file) then load it using PictureLoad and, voila, you get your image in your UserForm.

*This export and load process is documented at other sites and I do not describe it here.

I have tested this using both Label and Image controls and both work. Don't forget to set the control's PictureSizeMode appropriately to correctly display the image e.g. fmPictureSizeModeStretch.

If you want the image to interact dynamically then each influential change will need to trigger an image update. The ImageUpdate() routine will need to:-

• 1 - Re-export the chart to the image file - the default export will overwrite the existing file.
• 2 - Re-do the control.Picture = LoadPicture(sFilePath) action.

This process can exhibit a short and apparent delay depending on your system and the location of the exported image file. If, as is my situation, you are running Excel on your PC and the file is in a location on your local hard drive that will likely be barely noticeable. If the file is on a server that may slow things by an unpredictable amount.

I have no experience with Office 365 and whether that brings additional issues to the table.

For reference, I am using Office 2019 Pro. This will likely be my last version of Excel - been using it since v2.1 with runtime Windows.

If I get time I will make some generic example code and post it below here as a follow up - not done that before and I just wanted to get this posted. Hopefully it will help someone else who finds their way here.

Answer 2

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.

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:

 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. :)

Answer 3

I figured it out!

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

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

Set the visible property of the frame to "False": Visible 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

Name the images: Name 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.png Move Away 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

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

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.

Answer 4

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.

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().

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.