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.

Right click sheet tabview codeinsert thisNow 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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tomic" wrote in message
...
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.

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


"tomic" wrote in message
...
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.

Nice

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"tomic" wrote in message
...
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.

I have my moments... thanks.

Rick

"Don Guillett" wrote in message
...
Nice

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"tomic" wrote in message
...
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.

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?

"Rick Rothstein (MVP - VB)" wrote:

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


"tomic" wrote in message
...
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.

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

"tomic" wrote in message
...
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.

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

"tomic" wrote in message
...
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?

"Rick Rothstein (MVP - VB)" wrote:

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


"tomic" wrote in message
...
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.

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

"Peter T" <peter_t@discussions wrote in message
...
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

"tomic" wrote in message
...
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.

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" wrote in message
...
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?

"Rick Rothstein (MVP - VB)" wrote:

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


"tomic" wrote in message
...
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.

Thanks for your help.
This works great!

"Don Guillett" wrote:

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

"tomic" wrote in message
...
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?

"Rick Rothstein (MVP - VB)" wrote:

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


"tomic" wrote in message
...
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.