Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,202
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,202
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,202
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 169
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 169
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 169
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
with multiple option buttons on my form how do I isolate groups sabrina Excel Worksheet Functions 4 September 17th 07 10:06 PM
Two Groups of Option Buttons on Worksheet Jim May Excel Discussion (Misc queries) 0 June 24th 06 09:34 PM
Option buttons: How to get the selected option from a group? naddad Excel Programming 5 December 21st 05 06:09 PM
Navigating between option buttons is not selecting the option Gixxer_J_97[_2_] Excel Programming 4 June 2nd 05 02:50 PM
Option Buttons - groups Ciara Excel Discussion (Misc queries) 4 May 18th 05 05:41 PM


All times are GMT +1. The time now is 03:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"