Run macro on cell change - based on it´s value

[alexbat]

Hi,

want to check if a cell changes, and based on it´s new value perform an action.

Tried with

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Target.Worksheet.Range("D1")) Is Nothing Then
  
    If Range("D1").Value = "1" Then
    
    Range("B4").Select
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Global Ultimate").AutoSort _
       xlAscending, "Sum of NFR Total", ActiveSheet.PivotTables("PivotTable1"). _
      PivotColumnAxis.PivotLines(1), 1
    
    ElseIf Range("B4").Value = "2" Then
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Global Ultimate").AutoSort _
       xlDescending, "Sum of NFR Total", ActiveSheet.PivotTables("PivotTable1"). _
      PivotColumnAxis.PivotLines(1), 1
    
    End If
End Sub

but nothing happens.

Any ideas?

 

[Dustins]

hey bud, I didnt really change much, but i made it to where when range D1 = anything it runs the macro and left b4 alone, you forgot an end if at the end of your statement.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("D1")) Is Nothing Then

If Range("D1").Value <> "" Then

Range("B4").Select

ActiveSheet.PivotTables("PivotTable1").PivotFields("Global Ultimate").AutoSort _
xlAscending, "Sum of NFR Total", ActiveSheet.PivotTables("PivotTable1"). _
PivotColumnAxis.PivotLines(1), 1

ElseIf Range("B4").Value = "2" Then

ActiveSheet.PivotTables("PivotTable1").PivotFields("Global Ultimate").AutoSort _
xlDescending, "Sum of NFR Total", ActiveSheet.PivotTables("PivotTable1"). _
PivotColumnAxis.PivotLines(1), 1

End If
End If

End Sub

make sure you put this macro on the sheet you are wanting those cells to run the macro on.

 

[jim may]

Change line 2 to the below - Does that help?

If Not Intersect(Target, Range("D1")) Is Nothing Then

 

[alexbat]

Sorry, code messed up in previous post, should be

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Target.Worksheet.Range("D1")) Is Nothing Then
  
    If Range("D1").Value = "1" Then
    
    Range("B4").Select
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Global Ultimate").AutoSort _
       xlAscending, "Sum of NFR Total", ActiveSheet.PivotTables("PivotTable1"). _
      PivotColumnAxis.PivotLines(1), 1
    
    ElseIf Range("D1").Value = "2" Then
    
    Range("B4").Select
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Global Ultimate").AutoSort _
       xlDescending, "Sum of NFR Total", ActiveSheet.PivotTables("PivotTable1"). _
      PivotColumnAxis.PivotLines(1), 1
    
    End If
End Sub

 

[Joe4]

Note this line:

If Range("D1").Value = "1" Then

Since you enclosed the 1 in quotes, it is checking for a string value of 1, not a numeric value of 1 (1 does not equal "1").
So if you are actually checking for a numeric value of 1, drop the double-quotes that surround it.

Same thing with checking for 2 in B4.

 

[alexbat]

Corrected the code according to your suggestions guys, but still nothing happening to, in this case, the Pivot table.

The code looks like this currently:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Target.Worksheet.Range("D1")) Is Nothing Then
  
    If Range("D1").Value = 1 Then
    
    Range("B4").Select
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Global Ultimate").AutoSort _
       xlAscending, "Sum of NFR Total", ActiveSheet.PivotTables("PivotTable1"). _
      PivotColumnAxis.PivotLines(1), 1
    
    ElseIf Range("D1").Value = 2 Then
    
    Range("B4").Select
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Global Ultimate").AutoSort _
       xlDescending, "Sum of NFR Total", ActiveSheet.PivotTables("PivotTable1"). _
      PivotColumnAxis.PivotLines(1), 1
    
    End If
    End If
End Sub

 

[Joe4]

How is the value in D1 changing?
Is someone physically changing it, or is it a formula. If it is a formula, Worksheet_Change won't catch changes in values brought about by formulas.
You would probably need to use the Worksheet_Calculate event instead.

 

[alexbat]

Joe4,

D1 is changed by a Option Button form control. I have 2 options, "Top 20" and "Bottom 20", and I want to sort the Pivot based on
the choice done....

 

[Dustins]

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Target.Worksheet.Range("D1")) Is Nothing Then

If Range("D1").Value = "Top 20" Then
Range("B4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Global Ultimate").AutoSort xlAscending, "Sum of NFR Total", ActiveSheet.PivotTables("PivotTable1").PivotColumnAxis.PivotLines(1), 1

ElseIf Range("D1").Value = "Bottom 20" Then

Range("B4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Global Ultimate").AutoSort xlDescending, "Sum of NFR Total", ActiveSheet.PivotTables("PivotTable1").PivotColumnAxis.PivotLines(1), 1

End If
End If
End Sub

 

[alexbat]

Dustins... Sorry for being unclear, the "Top 20" and "Bottom 20" are just button descriptions, they render 1 or 2 when chosen...