Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
Please help, I have designed a simple spreadsheet that
uses Check Boxes and Option Buttons from the Control Toolbox and I am trying to make a small VBA routine to clear them all. I thought I could use: Sub Clear_cb_ob() Dim cb As CheckBox Dim ob As OptionButton For Each cb In Sheet1 cb.Value = False Next For Each ob In Sheet1 ob.Clear Next End Sub But I just get: Object doesn't support this property or method |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
Sub ClearCheckboxes()
Dim i As Long For i = 1 To ActiveSheet.OLEObjects.Count If TypeName(ActiveSheet.OLEObjects(i).Object) = "CheckBox" Then ActiveSheet.OLEObjects(i).Object.Value = False End If Next i For i = 1 To ActiveSheet.OLEObjects.Count If TypeName(ActiveSheet.OLEObjects(i).Object) = "OptionButton" Then ActiveSheet.OLEObjects(i).Object.Value = False End If Next i End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "newboy18" wrote in message ... Please help, I have designed a simple spreadsheet that uses Check Boxes and Option Buttons from the Control Toolbox and I am trying to make a small VBA routine to clear them all. I thought I could use: Sub Clear_cb_ob() Dim cb As CheckBox Dim ob As OptionButton For Each cb In Sheet1 cb.Value = False Next For Each ob In Sheet1 ob.Clear Next End Sub But I just get: Object doesn't support this property or method |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
Thanks Bob but your routine did not work, after I played
a bit I realised that it was my fault, I made a mistake in my question. The controls I used were created using the Forms Toolbar not the Control Toolbox Could you please help again. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
Sub ClearControls()
Dim chk As CheckBox Dim opt As OptionButton For Each chk In ActiveSheet.Checkboxes chk.Value = False Next chk For Each opt In ActiveSheet.OptionButtons opt.Value = False Next opt End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "newboy18" wrote in message ... Thanks Bob but your routine did not work, after I played a bit I realised that it was my fault, I made a mistake in my question. The controls I used were created using the Forms Toolbar not the Control Toolbox Could you please help again. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
Thanks very much, I had been stuck on this for the past
couple of days |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
I have another question.
Everything was working but now I need to group the optionboxes together into several groups. I tried to ungroup everything, clear all the optionboxes and then regroup everthing but it wont work. Dim grp As ShapeRange For Each grp In ActiveSheet.ShapeRange grp.Select Selection.ShapeRange.Ungroup.Select Next grp |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
why would you want to group forms controls. Why would you want to group
anything using code? grouping this way is not the way to make forms controls operate as a group. You use a group box around them to do that. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I have another question. Everything was working but now I need to group the optionboxes together into several groups. I tried to ungroup everything, clear all the optionboxes and then regroup everthing but it wont work. Dim grp As ShapeRange For Each grp In ActiveSheet.ShapeRange grp.Select Selection.ShapeRange.Ungroup.Select Next grp |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
That is what I did, create 5 option boxes and group them
using a group box, then create another 5 option boxes and group them using another group box and so on. Each 5 option boxes is used for a different topic. My problem is that I wanted an option that would clear them all, kind of a reset. -----Original Message----- why would you want to group forms controls. Why would you want to group anything using code? grouping this way is not the way to make forms controls operate as a group. You use a group box around them to do that. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I have another question. Everything was working but now I need to group the optionboxes together into several groups. I tried to ungroup everything, clear all the optionboxes and then regroup everthing but it wont work. Dim grp As ShapeRange For Each grp In ActiveSheet.ShapeRange grp.Select Selection.ShapeRange.Ungroup.Select Next grp . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
Sub Tester2()
For Each op In ActiveSheet.OptionButtons op.Value = xlOff Next End Sub works fine, pretty much the same as what Bob told you before. -- Regards, Tom Ogilvy "newboy18" wrote in message ... That is what I did, create 5 option boxes and group them using a group box, then create another 5 option boxes and group them using another group box and so on. Each 5 option boxes is used for a different topic. My problem is that I wanted an option that would clear them all, kind of a reset. -----Original Message----- why would you want to group forms controls. Why would you want to group anything using code? grouping this way is not the way to make forms controls operate as a group. You use a group box around them to do that. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I have another question. Everything was working but now I need to group the optionboxes together into several groups. I tried to ungroup everything, clear all the optionboxes and then regroup everthing but it wont work. Dim grp As ShapeRange For Each grp In ActiveSheet.ShapeRange grp.Select Selection.ShapeRange.Ungroup.Select Next grp . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
No, if I set a counter going I can see it is going round
the loop a few times, then it gives the error: Run-time error 1004 Unable to set the Value property of the OptionButton class. I only had 5 buttons that were grouped by 1 group box. If I ungroup the option buttons it works OK. So I guess I need 3 loops 1 - will ungroup everthing 2 - will clear everything 3 - re-group everything. -----Original Message----- Sub Tester2() For Each op In ActiveSheet.OptionButtons op.Value = xlOff Next End Sub works fine, pretty much the same as what Bob told you before. -- Regards, Tom Ogilvy "newboy18" wrote in message ... That is what I did, create 5 option boxes and group them using a group box, then create another 5 option boxes and group them using another group box and so on. Each 5 option boxes is used for a different topic. My problem is that I wanted an option that would clear them all, kind of a reset. -----Original Message----- why would you want to group forms controls. Why would you want to group anything using code? grouping this way is not the way to make forms controls operate as a group. You use a group box around them to do that. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I have another question. Everything was working but now I need to group the optionboxes together into several groups. I tried to ungroup everything, clear all the optionboxes and then regroup everthing but it wont work. Dim grp As ShapeRange For Each grp In ActiveSheet.ShapeRange grp.Select Selection.ShapeRange.Ungroup.Select Next grp . . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
Again, there is no reason to group anything. stop grouping things. It isn't
required and does nothing for your functionality. Ungroup things and leave them alone. -- Regards, Tom Ogilvy "newboy18" wrote in message ... No, if I set a counter going I can see it is going round the loop a few times, then it gives the error: Run-time error 1004 Unable to set the Value property of the OptionButton class. I only had 5 buttons that were grouped by 1 group box. If I ungroup the option buttons it works OK. So I guess I need 3 loops 1 - will ungroup everthing 2 - will clear everything 3 - re-group everything. -----Original Message----- Sub Tester2() For Each op In ActiveSheet.OptionButtons op.Value = xlOff Next End Sub works fine, pretty much the same as what Bob told you before. -- Regards, Tom Ogilvy "newboy18" wrote in message ... That is what I did, create 5 option boxes and group them using a group box, then create another 5 option boxes and group them using another group box and so on. Each 5 option boxes is used for a different topic. My problem is that I wanted an option that would clear them all, kind of a reset. -----Original Message----- why would you want to group forms controls. Why would you want to group anything using code? grouping this way is not the way to make forms controls operate as a group. You use a group box around them to do that. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I have another question. Everything was working but now I need to group the optionboxes together into several groups. I tried to ungroup everything, clear all the optionboxes and then regroup everthing but it wont work. Dim grp As ShapeRange For Each grp In ActiveSheet.ShapeRange grp.Select Selection.ShapeRange.Ungroup.Select Next grp . . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
How can I group option boxes together if I don't group
them? What I have is a questionare that asks users to select 1 of 5 different answers from several different questions. So I want the each 5 option boxes to be grouped together. -----Original Message----- Again, there is no reason to group anything. stop grouping things. It isn't required and does nothing for your functionality. Ungroup things and leave them alone. -- Regards, Tom Ogilvy "newboy18" wrote in message ... No, if I set a counter going I can see it is going round the loop a few times, then it gives the error: Run-time error 1004 Unable to set the Value property of the OptionButton class. I only had 5 buttons that were grouped by 1 group box. If I ungroup the option buttons it works OK. So I guess I need 3 loops 1 - will ungroup everthing 2 - will clear everything 3 - re-group everything. -----Original Message----- Sub Tester2() For Each op In ActiveSheet.OptionButtons op.Value = xlOff Next End Sub works fine, pretty much the same as what Bob told you before. -- Regards, Tom Ogilvy "newboy18" wrote in message ... That is what I did, create 5 option boxes and group them using a group box, then create another 5 option boxes and group them using another group box and so on. Each 5 option boxes is used for a different topic. My problem is that I wanted an option that would clear them all, kind of a reset. -----Original Message----- why would you want to group forms controls. Why would you want to group anything using code? grouping this way is not the way to make forms controls operate as a group. You use a group box around them to do that. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I have another question. Everything was working but now I need to group the optionboxes together into several groups. I tried to ungroup everything, clear all the optionboxes and then regroup everthing but it wont work. Dim grp As ShapeRange For Each grp In ActiveSheet.ShapeRange grp.Select Selection.ShapeRange.Ungroup.Select Next grp . . . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
You group option buttons by placing a group box around them. that is it.
You don't use code, you don't select them, do right click and group. You don't use the group object. Physically having the group box around them groups them for the purpose you require. If you only do that, then the code works fine. -- Regards, Tom Ogilvy "newboy18" wrote in message ... How can I group option boxes together if I don't group them? What I have is a questionare that asks users to select 1 of 5 different answers from several different questions. So I want the each 5 option boxes to be grouped together. -----Original Message----- Again, there is no reason to group anything. stop grouping things. It isn't required and does nothing for your functionality. Ungroup things and leave them alone. -- Regards, Tom Ogilvy "newboy18" wrote in message ... No, if I set a counter going I can see it is going round the loop a few times, then it gives the error: Run-time error 1004 Unable to set the Value property of the OptionButton class. I only had 5 buttons that were grouped by 1 group box. If I ungroup the option buttons it works OK. So I guess I need 3 loops 1 - will ungroup everthing 2 - will clear everything 3 - re-group everything. -----Original Message----- Sub Tester2() For Each op In ActiveSheet.OptionButtons op.Value = xlOff Next End Sub works fine, pretty much the same as what Bob told you before. -- Regards, Tom Ogilvy "newboy18" wrote in message ... That is what I did, create 5 option boxes and group them using a group box, then create another 5 option boxes and group them using another group box and so on. Each 5 option boxes is used for a different topic. My problem is that I wanted an option that would clear them all, kind of a reset. -----Original Message----- why would you want to group forms controls. Why would you want to group anything using code? grouping this way is not the way to make forms controls operate as a group. You use a group box around them to do that. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I have another question. Everything was working but now I need to group the optionboxes together into several groups. I tried to ungroup everything, clear all the optionboxes and then regroup everthing but it wont work. Dim grp As ShapeRange For Each grp In ActiveSheet.ShapeRange grp.Select Selection.ShapeRange.Ungroup.Select Next grp . . . |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
I am confused, I agree with what you are saying.
I created 5 option buttons and then grouped them with a Group Box and then I created 5 more option buttons and so on. What I want is a routine that will clear all of the option buttons, a reset function, but it won't work because the option buttons are grouped, if I ungroup them manualy then the routine works. -----Original Message----- You group option buttons by placing a group box around them. that is it. You don't use code, you don't select them, do right click and group. You don't use the group object. Physically having the group box around them groups them for the purpose you require. If you only do that, then the code works fine. -- Regards, Tom Ogilvy "newboy18" wrote in message ... How can I group option boxes together if I don't group them? What I have is a questionare that asks users to select 1 of 5 different answers from several different questions. So I want the each 5 option boxes to be grouped together. -----Original Message----- Again, there is no reason to group anything. stop grouping things. It isn't required and does nothing for your functionality. Ungroup things and leave them alone. -- Regards, Tom Ogilvy "newboy18" wrote in message ... No, if I set a counter going I can see it is going round the loop a few times, then it gives the error: Run-time error 1004 Unable to set the Value property of the OptionButton class. I only had 5 buttons that were grouped by 1 group box. If I ungroup the option buttons it works OK. So I guess I need 3 loops 1 - will ungroup everthing 2 - will clear everything 3 - re-group everything. -----Original Message----- Sub Tester2() For Each op In ActiveSheet.OptionButtons op.Value = xlOff Next End Sub works fine, pretty much the same as what Bob told you before. -- Regards, Tom Ogilvy "newboy18" wrote in message ... That is what I did, create 5 option boxes and group them using a group box, then create another 5 option boxes and group them using another group box and so on. Each 5 option boxes is used for a different topic. My problem is that I wanted an option that would clear them all, kind of a reset. -----Original Message----- why would you want to group forms controls. Why would you want to group anything using code? grouping this way is not the way to make forms controls operate as a group. You use a group box around them to do that. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I have another question. Everything was working but now I need to group the optionboxes together into several groups. I tried to ungroup everything, clear all the optionboxes and then regroup everthing but it wont work. Dim grp As ShapeRange For Each grp In ActiveSheet.ShapeRange grp.Select Selection.ShapeRange.Ungroup.Select Next grp . . . . |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
I did the same. An a New worksheet
Put 5 option buttons on the worksheet. Put a group box around them Linked the first to a cell (all are now linked) Put 5 option buttons on a worksheet Put a group box around them Linked the first to a cell (all are now linked) select an option button in each ran the code I provided. It worked with no problem. All option buttons were cleared (and linked cells showed zero). all controls from the forms toolbar. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I am confused, I agree with what you are saying. I created 5 option buttons and then grouped them with a Group Box and then I created 5 more option buttons and so on. What I want is a routine that will clear all of the option buttons, a reset function, but it won't work because the option buttons are grouped, if I ungroup them manualy then the routine works. -----Original Message----- You group option buttons by placing a group box around them. that is it. You don't use code, you don't select them, do right click and group. You don't use the group object. Physically having the group box around them groups them for the purpose you require. If you only do that, then the code works fine. -- Regards, Tom Ogilvy "newboy18" wrote in message ... How can I group option boxes together if I don't group them? What I have is a questionare that asks users to select 1 of 5 different answers from several different questions. So I want the each 5 option boxes to be grouped together. -----Original Message----- Again, there is no reason to group anything. stop grouping things. It isn't required and does nothing for your functionality. Ungroup things and leave them alone. -- Regards, Tom Ogilvy "newboy18" wrote in message ... No, if I set a counter going I can see it is going round the loop a few times, then it gives the error: Run-time error 1004 Unable to set the Value property of the OptionButton class. I only had 5 buttons that were grouped by 1 group box. If I ungroup the option buttons it works OK. So I guess I need 3 loops 1 - will ungroup everthing 2 - will clear everything 3 - re-group everything. -----Original Message----- Sub Tester2() For Each op In ActiveSheet.OptionButtons op.Value = xlOff Next End Sub works fine, pretty much the same as what Bob told you before. -- Regards, Tom Ogilvy "newboy18" wrote in message ... That is what I did, create 5 option boxes and group them using a group box, then create another 5 option boxes and group them using another group box and so on. Each 5 option boxes is used for a different topic. My problem is that I wanted an option that would clear them all, kind of a reset. -----Original Message----- why would you want to group forms controls. Why would you want to group anything using code? grouping this way is not the way to make forms controls operate as a group. You use a group box around them to do that. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I have another question. Everything was working but now I need to group the optionboxes together into several groups. I tried to ungroup everything, clear all the optionboxes and then regroup everthing but it wont work. Dim grp As ShapeRange For Each grp In ActiveSheet.ShapeRange grp.Select Selection.ShapeRange.Ungroup.Select Next grp . . . . |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
Are you sure you know what a groupbox is? It is another control on the
forms toolbar. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I am confused, I agree with what you are saying. I created 5 option buttons and then grouped them with a Group Box and then I created 5 more option buttons and so on. What I want is a routine that will clear all of the option buttons, a reset function, but it won't work because the option buttons are grouped, if I ungroup them manualy then the routine works. -----Original Message----- You group option buttons by placing a group box around them. that is it. You don't use code, you don't select them, do right click and group. You don't use the group object. Physically having the group box around them groups them for the purpose you require. If you only do that, then the code works fine. -- Regards, Tom Ogilvy "newboy18" wrote in message ... How can I group option boxes together if I don't group them? What I have is a questionare that asks users to select 1 of 5 different answers from several different questions. So I want the each 5 option boxes to be grouped together. -----Original Message----- Again, there is no reason to group anything. stop grouping things. It isn't required and does nothing for your functionality. Ungroup things and leave them alone. -- Regards, Tom Ogilvy "newboy18" wrote in message ... No, if I set a counter going I can see it is going round the loop a few times, then it gives the error: Run-time error 1004 Unable to set the Value property of the OptionButton class. I only had 5 buttons that were grouped by 1 group box. If I ungroup the option buttons it works OK. So I guess I need 3 loops 1 - will ungroup everthing 2 - will clear everything 3 - re-group everything. -----Original Message----- Sub Tester2() For Each op In ActiveSheet.OptionButtons op.Value = xlOff Next End Sub works fine, pretty much the same as what Bob told you before. -- Regards, Tom Ogilvy "newboy18" wrote in message ... That is what I did, create 5 option boxes and group them using a group box, then create another 5 option boxes and group them using another group box and so on. Each 5 option boxes is used for a different topic. My problem is that I wanted an option that would clear them all, kind of a reset. -----Original Message----- why would you want to group forms controls. Why would you want to group anything using code? grouping this way is not the way to make forms controls operate as a group. You use a group box around them to do that. -- Regards, Tom Ogilvy "newboy18" wrote in message ... I have another question. Everything was working but now I need to group the optionboxes together into several groups. I tried to ungroup everything, clear all the optionboxes and then regroup everthing but it wont work. Dim grp As ShapeRange For Each grp In ActiveSheet.ShapeRange grp.Select Selection.ShapeRange.Ungroup.Select Next grp . . . . |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
Yes, I think I have the same as you.
If I right click on one of the optionbuttons in the first group then the whole group is selected and I can select Format Object, (not what I want) If I Ungroup them first, then right click on a optionbutton, I can select just 1 of the optionbuttons, this time the short menu allows me to Format Control. From here I can select the Control tab and then Unselect. It is the groups that are stopping me from running the code. If I can unselect the groups in the VBA before clearing the optionbuttons then I am sure it will work -----Original Message----- Are you sure you know what a groupbox is? It is another control on the forms toolbar. |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
No, that is not how you group option buttons. You don't know what a
groupbox is. Your method is grouping shapes to form a composite shape. It is not the provided method for grouping optionbuttons - the groupbox control is provided for this purpose. A groupbox is the equivalent of a Frame on a userform. It is an actual square/rectangular shape that you put around the option buttons. this is how you properly group option buttons. It also provides a visible feedback to the user. You put you five optionbutton on the worksheet, then click the groupbox control in the forms toolbar, and rubberband the control around the 5 optionbuttons. the optionbuttons are not selected, they simply are contained within the border of the groupbox. Open a new workbook and put this macro in a general module. Then run it: Sub Macro2() Dim op1 As OptionButton, op2 As OptionButton Dim op3 As OptionButton, op4 As OptionButton Dim op5 As OptionButton Dim grpbx As GroupBox Set op1 = ActiveSheet. _ OptionButtons.Add(337, 27.75, 51.75, 17.25) Set op2 = ActiveSheet. _ OptionButtons.Add(337, 57.75, 57, 17.25) Set op3 = ActiveSheet. _ OptionButtons.Add(337.25, 90, 60.75, 17.25) Set op4 = ActiveSheet. _ OptionButtons.Add(337.75, 121.5, 65.25, 17.25) Set op5 = ActiveSheet. _ OptionButtons.Add(337, 156, 58.5, 27.75) Set grpbx = ActiveSheet. _ GroupBoxes.Add(297.75, 21, 182.25, 194.25) op1.LinkedCell = "$A$1" grpbx.Caption = "ABCD" Application.ScreenUpdating = False Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "newboy18" wrote in message ... Yes, I think I have the same as you. If I right click on one of the optionbuttons in the first group then the whole group is selected and I can select Format Object, (not what I want) If I Ungroup them first, then right click on a optionbutton, I can select just 1 of the optionbuttons, this time the short menu allows me to Format Control. From here I can select the Control tab and then Unselect. It is the groups that are stopping me from running the code. If I can unselect the groups in the VBA before clearing the optionbuttons then I am sure it will work -----Original Message----- Are you sure you know what a groupbox is? It is another control on the forms toolbar. |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Boxes and Option Buttons
Thanks for all your patience.
I created a counter and found that it failed when it had gone round the loop 27 times so I changed the loop to set the checkboxes, that way I could see which one was causing the problem. It turned out that I had Ctrl Right clicked on some checkboxes in one of the groups and grouped them, it was only these few that were causing the problem anyway I have fixed it now. Thanks for all your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Group Boxes Around Option Buttons | Excel Worksheet Functions | |||
Check Boxes or Option Buttons | Excel Discussion (Misc queries) | |||
option buttons or check boxes | New Users to Excel | |||
How do i create a value for check boxes or option boxes | Excel Discussion (Misc queries) | |||
Forms: Group Boxes and Option Buttons | Excel Programming |