Userform needs to display an image based on Listbox selection

[PeterMac65]

Hi
I have a form with ListBox1 and Image1 objects

ListBox1 contains the names of 5-6 images

When a listbox item is selected i want to show the picture in "Image1"

Seems simple but I can't figure how to get the image from the worksheet into the the Userform

Can someone please help me. (NOTE: I copied and pasted an image to show what i'd like to achieve)

Regards PeterMac

Option Explicit
Private Sub ListBox1_Click()
Call Pict(ListBox1.ListIndex)
End Sub


Private Sub UserForm_Initialize()
Dim Pic As Object
Dim x As Integer
x = 0
For Each Pic In Sheets("Cost").Shapes 'Pictures
If TypeName(Pic) = "Shape" Then
If Left(Pic.Name, 7) <> "Comment" Then
ListBox1.AddItem Pic.Name
x = x + 1
End If
End If
Next Pic
Debug.Print x
ListBox1.ListIndex = 0
Call Pict(0)
End Sub


Sub Pict
Dim Ans As String
Dim rng As Excel.Range
Dim cht As Excel.ChartObject
Dim Pth As String
Dim Pic As Object
With ListBox1
Set Pic = Sheets("Cost").Shapes(.List)
Pic.Copy 'Picture xlScreen, xlBitmap
Debug.Print Pic.Name
Set cht = ActiveSheet.ChartObjects.Add(100, 0, Pic.Width, Pic.Height)

' Me.Image1.Picture = "THIS IS THE BIT I DONT KNOW"

Set cht = Nothing
End With
End Sub

 

[Zot]

Here is an example how to put and get image

VBA Case Study #6 – Displaying Images in Excel using VBA User Forms

<div class="at-above-post addthis_tool" data-url="https://www.launchexcel.com/excel-vba-case-study-6-fish-images-in-user-forms/"></div>Learn how to display selected images inside Excel User Forms with a little bit of VBA magic!<!-- AddThis Advanced Settings above via filter on get_the_excerpt...

www.launchexcel.com

 

[PeterMac65]

Thanks Zot I've been looking for something like that for days.

 

[Zot]

Thanks Zot I've been looking for something like that for days.

Glad able to search for you ?

 

[PeterMac65]

I spoke too soon
The pictures are all "snipped" and pasted into a sheet and have names. eg "Picture 4"

Can you suggest suitable code to show "Picture 4" in Iname1
UserForm1.Image1.Picture = Worksheets("Costs").OLEObjects("Bluegill").Object.Picture

UserForm1.Image1.Picture = Worksheets("Costs").????????????????????????????????

Regards Peter

 

[Zot]

I noticed that the VBA is for different approach.

In your case, you could loop through all pictures to find correct name you are looking for. The problem is the shape and picture in UserForm has no access to each other directly. I found a solution in webpage here:

Picture shapes and Image boxes in VBA

I would like to put an image that is in my worksheet into an image box which in in my form. So I have this picture in my worksheet: And I would like to display it into an image Any idea's?

answers.microsoft.com

 

[PeterMac65]

I feel I'm getting closer with the following code but am getting Run time error '438' Object doesn't support this property or method

Private Sub ComboBox1_Change()

Dim myfish As String
Dim X As Integer

'Change the Caption on the Form to display fish name
Frame1.Caption = ComboBox1.Value

'Set value based on selected fish in ComboBox
myfish = ComboBox1.Value

UserForm1.Image1.Picture = Worksheets("Cost").Shapes("Picture 2").Picture
End Sub

 

[Zot]

It is because no such command supported. I found another alternative way which probably simpler than the previous one

How to copy image from worksheet to userform in Excel?

www.extendoffice.com

Still not the way you approach it I guess

 

[PeterMac65]

Thanks Zot for your help,
I'm getting the feeling that Excel wont do what I require. I couldn't get rid of the errors in your second suggestion. Not surprising as the code was 8 years old. It seems odd that i know the names of the images in the worksheet but cant copy into a Userform.

Regards PeterMac

 

[Zot]

Thanks Zot for your help,
I'm getting the feeling that Excel wont do what I require. I couldn't get rid of the errors in your second suggestion. Not surprising as the code was 8 years old. It seems odd that i know the names of the images in the worksheet but cant copy into a Userform.

Regards PeterMac

I did not try
I was trying to get the image in shape as picture object but got mismatch type.

I am hoping for a simpler way . I will continue to monitor this and perhap find a good way but right now I'm working on my urgent office job.