Check Box names
 

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

Check Box names
 

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

Check Box names
 

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

Check Box names
 

Ah I see your problem now.

What I suggest doing is to select one group of CBs and set the Group
property to say "Group 1", then assign "Group 2" to the other group. You can
then test that property

For Each obj In ActiveSheet.OLEObjects

' If the Checkbox was selected then ...

If TypeOf obj.Object Is MSForms.CheckBox And _
obj.Object.GroupName = "Group1" And _
obj.Object.Value = True Then
MsgBox obj.Name
End If
Next obj


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
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

Check Box names
 

Hi Bob

Tried the GroupName but it assumes exclusive option buttons rather than
check boxes and the users have to be able to make multiple selections! I got
an error message "Object doesn't support this property or method". Thanks
for coming back so quickly though.
--
Sharon


"Bob Phillips" wrote:

Ah I see your problem now.

What I suggest doing is to select one group of CBs and set the Group
property to say "Group 1", then assign "Group 2" to the other group. You can
then test that property

For Each obj In ActiveSheet.OLEObjects

' If the Checkbox was selected then ...

If TypeOf obj.Object Is MSForms.CheckBox And _
obj.Object.GroupName = "Group1" And _
obj.Object.Value = True Then
MsgBox obj.Name
End If
Next obj


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
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

Check Box names
 

I just tried it Sharon before posting and it worked fine for me. Where did
you get that message, in the code for testing, or when setting it up?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
Hi Bob

Tried the GroupName but it assumes exclusive option buttons rather than
check boxes and the users have to be able to make multiple selections! I
got
an error message "Object doesn't support this property or method". Thanks
for coming back so quickly though.
--
Sharon


"Bob Phillips" wrote:

Ah I see your problem now.

What I suggest doing is to select one group of CBs and set the Group
property to say "Group 1", then assign "Group 2" to the other group. You
can
then test that property

For Each obj In ActiveSheet.OLEObjects

' If the Checkbox was selected then ...

If TypeOf obj.Object Is MSForms.CheckBox And _
obj.Object.GroupName = "Group1" And _
obj.Object.Value = True Then
MsgBox obj.Name
End If
Next obj


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
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

Check Box names
 

Hi Bob

I added the extra bit of code and ran it but it gives me the "Object doesn't
support this ..." message. I get that message if refer to anything other
than the obj.Object.Value. However I have found a way around the problem by
making the users run the selections separately and having the individual
modules clear the selectors as they finish.

I have a huge favour to ask you though - I have to filter a series of OLAP
pivots based on the same array and I just can't get it to work. Code I am
trying below:

If aState$(a&) = "(All)" Then
pf3.CurrentPageList = Array("[Customer
Country].[All Customer Country]")
End If
If a& 1 Then
For j = LBound(aState$) To UBound(aState$)
For Each pi In pf3.PivotItems
pf3.CurrentPageList =
Array("[Customer Country].[All Customer Country]." & aState$(a&))
Next pi
Next j
End If

Bob, if you are just too busy to answer I'll completely understand!!

Thanks
--
Sharon


"Bob Phillips" wrote:

I just tried it Sharon before posting and it worked fine for me. Where did
you get that message, in the code for testing, or when setting it up?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
Hi Bob

Tried the GroupName but it assumes exclusive option buttons rather than
check boxes and the users have to be able to make multiple selections! I
got
an error message "Object doesn't support this property or method". Thanks
for coming back so quickly though.
--
Sharon


"Bob Phillips" wrote:

Ah I see your problem now.

What I suggest doing is to select one group of CBs and set the Group
property to say "Group 1", then assign "Group 2" to the other group. You
can
then test that property

For Each obj In ActiveSheet.OLEObjects

' If the Checkbox was selected then ...

If TypeOf obj.Object Is MSForms.CheckBox And _
obj.Object.GroupName = "Group1" And _
obj.Object.Value = True Then
MsgBox obj.Name
End If
Next obj


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
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

Check Box names
 

Sorry, I don't work with OLAP pivots, so I don't think I can be of any use.
I suggest you start a new thread with the new problem, bring it to the other
guys' attention.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
Hi Bob

I added the extra bit of code and ran it but it gives me the "Object
doesn't
support this ..." message. I get that message if refer to anything other
than the obj.Object.Value. However I have found a way around the problem
by
making the users run the selections separately and having the individual
modules clear the selectors as they finish.

I have a huge favour to ask you though - I have to filter a series of OLAP
pivots based on the same array and I just can't get it to work. Code I am
trying below:

If aState$(a&) = "(All)" Then
pf3.CurrentPageList = Array("[Customer
Country].[All Customer Country]")
End If
If a& 1 Then
For j = LBound(aState$) To
UBound(aState$)
For Each pi In pf3.PivotItems
pf3.CurrentPageList =
Array("[Customer Country].[All Customer Country]." & aState$(a&))
Next pi
Next j
End If

Bob, if you are just too busy to answer I'll completely understand!!

Thanks
--
Sharon


"Bob Phillips" wrote:

I just tried it Sharon before posting and it worked fine for me. Where
did
you get that message, in the code for testing, or when setting it up?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
Hi Bob

Tried the GroupName but it assumes exclusive option buttons rather
than
check boxes and the users have to be able to make multiple selections!
I
got
an error message "Object doesn't support this property or method".
Thanks
for coming back so quickly though.
--
Sharon


"Bob Phillips" wrote:

Ah I see your problem now.

What I suggest doing is to select one group of CBs and set the Group
property to say "Group 1", then assign "Group 2" to the other group.
You
can
then test that property

For Each obj In ActiveSheet.OLEObjects

' If the Checkbox was selected then ...

If TypeOf obj.Object Is MSForms.CheckBox And _
obj.Object.GroupName = "Group1" And _
obj.Object.Value = True Then
MsgBox obj.Name
End If
Next obj


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
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

Check Box names
 

Bob - Thanks so much for all of your suggestions. Will take your advice.

Regards
Sharon
--
Sharon


"Bob Phillips" wrote:

Sorry, I don't work with OLAP pivots, so I don't think I can be of any use.
I suggest you start a new thread with the new problem, bring it to the other
guys' attention.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
Hi Bob

I added the extra bit of code and ran it but it gives me the "Object
doesn't
support this ..." message. I get that message if refer to anything other
than the obj.Object.Value. However I have found a way around the problem
by
making the users run the selections separately and having the individual
modules clear the selectors as they finish.

I have a huge favour to ask you though - I have to filter a series of OLAP
pivots based on the same array and I just can't get it to work. Code I am
trying below:

If aState$(a&) = "(All)" Then
pf3.CurrentPageList = Array("[Customer
Country].[All Customer Country]")
End If
If a& 1 Then
For j = LBound(aState$) To
UBound(aState$)
For Each pi In pf3.PivotItems
pf3.CurrentPageList =
Array("[Customer Country].[All Customer Country]." & aState$(a&))
Next pi
Next j
End If

Bob, if you are just too busy to answer I'll completely understand!!

Thanks
--
Sharon


"Bob Phillips" wrote:

I just tried it Sharon before posting and it worked fine for me. Where
did
you get that message, in the code for testing, or when setting it up?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
Hi Bob

Tried the GroupName but it assumes exclusive option buttons rather
than
check boxes and the users have to be able to make multiple selections!
I
got
an error message "Object doesn't support this property or method".
Thanks
for coming back so quickly though.
--
Sharon


"Bob Phillips" wrote:

Ah I see your problem now.

What I suggest doing is to select one group of CBs and set the Group
property to say "Group 1", then assign "Group 2" to the other group.
You
can
then test that property

For Each obj In ActiveSheet.OLEObjects

' If the Checkbox was selected then ...

If TypeOf obj.Object Is MSForms.CheckBox And _
obj.Object.GroupName = "Group1" And _
obj.Object.Value = True Then
MsgBox obj.Name
End If
Next obj


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sharon" wrote in message
...
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