picture in userform from sheet

[muriealdurian]

is it possible to have an image control in a userform to reference a picture inserted on a worksheet?

i have a number of pictures i want to load depending on certain events that happen in my sheet. i would like to be able to have this on multiple computers, and i would prefer to not to have to copy over the workbook AND a picture folder. i would like this to be as self contained as possible.

thanks!

 

[MickG]

Hi, You can load pictures from your Sheet to A Userform Using "Ctrl+C" to Copy & Ctrl +V" to Paste into the Userform Picture Properties List.
I don't think you can do it using VBA.
You Could Paste a number of image controls onto you Userform, with different pictures , and then Make them Visible or not, as required.
Regards Mick

 

[muriealdurian]

i had hoped to use a large (100+) images for the application i'm working on. each graphic not being more than 133X100, so how horribly will this bog down the userform when trying to load it?

 

[MickG]

Hi, I have Loaded 50 Pictures with the Copy Paste Methods and have had no problems, by using the code below.
My Userform Has 1 Combobox and 50 Image controls.
If you load the Userform Comboboxwith the Image Names (Add Range address of Names in Properties "RowSource") . In My Case this was simply "Image1 to Image50", But you will probably want to change the Image Names.
Paste all the code into the top of the Userform Module in Design Mode.
When you run the code it initially Hides all the pictures, then on selection from the Combobox, will show the Picture required.
This may give you some ideas.

[COLOR=navy]Dim[/COLOR] Temp
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Change()
[COLOR=navy]Dim[/COLOR] Mage [COLOR=navy]As[/COLOR] Control
[COLOR=navy]If[/COLOR] Temp = "Yes" [COLOR=navy]Then[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Mage [COLOR=navy]In[/COLOR] UserForm4.Controls
    [COLOR=navy]If[/COLOR] TypeName(Mage) = "Image" [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Mage.Name = ComboBox1 [COLOR=navy]Then[/COLOR]
             Mage.Visible = True
                MsgBox Mage.Name
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Mage
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Click()
Temp = "Yes"
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
[COLOR=navy]Dim[/COLOR] Mage [COLOR=navy]As[/COLOR] Control
Temp = "No"
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Mage [COLOR=navy]In[/COLOR] UserForm4.Controls
    [COLOR=navy]If[/COLOR] TypeName(Mage) = "Image" [COLOR=navy]Then[/COLOR]
        Mage.Visible = False
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Mage
ComboBox1.ListIndex = 0
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[RoryA]

You could also load all the pictures into an imagelist control and then simply assign the required one to the image control when you need to.

 

[MickG]

Thanks for that Rory, Where did you get your "ImageList" Control From ??
PS :- Nice Picture !!
PPS Thanks "Just Found It "!!
Mick

 

[MickG]

Rory, have you got a bit of code to do this. This does'nt work.

Me.Image1 = ImageList1.ListImages.Index(1)

I Have a "ImageList" On the Userform with 3 (Index 1 to 3) pictures in it.
Regards Mick

 

[muriealdurian]

Thanks for your help everyone!

i'm curious about the imagelist myself.

 

[RoryA]

Sure - it would be:

Set Me.Image1.Picture = Me.ImageList1.ListImages(1).Picture

for example.

 

[MickG]

Thanks Rory, That seems to work fine. I also, with a bit of Google Research and Experimentation found :- for Userform

With Me.Controls("ImageList1").ListImages
Me.Image1.Picture = .Item(1).Picture
End with

And for Image control in worksheet I got:-

Dim ilist As ImageList
With ThisWorkbook
    ' load the picture from our imagelist
    Set ilist = .Worksheets("Sheet10").OLEObjects("ImageList1").Object_
With ilist.ListImages
    Sheets("Sheet10").Image1.Picture = .Item(2).Picture
End With
End With

Thanks for the Idea & Help
Regards Mick