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

 

[chobanne]

thank it works perfect

 

[chobanne]

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

Hi Mick, You helped me with this thread, but i have one more question for you because i have a problem i cant manage myself. If i Type value in cell your macro works perfect, but when i connect it with values in cells whom i get from various formula operations, it works perfect only for first time... if i change something in formulas before, some conditions or anything that will change authorative cells, the pictures wont to change for some reasons. If i again type something in that cells it will change picture. It looks like that only if i type something there it will change picture. Can u help please it is very important to me. Something to refresh the pictures. Could it be that the problem is that I have written macro in excell 2010 and now i m on vacation and using other computer that only have excell 2007

 

[MickG]

Is the value in "A1" and or "R1" changing as a results of the formula change. ?? I can't see why the code does not run if the cell changes value.
Does it make any differencr if you use a Calculate Event

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

 

[chobanne]

Is the value in "A1" and or "R1" changing as a results of the formula change. ?? I can't see why the code does not run if the cell changes value.
Does it make any differencr if you use a Calculate Event

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

yes i calculate values in A1 and R1, and when i change values in them with other calculations it wont change the pictures. It looks like that macro works only first time when there is no picture, and dont work when i have a picture. Maybe i ll need some refresh code or something. Sorry i dont know what you mean with last question. Dont know excell so good. Thank you

 

[chobanne]

hm, can enyone help pls. i ll just need one refresh part in this macro to have this working properly.