Clip art within a macro

[Mike F.]

I would like to use the IF Function and have a clip art
appear if the "value_if_true" is met.
I have been trying but can't seem to get it to work.
Would appreciate help from someone out there...Thanks

 

[Harlan Grove]

I would like to use the IF Function and have a clip art
appear if the "value_if_true" is met.
I have been trying but can't seem to get it to work.
Would appreciate help from someone out there...Thanks

Can't be done using formulas. You'd need to use a Calculate event handler.

 

[David]

Harlan,
Could you please give example code?
TIA

 

[Dave Peterson]

I put a picture on the worksheet and named it "Picture 1". I put a formula in
A1 that evaluated to true or false.

I could hide/show the picture with code like this:

Option Explicit
Private Sub Worksheet_Calculate()
Me.Pictures("picture 1").Visible = CBool(Me.Range("a1").Value)
End Sub

Each time the worksheet calculates, it runs this code. And the picture gets
hidden/shown accordingly.

Rightclick on the worksheet tab that should have this behavior and select view
code. Paste this in. And try it out.

And if you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Harlan Grove]

Could you please give example code?

First, insert some image in a worksheet and ensure it's named 'Picture 1'.
Copy the following code into the class module for the worksheet in which
'Picture 1' should appear when, e.g., cell A1 in that worksheet evaluates
to -1, but should not appear otherwise. This assumes cell A1 contains a
formula that would evaluate to -1, this triggerring the Calculate event
handler.


Option Explicit


Private Sub Worksheet_Calculate()
Static state As Boolean

On Error GoTo CleanUp

Application.EnableEvents = False

If (CDbl(Me.Range("A1").Value) = -1) <> state Then
Call TogglePic1
state = Not state
End If

CleanUp:
Application.EnableEvents = True
End Sub


Private Sub TogglePic1()
Dim x As Variant

With Me.Shapes("Picture 1")
.LockAspectRatio = msoFalse

x = Evaluate("Pic1H")
If IsError(x) Then
Me.Names.Add Name:="Pic1H", RefersTo:=.Height, Visible:=False
.Height = 0#
ElseIf .Height < 1# Then
.Height = x
Else
.Height = 0#
End If

x = Evaluate("Pic1W")
If IsError(x) Then
Me.Names.Add Name:="Pic1W", RefersTo:=.Width, Visible:=False
.Width = 0#
ElseIf .Width < 1# Then
.Width = x
Else
.Width = 0#
End If
End With
End Sub

 

[David]

Thanks Harlan,
After first glance (and the initial engage brain attempt)
I detected smoke coming out of my ears! I will persist
however, and remain confident of assimilating your wisdom.
David