Hi Bob
Here is the code that is being used to filter the pivots from the check
boxes. But I have a second set of check boxes that allow the users to filter
on a second pivot field. I don't know how to tell the vba how to distinguish
between the two sets of check boxes!
Thanks a lot
Sharon
Public Sub CommandButton1_Click() ' Non-OLAP Country Filter
Dim cbx As CheckBox
Dim ws As Worksheet
Dim ctrl As Control
Dim obj As Object
Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As PivotField
Dim pf3, pf4 As PivotField
Dim cf As CubeField
Dim aState$()
Application.ScreenUpdating = True
' Loop through all of the CheckBox Objects on Selector
a& = 0 ' initialise counter
For Each obj In ActiveSheet.OLEObjects
' If the Checkbox was selected then ...
If TypeOf obj.Object Is MSForms.CheckBox And obj.Object.Value = True Then
'MsgBox obj.Object.Name
' Increment a counter
a& = a& + 1
' Expand the aState$ dynamic array to add another element
ReDim Preserve aState$(1 To a&)
' Initialize the new element of the array with the caption of the
selected CheckBox
aState$(a&) = obj.Object.Caption
End If
Next obj
If a& 0 Then ' If there were any CheckBoxes selected, then ..
' Loop through the elements in the aState$ dynamic array
For Each ws In ActiveWorkbook.Worksheets ' go through the worksheets
' MsgBox ws.Name - to show me where it falls over if it does
If ws.PivotTables.Count 0 Then 'if there are pivots on
the sheet
For Each pt In ws.PivotTables
If pt.PivotCache.OLAP = False Then ' if its a non-OLAP
pivot
Set pf = pt.PivotFields("Customer Country") 'select
customer country
pf.AutoSort xlManual, "Customer Country"
If a& = 1 Then ' If only one
item selected
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
pf.CurrentPage = aState$(a&)
End If
If a& 1 Then ' If
more than one selection
For Each pi In pf.PivotItems
ShowMe = False
For j = LBound(aState$) To UBound(aState$)
If pi.Value = aState$(j) Then
ShowMe = True
End If
Next j
pi.Visible = ShowMe
Next pi
pf.CurrentPage = "(All)"
End If
End If
Next pt
End If
Next ws
End If
'ErrorHandler:
' MsgBox ws.Name & " - " & pt.Name
Call FilterServiceCluster
--
Sharon
"Bob Phillips" wrote:
Can you post the code that you have so far so we get a better picture of
what you are doing?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Sharon" wrote in message
...
Hi All - after having brilliant help last week re check boxes I have one
more question. I need to distinguish between two different sets of check
boxes (MSForms objects) on a worksheet. But for some reason vb code won't
recognise a check box name property e.g. obj.Object.Name in the same way
it
does for the value, e.g. obj.Object.value? If anyone has any ideas I
would
be really grateful, as always!! Thank you all so much for your help thus
far. You are making me look good!
--
Sharon