How can I make VB Code run when cell is changed by a formula?

[babyg]

I have code that runs 4 macros to hide and unhide sheets when the value of a cell changes. I have it set up so that when a user selects a value from the listbox a formula in a hidden cell changes to either a 1 or 0 depending on the value selected. The problem is unless I manually click on the formula cell the code doesn't run. How can I automate it so that my code recognizes the value in the cell changed without having to double click it?

Here is the code I am using:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target) And Target.Address = "$A$20" Then
Select Case Target.Value
Case Is = 0
Run "ShowAdminSheets"
Run "HideUserSheets"
Case Is = 1
Run "HideAdminSheets"
Run "ShowUserSheets"
End Select
End If
End Sub

 

[NeonRedSharpie]

Change it to:

  Private Sub Worksheet_Change(ByVal Target As Excel.Range)If Target.Cells.Count > 1 Then Exit Sub
Select Case Range(A20).value
Case Is = 0
Run "ShowAdminSheets"
Run "HideUserSheets"
Case Is = 1
Run "HideAdminSheets"
Run "ShowUserSheets"
End Select
End If
End Sub

 

[babyg]

That doesn't seem to work either. It is still not recognizing it until I double click on it. Also when I double click now it is returning a compile error message.

 

[Smitty]

You can use the Calculate event, which will fire when the sheet calculates. Just note that it doesn't support the target argument.

 

[babyg]

ADVERTISEMENT

Can you please give me an idea of what that would look like? I'm sorry I'm new to VB.

 

[Smitty]

Using what was already posted:

Private Sub Worksheet_Calculate()
  Select Case Range("A20").Value
    Case Is = 0
      Run "ShowAdminSheets"
      Run "HideUserSheets"
    Case Is = 1
      Run "HideAdminSheets"
      Run "ShowUserSheets"
  End Select
End Sub

 

[babyg]

That works perfect. Thanks so much!