VBA wont update when reference cell changes due to a formula

[neilcsmith1984]

Hi,

I have the below code that hides shapes on my sheet depending on the value in A1.

The value of A1 derives from a formula and when the formula changes the value in A1 the shapes do not adjust accordingly, the code only works when i type a value into cell A1.

Is it possible to change the code to work whenever the formula in A1 changes?

Sub SetVisible()

Dim s1 As Shape, s2 As Shape

Set s1 = Me.Shapes("Rectangle 1")
Set s2 = Me.Shapes("Rectangle 2")

Select Case UCase(Range("A1").Value)
Case "2015"
s1.Visible = msoTrue
s2.Visible = msoFalse
Case "2016"
s1.Visible = msoFalse
s2.Visible = msoTrue
Case "ALL"
s1.Visible = msoTrue
s2.Visible = msoTrue
Case Else
s1.Visible = msoFalse
s2.Visible = msoFalse
End Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A1").Address Then SetVisible
End Sub


Thanks

 

[Fluff]

Duplicate 2 Vba codes on one sheet

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).