Help with hiding shapes with IF statement VBA

[rick23]

The code below work perfectly if I am in the same worksheet and make the change to the IF statement from cell A1 to change A26 from "whole" or "", but when I put an equal sign in A1 to reference another sheet's cell, then it does not work. Any ideas how to fix this. I have another formula in a different worksheet that changes A1, which then changes A26. Below is my code - thanks in advance</SPAN>

Private Sub Worksheet_Change(ByVal Target As Range)</SPAN>

If Range("A21").Value = "Whole" Then</SPAN>
ActiveSheet.Shapes("Rounded Rectangle 8").Visible = False</SPAN>
ActiveSheet.Shapes("Rounded Rectangle 14").Visible = False</SPAN>
ActiveSheet.Shapes("Rounded Rectangle 16").Visible = False</SPAN>
Else</SPAN>
ActiveSheet.Shapes("Rounded Rectangle 8").Visible = True</SPAN>
ActiveSheet.Shapes("Rounded Rectangle 14").Visible = True</SPAN>
ActiveSheet.Shapes("Rounded Rectangle 16").Visible = True</SPAN>
End If</SPAN>
End Sub</SPAN>

 

[JoeMo]

Amend your change event and worksheet to trigger with a change to the precedent cell for A1.

 

[rick23]

Amend your change event and worksheet to trigger with a change to the precedent cell for A1.

Thanks alot for the reply. I am sorry, but I am considered a newbie at this stuff. I understand what you are saying by changing the event, but what would the code look like showing precedent for cell A1. Sorry about asking, but again I appreciate that help.

 

[JoeMo]

Thanks alot for the reply. I am sorry, but I am considered a newbie at this stuff. I understand what you are saying by changing the event, but what would the code look like showing precedent for cell A1. Sorry about asking, but again I appreciate that help.

Looking at your post some more b/c your precedent cell contains a formula, it may be that you can get the result you want by changing the current code from a worksheet_change event to a worksheet_calculate event. To try this simply change this line:

Private Sub Worksheet_Change(ByVal Target As Range)

to this:

Private Sub Worksheet_Calculate()

and change "Activesheet" to "Me" (w/o the quote marks) everywhere it appears in the code.

 

[rick23]

Looking at your post some more b/c your precedent cell contains a formula, it may be that you can get the result you want by changing the current code from a worksheet_change event to a worksheet_calculate event. To try this simply change this line:

Private Sub Worksheet_Change(ByVal Target As Range)

to this:

Private Sub Worksheet_Calculate()

and change "Activesheet" to "Me" (w/o the quote marks) everywhere it appears in the code.

Worked like a charm! Thank you so much.

 

[JoeMo]

Worked like a charm! Thank you so much.

You are welcome - thanks for the reply.