| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set up a check that matches names of companies in excel? | Excel Worksheet Functions | |||
| Can I check names in one list agains names in another in excel? | Excel Discussion (Misc queries) | |||
| check for duplicate names in spreadsheet | Excel Worksheet Functions | |||
| Referencing check box names in VBE | Excel Programming | |||
| range names or check box | Excel Worksheet Functions | |||
Hybrid Mode
