Groups in Option buttons

[Greg B]

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

 

[Peter T]

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]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Peter T]

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

 

[Peter T]

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\)]

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]

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\)]

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

 

[Peter T]

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

 

[Greg B]

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]

What I suggested should work similarly with Optionbuttons on a Userform as
on a sheet and code behind the sheet module.

Regards,
Peter T

 

[Dale Fye]

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

 

[Dale Fye]

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.

 

[Peter T]

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]

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.