Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2008

Tip: Looking for answers? Try searching our database.

Groups in Option buttons

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Greg B - 23 Jan 2008 16:12 GMT
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 - 23 Jan 2008 17:01 GMT
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

> 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
[quoted text clipped - 5 lines]
>
> Greg
Greg B - 23 Jan 2008 17:22 GMT
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
> Hi Greg,
>
[quoted text clipped - 27 lines]
>>
>> Greg
Rick Rothstein (MVP - VB) - 23 Jan 2008 18:02 GMT
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

> Yeah I didn't make much sence sorry,  I have a group of 7 optionbuttons in
> a group called "expenses".
[quoted text clipped - 38 lines]
>>>
>>> Greg
Peter T - 23 Jan 2008 21:27 GMT
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

> Where did your OptionButtons, GroupBox and TextBox come from (the Visual
> Basic or Forms Toolbar; or, perhaps, you are using a UserForm)? I ask
[quoted text clipped - 46 lines]
>>>>
>>>> Greg
Rick Rothstein (MVP - VB) - 23 Jan 2008 21:42 GMT
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

> Hi Rick,
>
[quoted text clipped - 56 lines]
>>>>>
>>>>> Greg
Peter T - 23 Jan 2008 22:15 GMT
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

> 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
[quoted text clipped - 64 lines]
>>>>>>
>>>>>> Greg
Rick Rothstein (MVP - VB) - 23 Jan 2008 22:34 GMT
> 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 - 23 Jan 2008 23:02 GMT
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

>> 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!
[quoted text clipped - 11 lines]
>
> Rick
Dale Fye - 24 Jan 2008 15:10 GMT
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
Signature


email address is invalid
Please reply to newsgroup only.

> Where did your OptionButtons, GroupBox and TextBox come from (the Visual
> Basic or Forms Toolbar; or, perhaps, you are using a UserForm)? I ask
[quoted text clipped - 46 lines]
> >>>
> >>> Greg
Dale Fye - 24 Jan 2008 16:30 GMT
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

Signature

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

> Similar issue.
>
[quoted text clipped - 61 lines]
> > >>>
> > >>> Greg
Peter T - 24 Jan 2008 16:35 GMT
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

> Similar issue.
>
[quoted text clipped - 61 lines]
> > >>>
> > >>> Greg
Dale Fye - 24 Jan 2008 18:09 GMT
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

Signature

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

> When you say "determine which is selected", if you mean literally that use
> ActionControl but see below for testing if in a frame
[quoted text clipped - 118 lines]
> > > >>>
> > > >>> Greg
Peter T - 23 Jan 2008 21:16 GMT
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

> Yeah I didn't make much sence sorry,  I have a group of 7 optionbuttons in
> a group called "expenses".
[quoted text clipped - 38 lines]
>>>
>>> Greg
Greg B - 23 Jan 2008 23:42 GMT
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
> 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
[quoted text clipped - 73 lines]
>>>>
>>>> Greg
Peter T - 24 Jan 2008 11:24 GMT
What I suggested should work similarly with Optionbuttons on a Userform as
on a sheet and code behind the sheet module.

Regards,
Peter T

> 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
[quoted text clipped - 46 lines]
> >> Thanks Again
> >> Greg
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.