Solved: Error on Insert Picture code...

[genracela]

I'm trying this code that I copied in the web.
Private Sub Worksheet_Change(ByVal _
Target As Excel.Range)
Dim myRange As Range
On Error Resume Next
Set myRange = Intersect(Range("B3"), Target)
If myRange = "YES" Then
With ActiveSheet.Shapes("Happy")
.Visible = True
End With
With ActiveSheet.Shapes("Sad")
.Visible = False
End With
ElseIf myRange = "NO" Then
With ActiveSheet.Shapes("Happy")
.Visible = False
End With
With ActiveSheet.Shapes("Sad")
.Visible = True
End With
Else
With ActiveSheet.Shapes("Happy")
.Visible = True
End With
With ActiveSheet.Shapes("Sad")
.Visible = True
End With
End If
End Sub



It actually works if you just manually typed in the words "Yes" or "No"
but when I use the formula:
=IF(B5="OK", "YES", "NO")

It doesn't work anymore, it will work on my first choice ( either "Yes" or "no"), but it will not anymore work if you choose again.

What I want is everytime I make a choice between "Yes" or "No" it will show the picture that it should show.

I attached a sample worksheet, thanks!

[genracela]

And now the happy picture is missing....:cry: :boohoo :sad2:

:help

[GTO]

I did not look at the wb, but believe you will want to look at the Caculate event rather than Change.

[genracela]

But the calculation returns "Yes" or "No" and the cell shows "Yes" or "No". The formula is in column B3, and the cell that I change is in B5. Why is it if I entered "Yes" or "No" manually it's okay... but if the formula is the one returning "Yes" or "No" it doesn't work?

[GTO]

I am not sure that we are following each other. What I mean is that you want to try the worksheet's calculate event for 'changes' resulting from a formula output.


Private Sub Worksheet_Calculate()

End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

[genracela]

Ohhhhh, yeah,

Ok I'll try....

Thanks again Mark!

[genracela]

Hmmmm, it's still the same...

[GTO]

Private Sub Worksheet_Calculate()

Select Case Me.Cells(3, "B").Value = "YES"
Case True
Me.Shapes("Happy").Visible = msoCTrue
Me.Shapes("Sad").Visible = msoFalse
Case False
Me.Shapes("Happy").Visible = msoFalse
Me.Shapes("Sad").Visible = msoCTrue
End Select
End Sub

[genracela]

I tried:
Private Sub Worksheet_Calculate()

Select Case Me.Cells(3, "B").Value = "YES"
Case True
Me.Shapes("Happy").Visible = msoCTrue
Me.Shapes("Sad").Visible = msoFalse
Case False
Me.Shapes("Happy").Visible = msoFalse
Me.Shapes("Sad").Visible = msoCTrue
End Select
End Sub
Private Sub Worksheet_Change(ByVal _
Target As Excel.Range)
Dim myRange As Range
On Error Resume Next
Set myRange = Intersect(Range("B3"), Target)
If myRange = "YES" Then
With ActiveSheet.Shapes("Happy")
.Visible = True
End With
With ActiveSheet.Shapes("Sad")
.Visible = False
End With
ElseIf myRange = "NO" Then
With ActiveSheet.Shapes("Happy")
.Visible = False
End With
With ActiveSheet.Shapes("Sad")
.Visible = True
End With
Else
With ActiveSheet.Shapes("Happy")
.Visible = True
End With
With ActiveSheet.Shapes("Sad")
.Visible = True
End With
End If
End Sub


and just:


Private Sub Worksheet_Calculate()

Select Case Me.Cells(3, "B").Value = "YES"
Case True
Me.Shapes("Happy").Visible = msoCTrue
Me.Shapes("Sad").Visible = msoFalse
Case False
Me.Shapes("Happy").Visible = msoFalse
Me.Shapes("Sad").Visible = msoCTrue
End Select
End Sub


But, it doesn't change any:(

[GTO]

If my laptop gets a smidgeon worse, I'll be using a hammer and chisel on a cave wall to calculate, and this still seems to work...

Are you keeping the code in the correct sheet's module?

[genracela]

Thanks as usual Mark!!! Mwahhh! Mwahhh! Mwahhh!

By the way, since I already given up looking for a macro for this hide unhide image thing, I stumbled upon this solution using Name Manager in another forum.

But anyway, I think I'll be using your macro instead... (it occupies less memory space)

Thanks again!!!!