Request for VBA Event for Running Code on Update of a Specific Cell

Hello EE People,
I am fairly unpracticed with VBA, so with VBA code below, I have only been able to make it run by clicking on another sheet in the workbook and then clicking again on the sheet the code runs on.  I really would like this to run when the K3 cell is updated?  Anyone know how to do this?


 Private Sub Worksheet_Activate()

 Dim term As Integer
      term = Range("K2").Value

      If term = 10 Then
      Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&RC1&""]"")"
      Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&$A23&""]"")"
     
      ElseIf term = 20 Then
      Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&RC1&""]"")"
      Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&$A23&""]"")"

      Else
     
     
 End If



Many thanks,

Lindsay
Lindsay_KAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexei KuznetsovMicrosoft Outlook MVPCommented:
It's

Private Sub Worksheet_Change(ByVal Target As Range)
...

Find the details in MS KB article:
https://support.microsoft.com/en-us/kb/213612
MacroShadowCommented:
Try this:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("K2")) Is Nothing Then
        Dim term As Integer
        term = Range("K3").Value
        Select Case term
            Case 10
                Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&RC1&""]"")"
                Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&$A23&""]"")"
            Case 20
                Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&RC1&""]"")"
                Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&$A23&""]"")"
        End Select
    End If
End Sub

Open in new window

Lindsay_KAuthor Commented:
Thank you so much Alexey and MacroShadow,
I tried both solutions, with Alexey's solution manifesting in the following code:

Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' update the record count for retained students according to the term specified.
    Set KeyCells = Range("K2")
   
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


     If KeyCells = 10 Then
     Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&RC1&""]"")"
     Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&$A23&""]"")"
     
     ElseIf KeyCells = 20 Then
     Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&RC1&""]"")"
     Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&$A23&""]"")"

     Else
     
   
End If


End Sub

*****However, neither solution will populate the formulas in H23 and H32 if 20 is typed into cell K2, but the formulas in H23 and H32 are populated if 10 is typed into cell K2.   Any ideas why?

Thank you,

Lindsay
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

MacroShadowCommented:
This works fine:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("K2")) Is Nothing Then
        Dim term As Integer
        term = Range("K2").Value
        Select Case term
            Case 10
                Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&RC1&""]"")"
                Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&$A23&""]"")"
            Case 20
                Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&RC1&""]"")"
                Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&$A23&""]"")"
        End Select
    End If
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MacroShadowCommented:
As does this (you were missing an End If):
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will
    ' update the record count for retained students according to the term specified.
    Set KeyCells = Range("K2")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        If KeyCells = 10 Then
            Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&RC1&""]"")"
            Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&$A23&""]"")"
        ElseIf KeyCells = 20 Then
            Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&RC1&""]"")"
            Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&$A23&""]"")"
        Else
        End If
    End If
End Sub

Open in new window

Lindsay_KAuthor Commented:
Perfect!  Thank you so much MacroShadow!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.