displaying a picture based on a cell value example coding problem excel

[bgailey]

My spreadsheet consists of 3 different sets of data. When the number returns a certain answer the image should switch based on the range of the answer. I have sucessfully done this for the first set of data that I have but I can not get the second set to work without it messing up and not showing the first set. This is the code that I have right now:
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("B5")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

This is one of the examples I have tried to get 2 of the sets of data to work and it has not worked:

Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("B5")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub
Private Sub Worksheet_CalculateCH()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("B8")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With

 

[Andrew Poulsom]

You can only have one Worksheet_Calculate event procedure, so you need to add the code in the second to the original.

 

[bgailey]

You can only have one Worksheet_Calculate event procedure, so you need to add the code in the second to the original.

I have done this also. This is the code, but it still removes the image from the first set of data sets and returns only the image with the second set.

Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("B5")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
Dim chPic As Picture
Me.Pictures.Visible = False
With Range("B8")
For Each chPic In Me.Pictures
If chPic.Name = .Text Then
chPic.Visible = True
chPic.Top = .Top
chPic.Left = .Left
Exit For
End If
Next chPic
End With

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Calculate()
    Dim oPic As Picture
    Me.Pictures.Visible = False
    Set oPic = Me.Pictures(Range("B5").Text)
    With Range("B5")
        oPic.Visible = True
        oPic.Top = .Top
        oPic.Left = .Left
    End With
    Set oPic = Me.Pictures(Range("B8").Text)
    With Range("B8")
        oPic.Visible = True
        oPic.Top = .Top
        oPic.Left = .Left
    End With
End Sub

 

[bgailey]

Try:

Private Sub Worksheet_Calculate()
    Dim oPic As Picture
    Me.Pictures.Visible = False
    Set oPic = Me.Pictures(Range("B5").Text)
    With Range("B5")
        oPic.Visible = True
        oPic.Top = .Top
        oPic.Left = .Left
    End With
    Set oPic = Me.Pictures(Range("B8").Text)
    With Range("B8")
        oPic.Visible = True
        oPic.Top = .Top
        oPic.Left = .Left
    End With
End Sub

Andrew,

That indeed looks like it will work! Thank you. One problem though and I am not sure about how far I can stretch excel, but if two different data sets return the same answer at the same time, it should return the same image at the same time. Do I have to make individual images for each data set because it will not return the same image at the same time?

Thanks for all your time so far.

 

[Andrew Poulsom]

You can only have one picture with the same name so you heve to allow for that.