Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
I have a option button and label grouped and what of set the option
button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
I'm not surprised your code doesn't work but I am surprised not to be able
to change a grouped Forms optionbutton after referencing it within the Group. If you can use an ActiveX option button then the following works Sub test() Dim shp As Shape, giShp As Shape For Each shp In ActiveSheet.Shapes If shp.Name = "Group 3" Then For Each giShp In shp.GroupItems If giShp.Name = "OptionButton1" Then With giShp.DrawingObject.Object .Value = Not .Value End With End If Next End If Next End Sub Regards, Peter T wrote in message ups.com... I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
I'm confused.
If you're just changing a single optionbutton's value, why not just: Activesheet.optionbuttons("Option button 1").value = xlon But if you've grouped your option buttons (from the Forms toolbar) and want to do something to a particular group, you can find that group like: Option Explicit Sub testme() Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.GroupBox.Name = "Group Box 498" Then If ob.Name = "Option Button 1" Then ob.Value = xlOn Exit For 'stop checking ??? End If End If Next ob End Sub But the GroupBox and optionbutton each will have their own names. wrote: I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
Hi Dave,
Activesheet.optionbuttons("Option button 1").value = xlon That doesn't work for me if the Forms Option button is grouped, nor does the testme routine you posted. Regards, Peter T "Dave Peterson" wrote in message ... I'm confused. If you're just changing a single optionbutton's value, why not just: Activesheet.optionbuttons("Option button 1").value = xlon But if you've grouped your option buttons (from the Forms toolbar) and want to do something to a particular group, you can find that group like: Option Explicit Sub testme() Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.GroupBox.Name = "Group Box 498" Then If ob.Name = "Option Button 1" Then ob.Value = xlOn Exit For 'stop checking ??? End If End If Next ob End Sub But the GroupBox and optionbutton each will have their own names. wrote: I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
They worked ok for me.
Are you sure you had option buttons and groupboxes with the matching names? Peter T wrote: Hi Dave, Activesheet.optionbuttons("Option button 1").value = xlon That doesn't work for me if the Forms Option button is grouped, nor does the testme routine you posted. Regards, Peter T "Dave Peterson" wrote in message ... I'm confused. If you're just changing a single optionbutton's value, why not just: Activesheet.optionbuttons("Option button 1").value = xlon But if you've grouped your option buttons (from the Forms toolbar) and want to do something to a particular group, you can find that group like: Option Explicit Sub testme() Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.GroupBox.Name = "Group Box 498" Then If ob.Name = "Option Button 1" Then ob.Value = xlOn Exit For 'stop checking ??? End If End If Next ob End Sub But the GroupBox and optionbutton each will have their own names. wrote: I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
With an option button and a rectangle in group it failed, but when ungrouped
it works Sub test() On Error GoTo errH ' fails ActiveSheet.OptionButtons("Option Button 1").Value = xlOn '' 1004 Unable to set the Value property of the OptionButton class ActiveSheet.GroupObjects(1).Ungroup ' now it works ActiveSheet.OptionButtons("Option Button 1").Value = xlOn Exit Sub errH: Debug.Print Err.Number; Err.Description Resume Next End Sub Maybe it's a version difference thing, this was with XL2000 Regards, Peter T "Dave Peterson" wrote in message ... They worked ok for me. Are you sure you had option buttons and groupboxes with the matching names? Peter T wrote: Hi Dave, Activesheet.optionbuttons("Option button 1").value = xlon That doesn't work for me if the Forms Option button is grouped, nor does the testme routine you posted. Regards, Peter T "Dave Peterson" wrote in message ... I'm confused. If you're just changing a single optionbutton's value, why not just: Activesheet.optionbuttons("Option button 1").value = xlon But if you've grouped your option buttons (from the Forms toolbar) and want to do something to a particular group, you can find that group like: Option Explicit Sub testme() Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.GroupBox.Name = "Group Box 498" Then If ob.Name = "Option Button 1" Then ob.Value = xlOn Exit For 'stop checking ??? End If End If Next ob End Sub But the GroupBox and optionbutton each will have their own names. wrote: I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
I don't think that the option buttons were grouped like that:
(Selecting a few, then rightclick|grouping|Group) I'm gonna guess that they were grouped by putting a groupbox around the optionbuttons (or putting the optionbuttons within a groupbox). The groupbox is also on the Forms toolbar. But I've been wrong before. Peter T wrote: With an option button and a rectangle in group it failed, but when ungrouped it works Sub test() On Error GoTo errH ' fails ActiveSheet.OptionButtons("Option Button 1").Value = xlOn '' 1004 Unable to set the Value property of the OptionButton class ActiveSheet.GroupObjects(1).Ungroup ' now it works ActiveSheet.OptionButtons("Option Button 1").Value = xlOn Exit Sub errH: Debug.Print Err.Number; Err.Description Resume Next End Sub Maybe it's a version difference thing, this was with XL2000 Regards, Peter T "Dave Peterson" wrote in message ... They worked ok for me. Are you sure you had option buttons and groupboxes with the matching names? Peter T wrote: Hi Dave, Activesheet.optionbuttons("Option button 1").value = xlon That doesn't work for me if the Forms Option button is grouped, nor does the testme routine you posted. Regards, Peter T "Dave Peterson" wrote in message ... I'm confused. If you're just changing a single optionbutton's value, why not just: Activesheet.optionbuttons("Option button 1").value = xlon But if you've grouped your option buttons (from the Forms toolbar) and want to do something to a particular group, you can find that group like: Option Explicit Sub testme() Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.GroupBox.Name = "Group Box 498" Then If ob.Name = "Option Button 1" Then ob.Value = xlOn Exit For 'stop checking ??? End If End If Next ob End Sub But the GroupBox and optionbutton each will have their own names. wrote: I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
The option button is from the Forms menu and is grouped to a label
using rightclick/grouping/group. The routine I showed, does find the "Group 498" but not the option button in it. It's as though the "Group 498" is now the option button, but you cannot change the value. Hope this makes sense and thank you for your continued help. This is doing my head in. Dave Peterson wrote: I don't think that the option buttons were grouped like that: (Selecting a few, then rightclick|grouping|Group) I'm gonna guess that they were grouped by putting a groupbox around the optionbuttons (or putting the optionbuttons within a groupbox). The groupbox is also on the Forms toolbar. But I've been wrong before. Peter T wrote: With an option button and a rectangle in group it failed, but when ungrouped it works Sub test() On Error GoTo errH ' fails ActiveSheet.OptionButtons("Option Button 1").Value = xlOn '' 1004 Unable to set the Value property of the OptionButton class ActiveSheet.GroupObjects(1).Ungroup ' now it works ActiveSheet.OptionButtons("Option Button 1").Value = xlOn Exit Sub errH: Debug.Print Err.Number; Err.Description Resume Next End Sub Maybe it's a version difference thing, this was with XL2000 Regards, Peter T "Dave Peterson" wrote in message ... They worked ok for me. Are you sure you had option buttons and groupboxes with the matching names? Peter T wrote: Hi Dave, Activesheet.optionbuttons("Option button 1").value = xlon That doesn't work for me if the Forms Option button is grouped, nor does the testme routine you posted. Regards, Peter T "Dave Peterson" wrote in message ... I'm confused. If you're just changing a single optionbutton's value, why not just: Activesheet.optionbuttons("Option button 1").value = xlon But if you've grouped your option buttons (from the Forms toolbar) and want to do something to a particular group, you can find that group like: Option Explicit Sub testme() Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.GroupBox.Name = "Group Box 498" Then If ob.Name = "Option Button 1" Then ob.Value = xlOn Exit For 'stop checking ??? End If End If Next ob End Sub But the GroupBox and optionbutton each will have their own names. wrote: I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
Don't group your option buttons via rightclick|grouping|group.
Group them by putting a groupbox from the Forms toolbar around the ones that should be grouped. wrote: The option button is from the Forms menu and is grouped to a label using rightclick/grouping/group. The routine I showed, does find the "Group 498" but not the option button in it. It's as though the "Group 498" is now the option button, but you cannot change the value. Hope this makes sense and thank you for your continued help. This is doing my head in. Dave Peterson wrote: I don't think that the option buttons were grouped like that: (Selecting a few, then rightclick|grouping|Group) I'm gonna guess that they were grouped by putting a groupbox around the optionbuttons (or putting the optionbuttons within a groupbox). The groupbox is also on the Forms toolbar. But I've been wrong before. Peter T wrote: With an option button and a rectangle in group it failed, but when ungrouped it works Sub test() On Error GoTo errH ' fails ActiveSheet.OptionButtons("Option Button 1").Value = xlOn '' 1004 Unable to set the Value property of the OptionButton class ActiveSheet.GroupObjects(1).Ungroup ' now it works ActiveSheet.OptionButtons("Option Button 1").Value = xlOn Exit Sub errH: Debug.Print Err.Number; Err.Description Resume Next End Sub Maybe it's a version difference thing, this was with XL2000 Regards, Peter T "Dave Peterson" wrote in message ... They worked ok for me. Are you sure you had option buttons and groupboxes with the matching names? Peter T wrote: Hi Dave, Activesheet.optionbuttons("Option button 1").value = xlon That doesn't work for me if the Forms Option button is grouped, nor does the testme routine you posted. Regards, Peter T "Dave Peterson" wrote in message ... I'm confused. If you're just changing a single optionbutton's value, why not just: Activesheet.optionbuttons("Option button 1").value = xlon But if you've grouped your option buttons (from the Forms toolbar) and want to do something to a particular group, you can find that group like: Option Explicit Sub testme() Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.GroupBox.Name = "Group Box 498" Then If ob.Name = "Option Button 1" Then ob.Value = xlOn Exit For 'stop checking ??? End If End If Next ob End Sub But the GroupBox and optionbutton each will have their own names. wrote: I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
I suppose surrounding with a groupbox vs group'ing (groupobject) would
depend on the OP's overall objective. If to allow different 'sets' of option buttons whereby one OB in each set can be 'On' then a groupbox indeed makes that possible. But that's not the same as group'ing shapes and OB's which does nothing to change their mutual exclusivity. Could of course also 'group' a groupbox of OB's (and labels) but back to the OP's original problem. Must admit I don't know why it's not possible to change the value property of a grouped Forms OB. Most properties of a Groupobject's groupitems can be individually changed without un-grouping. Regards, Peter T "Dave Peterson" wrote in message ... Don't group your option buttons via rightclick|grouping|group. Group them by putting a groupbox from the Forms toolbar around the ones that should be grouped. wrote: The option button is from the Forms menu and is grouped to a label using rightclick/grouping/group. The routine I showed, does find the "Group 498" but not the option button in it. It's as though the "Group 498" is now the option button, but you cannot change the value. Hope this makes sense and thank you for your continued help. This is doing my head in. Dave Peterson wrote: I don't think that the option buttons were grouped like that: (Selecting a few, then rightclick|grouping|Group) I'm gonna guess that they were grouped by putting a groupbox around the optionbuttons (or putting the optionbuttons within a groupbox). The groupbox is also on the Forms toolbar. But I've been wrong before. Peter T wrote: With an option button and a rectangle in group it failed, but when ungrouped it works Sub test() On Error GoTo errH ' fails ActiveSheet.OptionButtons("Option Button 1").Value = xlOn '' 1004 Unable to set the Value property of the OptionButton class ActiveSheet.GroupObjects(1).Ungroup ' now it works ActiveSheet.OptionButtons("Option Button 1").Value = xlOn Exit Sub errH: Debug.Print Err.Number; Err.Description Resume Next End Sub Maybe it's a version difference thing, this was with XL2000 Regards, Peter T "Dave Peterson" wrote in message ... They worked ok for me. Are you sure you had option buttons and groupboxes with the matching names? Peter T wrote: Hi Dave, Activesheet.optionbuttons("Option button 1").value = xlon That doesn't work for me if the Forms Option button is grouped, nor does the testme routine you posted. Regards, Peter T "Dave Peterson" wrote in message ... I'm confused. If you're just changing a single optionbutton's value, why not just: Activesheet.optionbuttons("Option button 1").value = xlon But if you've grouped your option buttons (from the Forms toolbar) and want to do something to a particular group, you can find that group like: Option Explicit Sub testme() Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.GroupBox.Name = "Group Box 498" Then If ob.Name = "Option Button 1" Then ob.Value = xlOn Exit For 'stop checking ??? End If End If Next ob End Sub But the GroupBox and optionbutton each will have their own names. wrote: I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
I'm not sure I understand this portion:
But that's not the same as group'ing shapes and OB's which does nothing to change their mutual exclusivity. when it concerns optionbuttons from the Forms toolbar that have been grouped using a groupbox from that same Forms toolbar. Each group of optionbuttons within that groupbox is separate and distinct from the other optionbuttons in different groupboxes--and only one of the optionbuttons within that groupbox can be selected at one time. === And to be more honest, I've never seen anyone try to group controls like optionbuttons via the grouping of shapes. That seems a little weird to me. Peter T wrote: I suppose surrounding with a groupbox vs group'ing (groupobject) would depend on the OP's overall objective. If to allow different 'sets' of option buttons whereby one OB in each set can be 'On' then a groupbox indeed makes that possible. But that's not the same as group'ing shapes and OB's which does nothing to change their mutual exclusivity. Could of course also 'group' a groupbox of OB's (and labels) but back to the OP's original problem. Must admit I don't know why it's not possible to change the value property of a grouped Forms OB. Most properties of a Groupobject's groupitems can be individually changed without un-grouping. Regards, Peter T "Dave Peterson" wrote in message ... Don't group your option buttons via rightclick|grouping|group. Group them by putting a groupbox from the Forms toolbar around the ones that should be grouped. wrote: The option button is from the Forms menu and is grouped to a label using rightclick/grouping/group. The routine I showed, does find the "Group 498" but not the option button in it. It's as though the "Group 498" is now the option button, but you cannot change the value. Hope this makes sense and thank you for your continued help. This is doing my head in. Dave Peterson wrote: I don't think that the option buttons were grouped like that: (Selecting a few, then rightclick|grouping|Group) I'm gonna guess that they were grouped by putting a groupbox around the optionbuttons (or putting the optionbuttons within a groupbox). The groupbox is also on the Forms toolbar. But I've been wrong before. Peter T wrote: With an option button and a rectangle in group it failed, but when ungrouped it works Sub test() On Error GoTo errH ' fails ActiveSheet.OptionButtons("Option Button 1").Value = xlOn '' 1004 Unable to set the Value property of the OptionButton class ActiveSheet.GroupObjects(1).Ungroup ' now it works ActiveSheet.OptionButtons("Option Button 1").Value = xlOn Exit Sub errH: Debug.Print Err.Number; Err.Description Resume Next End Sub Maybe it's a version difference thing, this was with XL2000 Regards, Peter T "Dave Peterson" wrote in message ... They worked ok for me. Are you sure you had option buttons and groupboxes with the matching names? Peter T wrote: Hi Dave, Activesheet.optionbuttons("Option button 1").value = xlon That doesn't work for me if the Forms Option button is grouped, nor does the testme routine you posted. Regards, Peter T "Dave Peterson" wrote in message ... I'm confused. If you're just changing a single optionbutton's value, why not just: Activesheet.optionbuttons("Option button 1").value = xlon But if you've grouped your option buttons (from the Forms toolbar) and want to do something to a particular group, you can find that group like: Option Explicit Sub testme() Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.GroupBox.Name = "Group Box 498" Then If ob.Name = "Option Button 1" Then ob.Value = xlOn Exit For 'stop checking ??? End If End If Next ob End Sub But the GroupBox and optionbutton each will have their own names. wrote: I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
I'm not sure I understand this portion:
But that's not the same as group'ing shapes and OB's which does nothing to change their mutual exclusivity. Sorry to confuse! eminates from the different meanings of the term 'group' particularly with Option Buttons a) Group shapes: select multiple shapes, right-click and select 'Group'. The multiple shapes, which may include Option Buttons, become a single shape, in VBA terms a GroupObject b) Group with a Group-box: from the Forms toolbar select the 'Group Box' icon and 'draw' it around Option Buttons. This 'set' is not related to other OB's in the sense in one of the OB's can be On even if another elsewhere is also On. Back to what I meant - group type a) does nothing to change the mutual exclusivity of Option Buttons, whether or not they are in a Group type b). Is that more or less confusing <g And to be more honest, I've never seen anyone try to group controls like optionbuttons via the grouping of shapes. That seems a little weird to me. I never have but I do Group type a) other Controls, and can see the reason for the OP wanting to group type a) with a label or textbox. Regards, Peter T "Dave Peterson" wrote in message ... I'm not sure I understand this portion: But that's not the same as group'ing shapes and OB's which does nothing to change their mutual exclusivity. when it concerns optionbuttons from the Forms toolbar that have been grouped using a groupbox from that same Forms toolbar. Each group of optionbuttons within that groupbox is separate and distinct from the other optionbuttons in different groupboxes--and only one of the optionbuttons within that groupbox can be selected at one time. === And to be more honest, I've never seen anyone try to group controls like optionbuttons via the grouping of shapes. That seems a little weird to me. Peter T wrote: I suppose surrounding with a groupbox vs group'ing (groupobject) would depend on the OP's overall objective. If to allow different 'sets' of option buttons whereby one OB in each set can be 'On' then a groupbox indeed makes that possible. But that's not the same as group'ing shapes and OB's which does nothing to change their mutual exclusivity. Could of course also 'group' a groupbox of OB's (and labels) but back to the OP's original problem. Must admit I don't know why it's not possible to change the value property of a grouped Forms OB. Most properties of a Groupobject's groupitems can be individually changed without un-grouping. Regards, Peter T "Dave Peterson" wrote in message ... Don't group your option buttons via rightclick|grouping|group. Group them by putting a groupbox from the Forms toolbar around the ones that should be grouped. wrote: The option button is from the Forms menu and is grouped to a label using rightclick/grouping/group. The routine I showed, does find the "Group 498" but not the option button in it. It's as though the "Group 498" is now the option button, but you cannot change the value. Hope this makes sense and thank you for your continued help. This is doing my head in. Dave Peterson wrote: I don't think that the option buttons were grouped like that: (Selecting a few, then rightclick|grouping|Group) I'm gonna guess that they were grouped by putting a groupbox around the optionbuttons (or putting the optionbuttons within a groupbox). The groupbox is also on the Forms toolbar. But I've been wrong before. Peter T wrote: With an option button and a rectangle in group it failed, but when ungrouped it works Sub test() On Error GoTo errH ' fails ActiveSheet.OptionButtons("Option Button 1").Value = xlOn '' 1004 Unable to set the Value property of the OptionButton class ActiveSheet.GroupObjects(1).Ungroup ' now it works ActiveSheet.OptionButtons("Option Button 1").Value = xlOn Exit Sub errH: Debug.Print Err.Number; Err.Description Resume Next End Sub Maybe it's a version difference thing, this was with XL2000 Regards, Peter T "Dave Peterson" wrote in message ... They worked ok for me. Are you sure you had option buttons and groupboxes with the matching names? Peter T wrote: Hi Dave, Activesheet.optionbuttons("Option button 1").value = xlon That doesn't work for me if the Forms Option button is grouped, nor does the testme routine you posted. Regards, Peter T "Dave Peterson" wrote in message ... I'm confused. If you're just changing a single optionbutton's value, why not just: Activesheet.optionbuttons("Option button 1").value = xlon But if you've grouped your option buttons (from the Forms toolbar) and want to do something to a particular group, you can find that group like: Option Explicit Sub testme() Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.GroupBox.Name = "Group Box 498" Then If ob.Name = "Option Button 1" Then ob.Value = xlOn Exit For 'stop checking ??? End If End If Next ob End Sub But the GroupBox and optionbutton each will have their own names. wrote: I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Grouped
I understood the difference between the two different grouping methods.
I just didn't understand what you were saying. I do now. And even though I've never done it or seen it, I guess there may be a reason to "shape-group" these type controls--but I'm never gonna do it! <vbg Peter T wrote: I'm not sure I understand this portion: But that's not the same as group'ing shapes and OB's which does nothing to change their mutual exclusivity. Sorry to confuse! eminates from the different meanings of the term 'group' particularly with Option Buttons a) Group shapes: select multiple shapes, right-click and select 'Group'. The multiple shapes, which may include Option Buttons, become a single shape, in VBA terms a GroupObject b) Group with a Group-box: from the Forms toolbar select the 'Group Box' icon and 'draw' it around Option Buttons. This 'set' is not related to other OB's in the sense in one of the OB's can be On even if another elsewhere is also On. Back to what I meant - group type a) does nothing to change the mutual exclusivity of Option Buttons, whether or not they are in a Group type b). Is that more or less confusing <g And to be more honest, I've never seen anyone try to group controls like optionbuttons via the grouping of shapes. That seems a little weird to me. I never have but I do Group type a) other Controls, and can see the reason for the OP wanting to group type a) with a label or textbox. Regards, Peter T "Dave Peterson" wrote in message ... I'm not sure I understand this portion: But that's not the same as group'ing shapes and OB's which does nothing to change their mutual exclusivity. when it concerns optionbuttons from the Forms toolbar that have been grouped using a groupbox from that same Forms toolbar. Each group of optionbuttons within that groupbox is separate and distinct from the other optionbuttons in different groupboxes--and only one of the optionbuttons within that groupbox can be selected at one time. === And to be more honest, I've never seen anyone try to group controls like optionbuttons via the grouping of shapes. That seems a little weird to me. Peter T wrote: I suppose surrounding with a groupbox vs group'ing (groupobject) would depend on the OP's overall objective. If to allow different 'sets' of option buttons whereby one OB in each set can be 'On' then a groupbox indeed makes that possible. But that's not the same as group'ing shapes and OB's which does nothing to change their mutual exclusivity. Could of course also 'group' a groupbox of OB's (and labels) but back to the OP's original problem. Must admit I don't know why it's not possible to change the value property of a grouped Forms OB. Most properties of a Groupobject's groupitems can be individually changed without un-grouping. Regards, Peter T "Dave Peterson" wrote in message ... Don't group your option buttons via rightclick|grouping|group. Group them by putting a groupbox from the Forms toolbar around the ones that should be grouped. wrote: The option button is from the Forms menu and is grouped to a label using rightclick/grouping/group. The routine I showed, does find the "Group 498" but not the option button in it. It's as though the "Group 498" is now the option button, but you cannot change the value. Hope this makes sense and thank you for your continued help. This is doing my head in. Dave Peterson wrote: I don't think that the option buttons were grouped like that: (Selecting a few, then rightclick|grouping|Group) I'm gonna guess that they were grouped by putting a groupbox around the optionbuttons (or putting the optionbuttons within a groupbox). The groupbox is also on the Forms toolbar. But I've been wrong before. Peter T wrote: With an option button and a rectangle in group it failed, but when ungrouped it works Sub test() On Error GoTo errH ' fails ActiveSheet.OptionButtons("Option Button 1").Value = xlOn '' 1004 Unable to set the Value property of the OptionButton class ActiveSheet.GroupObjects(1).Ungroup ' now it works ActiveSheet.OptionButtons("Option Button 1").Value = xlOn Exit Sub errH: Debug.Print Err.Number; Err.Description Resume Next End Sub Maybe it's a version difference thing, this was with XL2000 Regards, Peter T "Dave Peterson" wrote in message ... They worked ok for me. Are you sure you had option buttons and groupboxes with the matching names? Peter T wrote: Hi Dave, Activesheet.optionbuttons("Option button 1").value = xlon That doesn't work for me if the Forms Option button is grouped, nor does the testme routine you posted. Regards, Peter T "Dave Peterson" wrote in message ... I'm confused. If you're just changing a single optionbutton's value, why not just: Activesheet.optionbuttons("Option button 1").value = xlon But if you've grouped your option buttons (from the Forms toolbar) and want to do something to a particular group, you can find that group like: Option Explicit Sub testme() Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.GroupBox.Name = "Group Box 498" Then If ob.Name = "Option Button 1" Then ob.Value = xlOn Exit For 'stop checking ??? End If End If Next ob End Sub But the GroupBox and optionbutton each will have their own names. wrote: I have a option button and label grouped and what of set the option button to value xlon. I cannot seem to be able to set the value when it's grouped. I have been trying this which finds the option button but will not set the value. Dim ob As OptionButton For Each ob In ActiveSheet.OptionButtons If ob.Name = "Group 498" Then ob.Value = xlOn End If Next I get error 'Run-time error 1004': Unable to set the value property of the optionbutton class. Can somebody tell me where I'm going wrong. Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grouped Items won't Stay Grouped When Moving Object | Excel Discussion (Misc queries) | |||
keep source formatting is not an option in paste option button | Excel Discussion (Misc queries) | |||
Option Button | Excel Programming | |||
Option button | Excel Programming | |||
Option Button Value | Excel Programming |