Userform needs to display an image based on Listbox selection

PeterMac65

New Member
Joined
May 7, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
1619998214642.png


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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,758
Office Version
  1. 2016
Platform
  1. Windows
Here is an example how to put and get image
 
Solution

PeterMac65

New Member
Joined
May 7, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Thanks Zot I've been looking for something like that for days.
 

PeterMac65

New Member
Joined
May 7, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
1620005662493.png
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,758
Office Version
  1. 2016
Platform
  1. Windows
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:
 

PeterMac65

New Member
Joined
May 7, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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


1620009893232.png
 

PeterMac65

New Member
Joined
May 7, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,758
Office Version
  1. 2016
Platform
  1. Windows
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
:unsure: 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.
 

Forum statistics

Threads
1,143,687
Messages
5,720,301
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top