showing picture

[chobanne]

Hi all

i have 10 pictures in sheet2. i need a macro who will show appropriate picture in sheet1 bellow cell A1. if in cell A1 value is 1 then macro will show picture 1 bellow A1, if its 2 then it will show picture 2 bellow A1 etc. thank you. And i need the picture is inside range A2:F20

 

[MickG]

It would be easier if you places all your pictures in sheet (1) , and then used the code below, made visible only the one with the matching number in "A1" and hid all the rest.

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Pic [COLOR=navy]As[/COLOR] Shape
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Pic [COLOR=navy]In[/COLOR] ActiveSheet.Shapes
   [COLOR=navy]If[/COLOR] Pic.Type = msoPicture [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Pic.Name = "Picture " & Range("A1") [COLOR=navy]Then[/COLOR]
            Pic.Visible = True
            Pic.Top = Range("A2").Top
            Pic.Left = Range("A2").Left
        [COLOR=navy]Else[/COLOR]
            Pic.Visible = False
        [COLOR=navy]End[/COLOR] If
   [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Pic
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[chobanne]

It would be easier if you places all your pictures in sheet (1) , and then used the code below, made visible only the one with the matching number in "A1" and hid all the rest.

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Pic [COLOR=navy]As[/COLOR] Shape
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Pic [COLOR=navy]In[/COLOR] ActiveSheet.Shapes
   [COLOR=navy]If[/COLOR] Pic.Type = msoPicture [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Pic.Name = "Picture " & Range("A1") [COLOR=navy]Then[/COLOR]
            Pic.Visible = True
            Pic.Top = Range("A2").Top
            Pic.Left = Range("A2").Left
        [COLOR=navy]Else[/COLOR]
            Pic.Visible = False
        [COLOR=navy]End[/COLOR] If
   [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Pic
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

thank you. Thats great

 

[aysam]

Hi Mick
I tried to assign (A1) to a digital clock Seconds using

=SECOND(Digital Clock)

but it didn't work !!

the macro to the clock is working very well
I wonder Why your macro didn't work ???
Any help 'd be very appreciated

 

[aysam]

up

 

[MickG]

What's the code for the digital clock???

 

[chobanne]

It would be easier if you places all your pictures in sheet (1) , and then used the code below, made visible only the one with the matching number in "A1" and hid all the rest.

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Pic [COLOR=navy]As[/COLOR] Shape
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Pic [COLOR=navy]In[/COLOR] ActiveSheet.Shapes
   [COLOR=navy]If[/COLOR] Pic.Type = msoPicture [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Pic.Name = "Picture " & Range("A1") [COLOR=navy]Then[/COLOR]
            Pic.Visible = True
            Pic.Top = Range("A2").Top
            Pic.Left = Range("A2").Left
        [COLOR=navy]Else[/COLOR]
            Pic.Visible = False
        [COLOR=navy]End[/COLOR] If
   [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Pic
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Hello mick i wonder can you make this macro to show 4 pictures depends of the value from 4 cells. macro you made was: A1 value show picture in A2. i need now beside that to manage that R1 (A20) (R21) values to show pictures in R2 (A21) (R21). Thank you for your time.

 

[MickG]

Do the pictures that you want to show in "A2" & "R2" all have different names :- "Picture1, Picture2" etc.

 

[chobanne]

yes they have

 

[MickG]

Try this:-
NB:- If you use the same number for both , then "A2" will fill with the picture.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Pic [COLOR="Navy"]As[/COLOR] Shape
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Pic [COLOR="Navy"]In[/COLOR] ActiveSheet.Shapes
   [COLOR="Navy"]If[/COLOR] Pic.Type = msoPicture [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Pic.Name = "Picture " & Range("A1") [COLOR="Navy"]Then[/COLOR]
            Pic.Visible = True
            Pic.Top = Range("A2").Top
            Pic.Left = Range("A2").Left
        
        [COLOR="Navy"]ElseIf[/COLOR] Pic.Name = "Picture " & Range("R1") [COLOR="Navy"]Then[/COLOR]
            Pic.Visible = True
            Pic.Top = Range("R2").Top
            Pic.Left = Range("R2").Left
        [COLOR="Navy"]Else[/COLOR]
            Pic.Visible = False
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Pic
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick