Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
I would like to know how I can get the data from a group of option buttons.
I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
Hi Greg,
Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a
group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg "Peter T" <peter_t@discussions wrote in message ... Hi Greg, Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
Where did your OptionButtons, GroupBox and TextBox come from (the Visual
Basic or Forms Toolbar; or, perhaps, you are using a UserForm)? I ask because the Forms' Toolbar has a GroupBox but no TextBox whereas the Visual Basic Toolbar calls its "group box" a Frame and does have a TextBox. Or are you mixing components from both Toolbars? Rick "Greg B" wrote in message ... Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg "Peter T" <peter_t@discussions wrote in message ... Hi Greg, Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
Not sure I'm much the wiser. Guessing as to what you have and want to
achieve - following should work with ActiveX OptionButtons whether on a form or on a sheet: Private Sub OptionButton1_Click() ProcessOB OptionButton1 End Sub Private Sub OptionButton2_Click() ProcessOB OptionButton2 End Sub Private Sub ProcessOB(ob As MSForms.OptionButton) Dim sCap As String, sGrp As String With ob sGrp = .GroupName sCap = .Caption End With Select Case sGrp Case "expenses" Me.reason.Text = s ' maybe set some module level variable here Case "tips" ' code End Select MsgBox sCap, , sGrp End Sub You might also look into using 'WithEvents' to handle events of multiple similar controls a class module. Regards, Peter T "Greg B" wrote in message ... Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg "Peter T" <peter_t@discussions wrote in message ... Hi Greg, Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
Hi Rick,
I suspect ActiveX OB's on a form or a sheet. OptionButton's GroupName property enables sets of mutually exclusive buttons as an alternative to say placing each set in its own Frame. Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... Where did your OptionButtons, GroupBox and TextBox come from (the Visual Basic or Forms Toolbar; or, perhaps, you are using a UserForm)? I ask because the Forms' Toolbar has a GroupBox but no TextBox whereas the Visual Basic Toolbar calls its "group box" a Frame and does have a TextBox. Or are you mixing components from both Toolbars? Rick "Greg B" wrote in message ... Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg "Peter T" <peter_t@discussions wrote in message ... Hi Greg, Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
You are probably right; but, of course, the GroupName property is not the
same as a GroupBox... the OP's use of references to names for controls from both the Forms and Visual Basic toolbars is what left me (and still leaves me) wondering exactly what he is doing. Rick "Peter T" <peter_t@discussions wrote in message ... Hi Rick, I suspect ActiveX OB's on a form or a sheet. OptionButton's GroupName property enables sets of mutually exclusive buttons as an alternative to say placing each set in its own Frame. Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... Where did your OptionButtons, GroupBox and TextBox come from (the Visual Basic or Forms Toolbar; or, perhaps, you are using a UserForm)? I ask because the Forms' Toolbar has a GroupBox but no TextBox whereas the Visual Basic Toolbar calls its "group box" a Frame and does have a TextBox. Or are you mixing components from both Toolbars? Rick "Greg B" wrote in message ... Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg "Peter T" <peter_t@discussions wrote in message ... Hi Greg, Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
He says he has "optionboxes", presumably from the 0ptionBox' menu, so it's
all clear <g To be fair I'm not sure he ever mentioned GroupBox (I did) but he did say "groupname" in his OP which is what I'm basing the rest of my guess on! Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... You are probably right; but, of course, the GroupName property is not the same as a GroupBox... the OP's use of references to names for controls from both the Forms and Visual Basic toolbars is what left me (and still leaves me) wondering exactly what he is doing. Rick "Peter T" <peter_t@discussions wrote in message ... Hi Rick, I suspect ActiveX OB's on a form or a sheet. OptionButton's GroupName property enables sets of mutually exclusive buttons as an alternative to say placing each set in its own Frame. Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... Where did your OptionButtons, GroupBox and TextBox come from (the Visual Basic or Forms Toolbar; or, perhaps, you are using a UserForm)? I ask because the Forms' Toolbar has a GroupBox but no TextBox whereas the Visual Basic Toolbar calls its "group box" a Frame and does have a TextBox. Or are you mixing components from both Toolbars? Rick "Greg B" wrote in message ... Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg "Peter T" <peter_t@discussions wrote in message ... Hi Greg, Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
To be fair I'm not sure he ever mentioned GroupBox (I did) but he did say
"groupname" in his OP which is what I'm basing the rest of my guess on! Ah, you are right. I see the problem now... I just noticed that the OP's original message is not showing in my newsreader. Your answer to his original posting is what I see as the top message in this thread; so, when I responded to his 2nd message (which looks like his first message to me), I went to what I thought was his first message (which was actually your answer to his first message), but didn't scroll down to see the OP's original message in the quoted section of your response.... confused yet<g.... and looked around quickly (without paying attention to the names of the posters) and it is there I picked up the mention of the GroupBoxes. Rick |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
Ah well, I know over in vb proper land the convention is down under, whereas
over here it is up top. Where I come from we drive on the left, obviously the most sensible and correct side on which to drive, whereas not insignificant parts of the world drive on the right. But I don't mind driving on the wrong side to go with the flow, that is, if I can't go in the lane I really prefer which is straight down the middle! Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... To be fair I'm not sure he ever mentioned GroupBox (I did) but he did say "groupname" in his OP which is what I'm basing the rest of my guess on! Ah, you are right. I see the problem now... I just noticed that the OP's original message is not showing in my newsreader. Your answer to his original posting is what I see as the top message in this thread; so, when I responded to his 2nd message (which looks like his first message to me), I went to what I thought was his first message (which was actually your answer to his first message), but didn't scroll down to see the OP's original message in the quoted section of your response.... confused yet<g.... and looked around quickly (without paying attention to the names of the posters) and it is there I picked up the mention of the GroupBoxes. Rick |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
Sorry about the lack of reply it was 5.30 in the morning and i was grasping
for the answer. I loaded the option boxes in the userform section. I have built this using the userform side of the equation. Thanks Greg "Peter T" <peter_t@discussions wrote in message ... Not sure I'm much the wiser. Guessing as to what you have and want to achieve - following should work with ActiveX OptionButtons whether on a form or on a sheet: Private Sub OptionButton1_Click() ProcessOB OptionButton1 End Sub Private Sub OptionButton2_Click() ProcessOB OptionButton2 End Sub Private Sub ProcessOB(ob As MSForms.OptionButton) Dim sCap As String, sGrp As String With ob sGrp = .GroupName sCap = .Caption End With Select Case sGrp Case "expenses" Me.reason.Text = s ' maybe set some module level variable here Case "tips" ' code End Select MsgBox sCap, , sGrp End Sub You might also look into using 'WithEvents' to handle events of multiple similar controls a class module. Regards, Peter T "Greg B" wrote in message ... Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg "Peter T" <peter_t@discussions wrote in message ... Hi Greg, Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
What I suggested should work similarly with Optionbuttons on a Userform as
on a sheet and code behind the sheet module. Regards, Peter T "Greg B" wrote in message ... Sorry about the lack of reply it was 5.30 in the morning and i was grasping for the answer. I loaded the option boxes in the userform section. I have built this using the userform side of the equation. Thanks Greg "Peter T" <peter_t@discussions wrote in message ... Not sure I'm much the wiser. Guessing as to what you have and want to achieve - following should work with ActiveX OptionButtons whether on a form or on a sheet: Private Sub OptionButton1_Click() ProcessOB OptionButton1 End Sub Private Sub OptionButton2_Click() ProcessOB OptionButton2 End Sub Private Sub ProcessOB(ob As MSForms.OptionButton) Dim sCap As String, sGrp As String With ob sGrp = .GroupName sCap = .Caption End With Select Case sGrp Case "expenses" Me.reason.Text = s ' maybe set some module level variable here Case "tips" ' code End Select MsgBox sCap, , sGrp End Sub You might also look into using 'WithEvents' to handle events of multiple similar controls a class module. Regards, Peter T "Greg B" wrote in message ... Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
Similar issue.
I have a frame (vb toolbox) on a UserForm. Have two OptionButtons in the frame. In Access, each of the option buttons would have a numeric value, and I would refer to the frames value to determine which OptionButton was selected, and to set them via code. Is there a simple way to do this in Excel? I've got several more of these and would like a quick and easy way to determine which is selected and to set them via code. Dale -- email address is invalid Please reply to newsgroup only. "Rick Rothstein (MVP - VB)" wrote: Where did your OptionButtons, GroupBox and TextBox come from (the Visual Basic or Forms Toolbar; or, perhaps, you are using a UserForm)? I ask because the Forms' Toolbar has a GroupBox but no TextBox whereas the Visual Basic Toolbar calls its "group box" a Frame and does have a TextBox. Or are you mixing components from both Toolbars? Rick "Greg B" wrote in message ... Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg "Peter T" <peter_t@discussions wrote in message ... Hi Greg, Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
For now, I've written 2 routines that allow me to perform this function.
Now, all I have to do is call the function to get the value of the obtion button that is selected (assumes values of 1 to N) for the buttons, returns a zero if not selected. Same for setting the values. Public Function GetFrameValue(og As Frame) as integer Dim intPointer As Integer GetFrameValue = 0 For intPointer = 0 To og.Controls.Count - 1 If og.Controls(intPointer) = True Then GetFrameValue = intPointer + 1 End If Next End Function Public Sub SetFrameValue(og As Frame, SomeValue As Integer) Dim intPointer As Integer For intPointer = 0 To og.Controls.Count - 1 og.Controls(intPointer) = (intPointer = SomeValue - 1) Next End Sub -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Dale Fye" wrote: Similar issue. I have a frame (vb toolbox) on a UserForm. Have two OptionButtons in the frame. In Access, each of the option buttons would have a numeric value, and I would refer to the frames value to determine which OptionButton was selected, and to set them via code. Is there a simple way to do this in Excel? I've got several more of these and would like a quick and easy way to determine which is selected and to set them via code. Dale -- email address is invalid Please reply to newsgroup only. "Rick Rothstein (MVP - VB)" wrote: Where did your OptionButtons, GroupBox and TextBox come from (the Visual Basic or Forms Toolbar; or, perhaps, you are using a UserForm)? I ask because the Forms' Toolbar has a GroupBox but no TextBox whereas the Visual Basic Toolbar calls its "group box" a Frame and does have a TextBox. Or are you mixing components from both Toolbars? Rick "Greg B" wrote in message ... Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg "Peter T" <peter_t@discussions wrote in message ... Hi Greg, Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
When you say "determine which is selected", if you mean literally that use
ActionControl but see below for testing if in a frame If you mean determine which optionbutton is 'true' you'll need to poll all Optionbuttons in the frame or group.you are interested in. You can refer to a control by it's index number (instead of its name) which is set at design time and will never change. It'll be the nth item placed on the form counting from zero and also the m'th item if say in a Frame. Where a control is in a container such as a frame it will have two index numbers, one that relates to the form and another to the nth control on the frame. frm.controls(n) frm.Frame1.controls(m) Pick anything of use in the following, assumes a Frame on a form containing some optionbuttons and other controls on the form, perhaps also optionbuttons. Private Sub UserForm_Click() Dim ctr As Control, ob As MSForms.OptionButton If TypeName(Me.ActiveControl) = "Frame" Then Set ctr = Me.ActiveControl.ActiveControl Else Set ctr = Me.ActiveControl End If MsgBox ctr.Name, , ctr.Parent.Name MsgBox Me.Frame1.Controls(0).Name 'if you are certain the frame only contains optionbuttons For i = 0 To Me.Frame1.Controls.Count - 1 With Me.Frame1.Controls Debug.Print .Item(i).Caption, .Item(i).Value End With Next For i = 0 To Me.Controls.Count - 1 Debug.Print i, Me.Controls(i).Name Next End Sub Regards, Peter T "Dale Fye" wrote in message ... Similar issue. I have a frame (vb toolbox) on a UserForm. Have two OptionButtons in the frame. In Access, each of the option buttons would have a numeric value, and I would refer to the frames value to determine which OptionButton was selected, and to set them via code. Is there a simple way to do this in Excel? I've got several more of these and would like a quick and easy way to determine which is selected and to set them via code. Dale -- email address is invalid Please reply to newsgroup only. "Rick Rothstein (MVP - VB)" wrote: Where did your OptionButtons, GroupBox and TextBox come from (the Visual Basic or Forms Toolbar; or, perhaps, you are using a UserForm)? I ask because the Forms' Toolbar has a GroupBox but no TextBox whereas the Visual Basic Toolbar calls its "group box" a Frame and does have a TextBox. Or are you mixing components from both Toolbars? Rick "Greg B" wrote in message ... Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg "Peter T" <peter_t@discussions wrote in message ... Hi Greg, Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Groups in Option buttons
Peter,
One of these days, the Office development team at Microsoft will start calling objects the same thing (between applications), and start giving them the same properties. I can understand a RadioButton/OptionButton/??? having values of True/False when standing on its own, but when placed inside a Frame/OptionGroup/???, they should have numeric values and the Frame/OptionGroup/??? should have a value that relates to the buttons inside the frame. I hate having to create user-defined-functions to do something in Excel that already in the Access object model. Thank you for allowing me to vent! ;-) Dale -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Peter T" wrote: When you say "determine which is selected", if you mean literally that use ActionControl but see below for testing if in a frame If you mean determine which optionbutton is 'true' you'll need to poll all Optionbuttons in the frame or group.you are interested in. You can refer to a control by it's index number (instead of its name) which is set at design time and will never change. It'll be the nth item placed on the form counting from zero and also the m'th item if say in a Frame. Where a control is in a container such as a frame it will have two index numbers, one that relates to the form and another to the nth control on the frame. frm.controls(n) frm.Frame1.controls(m) Pick anything of use in the following, assumes a Frame on a form containing some optionbuttons and other controls on the form, perhaps also optionbuttons. Private Sub UserForm_Click() Dim ctr As Control, ob As MSForms.OptionButton If TypeName(Me.ActiveControl) = "Frame" Then Set ctr = Me.ActiveControl.ActiveControl Else Set ctr = Me.ActiveControl End If MsgBox ctr.Name, , ctr.Parent.Name MsgBox Me.Frame1.Controls(0).Name 'if you are certain the frame only contains optionbuttons For i = 0 To Me.Frame1.Controls.Count - 1 With Me.Frame1.Controls Debug.Print .Item(i).Caption, .Item(i).Value End With Next For i = 0 To Me.Controls.Count - 1 Debug.Print i, Me.Controls(i).Name Next End Sub Regards, Peter T "Dale Fye" wrote in message ... Similar issue. I have a frame (vb toolbox) on a UserForm. Have two OptionButtons in the frame. In Access, each of the option buttons would have a numeric value, and I would refer to the frames value to determine which OptionButton was selected, and to set them via code. Is there a simple way to do this in Excel? I've got several more of these and would like a quick and easy way to determine which is selected and to set them via code. Dale -- email address is invalid Please reply to newsgroup only. "Rick Rothstein (MVP - VB)" wrote: Where did your OptionButtons, GroupBox and TextBox come from (the Visual Basic or Forms Toolbar; or, perhaps, you are using a UserForm)? I ask because the Forms' Toolbar has a GroupBox but no TextBox whereas the Visual Basic Toolbar calls its "group box" a Frame and does have a TextBox. Or are you mixing components from both Toolbars? Rick "Greg B" wrote in message ... Yeah I didn't make much sence sorry, I have a group of 7 optionbuttons in a group called "expenses". Instead of going throught all with the code for each individual optionbox, I was wondering if there was a way to have the selected box put it's caption into a textbox called reason. Hope that is a bit more understandable. Thanks Again Greg "Peter T" <peter_t@discussions wrote in message ... Hi Greg, Is that Option buttons on a form to which you have assigned a GroupName property, or Forms OB's on a sheet, which you have Grouped or placed in a GroupBox Or ActiveX OB's on a Sheet which you have done either of the above or given GroupNames After clarifying the above, explain that you mean by - "I am looking to have reason.text show the answer." What is "reason", what do you have in mind for the "answer" Regards, Peter T "Greg B" wrote in message ... I would like to know how I can get the data from a group of option buttons. I have a group of 8 optionbuttons with the names "e1" through to "e8", the name of the group is "expenses" I am not sure how to use or if I can use a groupname vba. I am looking to have reason.text show the answer. Or do I have to go through and write if e1.value = true then..... thanks in advance Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
with multiple option buttons on my form how do I isolate groups | Excel Worksheet Functions | |||
Two Groups of Option Buttons on Worksheet | Excel Discussion (Misc queries) | |||
Option buttons: How to get the selected option from a group? | Excel Programming | |||
Navigating between option buttons is not selecting the option | Excel Programming | |||
Option Buttons - groups | Excel Discussion (Misc queries) |