combining 2) MACROs so CHECKBOX reverses operation.

[ectiger86]

I have been reading and trying and trying and reading and searching and I cant figure it out. I'm no coder, obv.

I made a checkbox form that when initially is checked, via macro it filters a pivot table and pivot chart.

I made a macro to undo this procedure once the box is unchecked but it can only run on its own, meaning i can not figure out how to combine the two macros to operate on this checkbox.

any help would be very much appreciated.

Sub CLICK4_TOP10on()'
' CLICK4_TOP10on Macro
'


'
    Range("G13").Select
    ActiveSheet.PivotTables("PivotTable5").PivotFields("CUSTOMER").PivotFilters. _
        Add2 Type:=xlTopCount, DataField:=ActiveSheet.PivotTables("PivotTable5"). _
        PivotFields("Sum of Amnt"), Value1:=10
End Sub
Sub CLICK4_TOP10off()
'
' CLICK4_TOP10off Macro
'


'
    ActiveSheet.PivotTables("PivotTable5").PivotFields("CUSTOMER").ClearAllFilters
End Sub

 

[Jerry Sullivan]

Here's a framework to get you started.

Copy this code into a Standard Code Module (like "Module1" not a Sheet Code Module).
Assign the macro to your Form Control Checkbox

Public Sub TogglePivotFilters()
'--assign this macro to a form control checkbox

 Dim vCaller As Variant

 vCaller = Application.Caller
 If TypeName(vCaller) = "String" Then

   If ActiveSheet.Shapes(CStr(vCaller)).ControlFormat.Value = xlOn Then
      '--add your code to filter pivot here
      '....
      '....
      MsgBox "Applying filters..." '--message for test only
   Else
      '--add your code to clear pivot filters here
      '....
      '....
      MsgBox "Clearing filters..." '--message for test only
   End If
 End If

End Sub

 

[ectiger86]

I appreciate you help thus far When I added my code the initial click on the check box worked to filter the table. When the checkbox was unchecked your msgbox saying clearing filters appeared but it didnt actually clear any filters.

Here is the code as it is now. I may have inserted it into your code incorrectly.

Public Sub TogglePivotFilters()
'--assign this macro to a form control checkbox

 Dim vCaller As Variant

 vCaller = Application.Caller
 If TypeName(vCaller) = "String" Then

   If ActiveSheet.Shapes(CStr(vCaller)).ControlFormat.Value = xlOn Then
      '--Range("G13").Select
    ActiveSheet.PivotTables("PivotTable5").PivotFields("CUSTOMER").PivotFilters. _
        Add2 Type:=xlTopCount, DataField:=ActiveSheet.PivotTables("PivotTable5"). _
        PivotFields("Sum of Amnt"), Value1:=10
      '....
      '....
      MsgBox "Applying filters..." '--message for test only
   Else
      '--ActiveSheet.PivotTables("PivotTable5").PivotFields("CUSTOMER").ClearAllFilters
      '....
      '....
      MsgBox "Clearing filters..." '--message for test only
   End If
 End If

End Sub

 

[Jerry Sullivan]

Remove the single quotes at the beginning of the lines of code to be executed. Your clear filter code is not being executed.

 

[ectiger86]

You are a BEAUTIFUL man! Thank YOU!

Im not sure if I want to yet, but In order to get rid of the MsgBox's i just need to delete those two lines from the code, Correct?

 

[Stiuart_W]

You are a BEAUTIFUL man! Thank YOU!

Im not sure if I want to yet, but In order to get rid of the MsgBox's i just need to delete those two lines from the code, Correct?

Yep!!!!!!