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
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" <Sharon@discussions.microsoft.com> wrote in message
news:7608EF33-5852-491D-9F16-5B6C83224833@microsoft.com...
> 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
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" <Sharon@discussions.microsoft.com> wrote in message
> news:7608EF33-5852-491D-9F16-5B6C83224833@microsoft.com...
> > 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
>
>
>
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" <Sharon@discussions.microsoft.com> wrote in message
news:CF345DC7-148E-413D-BD8E-C2D990315BEE@microsoft.com...
> 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" <Sharon@discussions.microsoft.com> wrote in message
> > news:7608EF33-5852-491D-9F16-5B6C83224833@microsoft.com...
> > > 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
> >
> >
> >
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" <Sharon@discussions.microsoft.com> wrote in message
> news:CF345DC7-148E-413D-BD8E-C2D990315BEE@microsoft.com...
> > 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" <Sharon@discussions.microsoft.com> wrote in message
> > > news:7608EF33-5852-491D-9F16-5B6C83224833@microsoft.com...
> > > > 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
> > >
> > >
> > >
>
>
>
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" <Sharon@discussions.microsoft.com> wrote in message
news:C9F4DD69-6FBE-43D0-BFCC-C7ACF38E51D3@microsoft.com...
> 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" <Sharon@discussions.microsoft.com> wrote in message
> > news:CF345DC7-148E-413D-BD8E-C2D990315BEE@microsoft.com...
> > > 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" <Sharon@discussions.microsoft.com> wrote in message
> > > > news:7608EF33-5852-491D-9F16-5B6C83224833@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks