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