Lindsay_K
asked on
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].[Disti nct Count of ID_NUM]"",""[Table_TMSEPRD ].[cohort_ cde].&[""& R2C7&""]"" ,""[Table_ TMSEPRD].[ NR - FALL GROUP].&[""&RC1&""]"")"
Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti nct Count of ID_NUM]"",""[Table_TMSEPRD ].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[ Table_TMSE PRD].[COHO RT TERM GROUP].&[""&$J$2&""]"",""[ Table_TMSE PRD].[NR - FALL GROUP].&[""&$A23&""]"")"
ElseIf term = 20 Then
Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti nct Count of ID_NUM]"",""[Table_TMSEPRD ].[cohort_ cde].&[""& R2C7&""]"" ,""[Table_ TMSEPRD].[ NR - SPRING GROUP].&[""&RC1&""]"")"
Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti nct Count of ID_NUM]"",""[Table_TMSEPRD ].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[ Table_TMSE PRD].[COHO RT TERM GROUP].&[""&$J$2&""]"",""[ Table_TMSE PRD].[NR - SPRING GROUP].&[""&$A23&""]"")"
Else
End If
Many thanks,
Lindsay
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].[Disti
Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti
ElseIf term = 20 Then
Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti
Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti
Else
End If
Many thanks,
Lindsay
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
ASKER
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(KeyC ells, Range(Target.Address)) _
Is Nothing Then
If KeyCells = 10 Then
Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti nct Count of ID_NUM]"",""[Table_TMSEPRD ].[cohort_ cde].&[""& R2C7&""]"" ,""[Table_ TMSEPRD].[ NR - FALL GROUP].&[""&RC1&""]"")"
Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti nct Count of ID_NUM]"",""[Table_TMSEPRD ].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[ Table_TMSE PRD].[COHO RT TERM GROUP].&[""&$J$2&""]"",""[ Table_TMSE PRD].[NR - FALL GROUP].&[""&$A23&""]"")"
ElseIf KeyCells = 20 Then
Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti nct Count of ID_NUM]"",""[Table_TMSEPRD ].[cohort_ cde].&[""& R2C7&""]"" ,""[Table_ TMSEPRD].[ NR - SPRING GROUP].&[""&RC1&""]"")"
Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti nct Count of ID_NUM]"",""[Table_TMSEPRD ].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[ Table_TMSE PRD].[COHO RT TERM GROUP].&[""&$J$2&""]"",""[ Table_TMSE PRD].[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
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(KeyC
Is Nothing Then
If KeyCells = 10 Then
Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti
Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti
ElseIf KeyCells = 20 Then
Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti
Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Disti
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
Perfect! Thank you so much MacroShadow!
Private Sub Worksheet_Change(ByVal Target As Range)
...
Find the details in MS KB article:
https://support.microsoft.com/en-us/kb/213612