Create a collection of pictures to work with VBA

[alosbh]

Hi guys.


Im using this code that i get from internet to show a dynamic picture.


Option Explicit


Private Sub Worksheet_Calculate()
Dim oPic As Picture
Pictures.Visible = False
With Range("b6")
For Each oPic In Pictures
If oPic.Name = .Text Then

oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
oPic.Height = .Height
oPic.Width = .Width



Exit For
End If
Next oPic
End With
End Sub




But this way it hides every image on my sheet. There is something i can do to work dynamically only with
the pictures i want?


Maybe create a collection of pictures and work inside it. So instead of ''For each oPic in Pictures'' i use
''For each oPic in MyColletion''
If it is possible, can you help me? Dont know how to create a collection of pictures and add them by name. Thank you.

 

[Domenic]

Try...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Calculate()
    [COLOR=darkblue]Dim[/COLOR] vPictureNames [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vPictureName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] oPic [COLOR=darkblue]As[/COLOR] Picture
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    vPictureNames = Array("Picture 1", "Picture 2", "Picture 3") [COLOR=green]'specify the relevant names accordingly[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] vPictureName [COLOR=darkblue]In[/COLOR] vPictureNames
        [COLOR=darkblue]Set[/COLOR] oPic = Me.Pictures(vPictureName)
        [COLOR=darkblue]If[/COLOR] Err = 0 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]With[/COLOR] Range("B6")
                [COLOR=darkblue]If[/COLOR] vPictureName = .Text [COLOR=darkblue]Then[/COLOR]
                    oPic.Left = .Left
                    oPic.Top = .Top
                    oPic.Width = .Width
                    oPic.Height = .Height
                    oPic.Visible = [COLOR=darkblue]True[/COLOR]
                [COLOR=darkblue]Else[/COLOR]
                    oPic.Visible = [COLOR=darkblue]False[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Else[/COLOR]
            Err.Clear
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] vPictureName
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[alosbh]

Try...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Calculate()
    [COLOR=darkblue]Dim[/COLOR] vPictureNames [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vPictureName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] oPic [COLOR=darkblue]As[/COLOR] Picture
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    vPictureNames = Array("Picture 1", "Picture 2", "Picture 3") [COLOR=green]'specify the relevant names accordingly[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] vPictureName [COLOR=darkblue]In[/COLOR] vPictureNames
        [COLOR=darkblue]Set[/COLOR] oPic = Me.Pictures(vPictureName)
        [COLOR=darkblue]If[/COLOR] Err = 0 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]With[/COLOR] Range("B6")
                [COLOR=darkblue]If[/COLOR] vPictureName = .Text [COLOR=darkblue]Then[/COLOR]
                    oPic.Left = .Left
                    oPic.Top = .Top
                    oPic.Width = .Width
                    oPic.Height = .Height
                    oPic.Visible = [COLOR=darkblue]True[/COLOR]
                [COLOR=darkblue]Else[/COLOR]
                    oPic.Visible = [COLOR=darkblue]False[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Else[/COLOR]
            Err.Clear
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] vPictureName
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

Thank you for you reply Dear. But im still in trouble to do what i want.
I just want to have multiple dynamic images.

By the code you sent i tried to use it twice, so i copied it, changed the Sub name and chaged the name of the pictures. But only one dynamic pic is working i dont know how.

 

[alosbh]

I changed some part of the code and now it worked, thank you

 

[Domenic]

You're very welcome!