mercredi 29 avril 2015

VBA multiple checkboxes to control multiple pivot tables

again I need little help which I will greatly appreciate.

Basically, on my dashboard page I have couple of checkboxes that control numerous of pivot tables in the background.

I have checkboxes that are called "definite", "tentative", "pending,", ... and also corresponds to values in pivot fields.

and I have numerous of pivot tables called: "Hidden_1" or "Hidden_2" in different sheets but all with the same structure.

My idea was that If someone checked "definite", it will be selected in all pivot pivot tables in fields called "Status". If someone "unchecked" this checkbox, the pivots will react.

To do so I used a code that I create before and it was working well:

    Sub checkbox1()
Application.ScreenUpdating = False
On Error Resume Next  
Dim pt As PivotTable, wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
    For Each pt In wks.PivotTables
        With pt
            If .Name = "Hidden_1" Or .Name = "Hidden_2" Then
                .PivotFields("Status").CurrentPage = "definite"
            End If
        End With
    Next pt
Next wks 
Application.ScreenUpdating = True 
End Sub

However, this code selects only one value, so I can't have selected both "definite" and "pending" if someone checked those boxes. Right now all checkboxes has a separate code assigned where only .CurrentPage = "checkboxname" was changed..

I have two questions:

1) what is the best way to select multiple values. E.g. if checked boxes "definite" and "pending" are checked, pivot tables should have selected two values "definite" and "pending" selected in the "Status" field

2) what is the best way to "dis-select" the value? Right now, my procedure checkbox1 is running everytime that the checkbox is clicked. And I want it to run only when I am "checking" it. Right now I am trying to link the checkbox with cell, e.g. "definite" has H10, so my code starts with the line:

If Range("H10").Value = True Then
'code to select the value in "Status" field
Else
'code to unselect the value in "Status" field
End If

I should also noted that I couldn't use ActiveX Checkbox because I had error: "cannot insert object" and I used form controls. I read that this error is somehow connected with a patch that I have installed.

Thank you all for your help, Matt




Aucun commentaire:

Enregistrer un commentaire