View Full Version : Solved: Error on Insert Picture code...
genracela
07-15-2010, 11:17 PM
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
07-15-2010, 11:53 PM
And now the happy picture is missing....:cry: :boohoo :sad2:
:help
I did not look at the wb, but believe you will want to look at the Caculate event rather than Change.
genracela
07-16-2010, 12:09 AM
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?
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
07-16-2010, 01:02 AM
Ohhhhh, yeah,
Ok I'll try....
Thanks again Mark!
genracela
07-16-2010, 01:12 AM
Hmmmm, it's still the same...
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
07-18-2010, 04:53 PM
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:(
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
07-19-2010, 01:20 AM
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!!!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.