Clip art within a macro

  • Thread starter Thread starter Mike F.
  • Start date Start date
M

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
 
Mike F. said:
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
Click to expand...

Can't be done using formulas. You'd need to use a Calculate event handler.
 
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
 
David said:
Could you please give example code?
Click to expand...

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
 
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
 
Back
Top