Hide and Unhide Auto Shape Based on Value of the Other Cell

[igbert]

I have created a Auto Shape with Text "Click Here to go Sheet 3" and assign
the shape with a macro.

Is there a way to hdie the shape and its text when Cell A1 has the words "DR"?

Thanks.

Igbert

 

[Jamo via OfficeKB.com]

Good afternoon,

On worksheet (sheet 1) activation and/or change, you can use the following:

Private Sub Worksheet_Activate()
If Range("A1").Value = "DR" Then
ActiveSheet.Shapes("Arrow1").Visible = False
Else
ActiveSheet.Shapes("Arrow1").Visible = True
End If
End Sub

Where Arrow1 is the name of your autoshape.

Kind regards,
James

 

[Paul C]

Jamo solution will do the trick and hide the shape whenever the sheet is
activated and Cell A1 already equals "DR".

However if someone activates the sheets and then enters "DR" in cell A1 the
shape will not hide until the sheet selection is toggled again.

If you need to cover this case also use the Sheet Change event instead of
the Sheet Activate event.

Simply change the first line to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

one disadvantage is that this will run everytime the sheet is changed. You
can focus it to skip most of the code if A1 is not the changed cell by using.

If Target.Row<>1 and Target.Column <>1 then exit sub

--
If this helps, please remember to click yes.

Good afternoon,

On worksheet (sheet 1) activation and/or change, you can use the following:

Private Sub Worksheet_Activate()
If Range("A1").Value = "DR" Then
ActiveSheet.Shapes("Arrow1").Visible = False
Else
ActiveSheet.Shapes("Arrow1").Visible = True
End If
End Sub

Where Arrow1 is the name of your autoshape.

Kind regards,
James
----
jamotarpey[at]gmail[dot]com

I have created a Auto Shape with Text "Click Here to go Sheet 3" and assign
the shape with a macro.

Is there a way to hdie the shape and its text when Cell A1 has the words "DR"?

Thanks.

Igbert

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200912/1

.

 

[Paul C]

Sorry the first line should really be

Private Sub Worksheet_Change(ByVal Target As Range)

not

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

--
If this helps, please remember to click yes.

Jamo solution will do the trick and hide the shape whenever the sheet is
activated and Cell A1 already equals "DR".

However if someone activates the sheets and then enters "DR" in cell A1 the
shape will not hide until the sheet selection is toggled again.

If you need to cover this case also use the Sheet Change event instead of
the Sheet Activate event.

Simply change the first line to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

one disadvantage is that this will run everytime the sheet is changed. You
can focus it to skip most of the code if A1 is not the changed cell by using.

If Target.Row<>1 and Target.Column <>1 then exit sub

--
If this helps, please remember to click yes.

Good afternoon,

On worksheet (sheet 1) activation and/or change, you can use the following:

Private Sub Worksheet_Activate()
If Range("A1").Value = "DR" Then
ActiveSheet.Shapes("Arrow1").Visible = False
Else
ActiveSheet.Shapes("Arrow1").Visible = True
End If
End Sub

Where Arrow1 is the name of your autoshape.

Kind regards,
James
----
jamotarpey[at]gmail[dot]com

I have created a Auto Shape with Text "Click Here to go Sheet 3" and assign
the shape with a macro.

Is there a way to hdie the shape and its text when Cell A1 has the words "DR"?

Thanks.

Igbert

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200912/1

.

 

[igbert]

Thanks James,

It works like magic.

Good afternoon,

On worksheet (sheet 1) activation and/or change, you can use the following:

Private Sub Worksheet_Activate()
If Range("A1").Value = "DR" Then
ActiveSheet.Shapes("Arrow1").Visible = False
Else
ActiveSheet.Shapes("Arrow1").Visible = True
End If
End Sub

Where Arrow1 is the name of your autoshape.

Kind regards,
James
----
jamotarpey[at]gmail[dot]com

I have created a Auto Shape with Text "Click Here to go Sheet 3" and assign
the shape with a macro.

Is there a way to hdie the shape and its text when Cell A1 has the words "DR"?

Thanks.

Igbert

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200912/1

.

 

[igbert]

Hi Paul,

I also implemented your suggestion.

Many thanks.

Igbert

Sorry the first line should really be

Private Sub Worksheet_Change(ByVal Target As Range)

not

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

--
If this helps, please remember to click yes.

Jamo solution will do the trick and hide the shape whenever the sheet is
activated and Cell A1 already equals "DR".

However if someone activates the sheets and then enters "DR" in cell A1 the
shape will not hide until the sheet selection is toggled again.

If you need to cover this case also use the Sheet Change event instead of
the Sheet Activate event.

Simply change the first line to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

one disadvantage is that this will run everytime the sheet is changed. You
can focus it to skip most of the code if A1 is not the changed cell by using.

If Target.Row<>1 and Target.Column <>1 then exit sub

--
If this helps, please remember to click yes.

Good afternoon,

On worksheet (sheet 1) activation and/or change, you can use the following:

Private Sub Worksheet_Activate()
If Range("A1").Value = "DR" Then
ActiveSheet.Shapes("Arrow1").Visible = False
Else
ActiveSheet.Shapes("Arrow1").Visible = True
End If
End Sub

Where Arrow1 is the name of your autoshape.

Kind regards,
James
----
jamotarpey[at]gmail[dot]com

igbert wrote:
I have created a Auto Shape with Text "Click Here to go Sheet 3" and assign
the shape with a macro.

Is there a way to hdie the shape and its text when Cell A1 has the words "DR"?

Thanks.

Igbert

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200912/1

.