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

  • Thread starter Thread starter igbert
  • Start date Start date
I

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 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.


Jamo via OfficeKB.com said:
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
Click to expand...

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

.
Click to expand...
 
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.


Paul C said:
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.


Jamo via OfficeKB.com said:
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
Click to expand...

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

.
Click to expand...
Click to expand...
 
Thanks James,

It works like magic.


Jamo via OfficeKB.com said:
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
Click to expand...

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

.
Click to expand...
 
Hi Paul,

I also implemented your suggestion.

Many thanks.

Igbert



Paul C said:
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.


Paul C said:
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.


Jamo via OfficeKB.com said:
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

.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top