Hide Autoshape based on Cell Value

[tomic]

I've tried several things, based on some other posts here, but haven't been
successful in getting this to work.

I would like to hide an autoshape, named "Change" based on the value of a
cell "E5" in a worksheet named "Flow Rates". Basically, if E5 = 0, I don't
want the user to see this autoshape.

Any help would be appreciated. I have a feeling this is fairly simple, but
as my VB knowledge is fairly limited, I haven't been able to figure it out.

Thanks.

 

[Don Guillett]

Right click sheet tab>view code>insert this>Now if you change cell e5 the
macro will fire.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("e5")) Is Nothing Then Exit Sub
If Target = 0 Then
'msgBox "Hide"
ActiveSheet.Shapes("change").Visible = False
Else
'MsgBox "Show"
ActiveSheet.Shapes("change").Visible = True
End If
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Right-click the "Flow Rates" worksheet tab, select "View Code" from the
popup menu that appears and then copy/paste this code into the code window
that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$5" Then
ActiveSheet.Shapes("change").Visible = Target.Value <> 0
End If
End Sub

Rick

 

[Don Guillett]

Nice

 

[Rick Rothstein \(MVP - VB\)]

I have my moments... thanks.

Rick

Nice

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[tomic]

Thanks for the quick response.

Does this change if E5 contains a formula? It's working if I enter a zero
into the cell, but if I have a formula in the cell, and the formula returns a
zero, it doesn't seem to work?

 

[Peter T]

One more -

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bVis As Boolean
Dim cel As Range
On errExit GoTo errExit
Set cel = Range("E5")
If Not Intersect(Target, cel) Is Nothing Then
bVis = Not cel = 0
With ActiveSheet.Shapes("change")
If .Visible <> bVis Then .Visible = bVis
End With
End If
errExit:
End Sub

Caters for user changing multiple cells that include E5 without it
necessarily being the active cell, eg paste or delete a block. Then only
change visibility if needs to retain Undo where possible.

Regards,
Peter T

 

[Don Guillett]

You would need to change the event to calculate, as shown, or another if you
don't want it to fire with each calculation.

Private Sub Worksheet_Calculate()
ActiveSheet.Shapes("change").Visible = Range("e5").Value <> 0
End Sub

 

[Peter T]

I see from your recent reply to Rick that E5 is a formula cell. As long as
it only refers to cells on the same sheet, in the code below change.

If Not Intersect(Target, cel) Is Nothing Then

to
If Not Intersect(Target, cel.Precedents) Is Nothing Then

If the formula refers to cell(s) on (an)other sheet(s), put a similar
formula in a helper cell on the same sheet and link E1 to that.

Regards,
Peter T

 

[Rick Rothstein \(MVP - VB\)]

Does this change if E5 contains a formula?

Nope! That is a completely different problem. Replace the code I posted
earlier with this code instead...

'********** START OF CODE **********
Dim LastValueInE5 As Double

Private Sub Worksheet_Activate()
LastValueInE5 = Worksheets("Sheet1").Value
End Sub

Private Sub Worksheet_Calculate()
If Range("E5").Value <> LastValueInE5 Then
ActiveSheet.Shapes("change").Visible = Range("E5").Value <> 0
End If
LastValueInE5 = Range("E5").Value
End Sub
'********** END OF CODE **********

Rick

 

[tomic]

Thanks for your help.
This works great!

You would need to change the event to calculate, as shown, or another if you
don't want it to fire with each calculation.

Private Sub Worksheet_Calculate()
ActiveSheet.Shapes("change").Visible = Range("e5").Value <> 0
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)