Show Shapes Based on Cell Value

[simbk]

[h=2]

Show Shapes Based on Cell Value[/h]

Advertisement ​

I want to show and hide shapes on a sheet based on the cell value >1. If cell A1 = 1 the shape should be visible and for value is 0, the shape should be hidden. I thought this would be fairly straight forward but the following code is not working.

Private Sub Worksheet_Calculate()
If Range("A1").Value = 1 Then
ActiveSheet.Shapes("Oval 1").Visible = True
Else
ActiveSheet.Shapes("Oval 1").Visible = False
End If
If Range("A2").Value = 1 Then
ActiveSheet.Shapes("Oval 2").Visible = True
Else
ActiveSheet.Shapes("Oval 2").Visible = False
End If
End Sub​

 

[Yongle]

please use code tags around code (click on # icon above post window and paste code between tags)

This works when any cell is selected

[I]Private Sub Worksheet_SelectionChange[/I](ByVal Target As Range)
    If Range("A1").Value = 1 Then
       ActiveSheet.Shapes("Oval 1").Visible = True
    Else
       ActiveSheet.Shapes("Oval 1").Visible = False
    End If
    If Range("A2").Value = 1 Then
       ActiveSheet.Shapes("Oval 2").Visible = True
    Else
       ActiveSheet.Shapes("Oval 2").Visible = False
    End If
End Sub

When do you want the macro to trigger?

 

[simbk]

please use code tags around code (click on # icon above post window and paste code between tags)

This works when any cell is selected

[I]Private Sub Worksheet_SelectionChange[/I](ByVal Target As Range)
    If Range("A1").Value = 1 Then
       ActiveSheet.Shapes("Oval 1").Visible = True
    Else
       ActiveSheet.Shapes("Oval 1").Visible = False
    End If
    If Range("A2").Value = 1 Then
       ActiveSheet.Shapes("Oval 2").Visible = True
    Else
       ActiveSheet.Shapes("Oval 2").Visible = False
    End If
End Sub

When do you want the macro to trigger?
TQ you , I am a beginner,

 

[Yongle]

When do you want the macro to trigger?

Give me a clue. What are you trying to achieve?

 

[mikerickson]

How are A1 and A2 getting their values? Is the user manually entering them, or is there a formula?
The answer to that would determine the best place to put this code.

ActiveSheet.Shapes("Oval1").Visible = (1 <= Range("A1").Value)
ActiveSheet.Shapes("Oval2").Visible = (1 <= Range("A2").Value)

 

[simbk]

tell u later, my English very Poor，