If statement on an option group

I am just starting to learn VBA and am not sure how to do if statements on
option groups if someone can help me. I tried a number of different things
but nothing happens.

I have a User Form with two option groups. They are:

frmGroup1
optYes
optNo

frmGroup2
optYes
optNo

If frmGroup1, optYes is clicked then frmGroup2, has to be optNo otherwise a
message needs to appear telling the user it must be No, the entry is canceled
and they stay in frmGroup2 so they can click No

ElseIf frmGroup1, optNo is clicked and frmGroup2, optYes is clicked (this is
not a mandatory Yes option) a message needs to appear telling the user the
item needs to be put on hold. Any other Any other options are fine and they
can continue on with the form.

Thanks in advance for all the help.

Ann
Mon Jun 16 07:56:00 PDT 2008

I was able to figure this out on my own...thanks anyway.

"Ann" wrote:

> I am just starting to learn VBA and am not sure how to do if statements on
> option groups if someone can help me. I tried a number of different things
> but nothing happens.
>
> I have a User Form with two option groups. They are:
>
> frmGroup1
> optYes
> optNo
>
> frmGroup2
> optYes
> optNo
>
> If frmGroup1, optYes is clicked then frmGroup2, has to be optNo otherwise a
> message needs to appear telling the user it must be No, the entry is canceled
> and they stay in frmGroup2 so they can click No
>
> ElseIf frmGroup1, optNo is clicked and frmGroup2, optYes is clicked (this is
> not a mandatory Yes option) a message needs to appear telling the user the
> item needs to be put on hold. Any other Any other options are fine and they
> can continue on with the form.
>
> Thanks in advance for all the help.
>
>

stevencraigmiller(at)comcast(dot)net>
Mon Jun 16 08:29:00 PDT 2008

To: Ann,

I'm unsure if I understand your problem, but it seems to me that you have
(one or) two buttons too many.

Why don't you have just two option buttons, one representing frmGroup1 and
the other frmGroup2, together in one group? Then if one selected frmGroup1,
the other button would be deselected automatically, and if one selected
frmGroup2, then frmGroup1 would be deselected autormatically. And if you
need a third option, when both frmGroup1 and frmGroup2 are deselected, then
add a third option button to the group with the caption "None" or "Neither"
or "Both off" or whatever.

On the other hand, lets say we have 4 option buttons.
Buttons 1 & 2 form one group, 1 = on & 2 = off;
Buttons 3 & 4 form another group, 3 = on & 4 = off.

The code you would need would be:

Private Sub OptionButton1_Click()
Me.OptionButton3.Value = False
Me.OptionButton4.Value = True
End Sub

Private Sub OptionButton3_Click()
Me.OptionButton1.Value = False
Me.OptionButton2.Value = True
End Sub

This would allow both groups to be "off" and yet if button1 is selected,
then button 3 is turned off and button 4 (the "off" button) is turned on.
Likewise, if button 3 is turned on, button 1 is turned off and button 2 (the
"off" button) is turned on.

Steven Craig Miller

"Ann" wrote:

> I am just starting to learn VBA and am not sure how to do if statements on
> option groups if someone can help me. I tried a number of different things
> but nothing happens.
>
> I have a User Form with two option groups. They are:
>
> frmGroup1
> optYes
> optNo
>
> frmGroup2
> optYes
> optNo
>
> If frmGroup1, optYes is clicked then frmGroup2, has to be optNo otherwise a
> message needs to appear telling the user it must be No, the entry is canceled
> and they stay in frmGroup2 so they can click No
>
> ElseIf frmGroup1, optNo is clicked and frmGroup2, optYes is clicked (this is
> not a mandatory Yes option) a message needs to appear telling the user the
> item needs to be put on hold. Any other Any other options are fine and they
> can continue on with the form.
>
> Thanks in advance for all the help.

Ann
Mon Jun 16 08:59:00 PDT 2008

I think I understand what you are saying. I used a tutorial from
http://www.fontstuff.com/vba/vbatut09pfv.htm and I guess being a beginner I
just didn't think about using one button. I will the next time though.
Thanks.


This"StevenM" wrote:

> To: Ann,
>
> I'm unsure if I understand your problem, but it seems to me that you have
> (one or) two buttons too many.
>
> Why don't you have just two option buttons, one representing frmGroup1 and
> the other frmGroup2, together in one group? Then if one selected frmGroup1,
> the other button would be deselected automatically, and if one selected
> frmGroup2, then frmGroup1 would be deselected autormatically. And if you
> need a third option, when both frmGroup1 and frmGroup2 are deselected, then
> add a third option button to the group with the caption "None" or "Neither"
> or "Both off" or whatever.
>
> On the other hand, lets say we have 4 option buttons.
> Buttons 1 & 2 form one group, 1 = on & 2 = off;
> Buttons 3 & 4 form another group, 3 = on & 4 = off.
>
> The code you would need would be:
>
> Private Sub OptionButton1_Click()
> Me.OptionButton3.Value = False
> Me.OptionButton4.Value = True
> End Sub
>
> Private Sub OptionButton3_Click()
> Me.OptionButton1.Value = False
> Me.OptionButton2.Value = True
> End Sub
>
> This would allow both groups to be "off" and yet if button1 is selected,
> then button 3 is turned off and button 4 (the "off" button) is turned on.
> Likewise, if button 3 is turned on, button 1 is turned off and button 2 (the
> "off" button) is turned on.
>
> Steven Craig Miller
>
> "Ann" wrote:
>
> > I am just starting to learn VBA and am not sure how to do if statements on
> > option groups if someone can help me. I tried a number of different things
> > but nothing happens.
> >
> > I have a User Form with two option groups. They are:
> >
> > frmGroup1
> > optYes
> > optNo
> >
> > frmGroup2
> > optYes
> > optNo
> >
> > If frmGroup1, optYes is clicked then frmGroup2, has to be optNo otherwise a
> > message needs to appear telling the user it must be No, the entry is canceled
> > and they stay in frmGroup2 so they can click No
> >
> > ElseIf frmGroup1, optNo is clicked and frmGroup2, optYes is clicked (this is
> > not a mandatory Yes option) a message needs to appear telling the user the
> > item needs to be put on hold. Any other Any other options are fine and they
> > can continue on with the form.
> >
> > Thanks in advance for all the help.
>

gordon(dot)bentleymix(at)gmail(dot)com>
Mon Jun 16 17:01:01 PDT 2008

Ann,

In situations where you only have two choices, it's often easier to use a
CheckBox over a pair of OptionButtons. Of course you have to restructure the
'question' that selecting the CheckBox 'answers' a bit, but it's probably
worth it in the long run. For one thing, it's easier for the user because:
they only have to select and change the state of one control (and if the user
is tabbing through the controls on the form, the space bar makes this really
easy); and they don't have to tab past the other OptionButton and run the
risk of changing its state accidentally.

It's also easier for you as the developer because you don't have to put a
CheckBox into Frame (option group in your terms) to achieve the 'exclusivity'
functionality of OptionButtons. You also only have to evaluate the state of
one control instead of two when doing things like writing Change event
procedures or using UserForm data to control the creation of a document.

In your particular situation, I would probably use two CheckBoxes and use
the state of the first CheckBox (selected or unselected) in a Change event
procedure to enable or disable the second CheckBox. Something like this:

Private Sub CheckBox1_Change()
If CheckBox1.Value = True Then
With CheckBox2
.Enabled = True
.Locked = False
.TabStop = True
End With
Else
With CheckBox2
.Enabled = False
.Locked = True
.TabStop = False
.Value = False
End With
End If
End Sub

You could even go so far as to write separate procedures for enabling and
disable CheckBox2. I would, but then I'm a bit obsessive about modular code.
;-P
--
Cheers!
Gordon
The Kiwi Koder

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.


"Ann" wrote:

> I am just starting to learn VBA and am not sure how to do if statements on
> option groups if someone can help me. I tried a number of different things
> but nothing happens.
>
> I have a User Form with two option groups. They are:
>
> frmGroup1
> optYes
> optNo
>
> frmGroup2
> optYes
> optNo
>
> If frmGroup1, optYes is clicked then frmGroup2, has to be optNo otherwise a
> message needs to appear telling the user it must be No, the entry is canceled
> and they stay in frmGroup2 so they can click No
>
> ElseIf frmGroup1, optNo is clicked and frmGroup2, optYes is clicked (this is
> not a mandatory Yes option) a message needs to appear telling the user the
> item needs to be put on hold. Any other Any other options are fine and they
> can continue on with the form.
>
> Thanks in advance for all the help.
>
>

gordon(dot)bentleymix(at)gmail(dot)com>
Mon Jun 16 17:06:01 PDT 2008

Another way of doing it - just for fun:

Private Sub CheckBox1_Change()
With CheckBox2
If CheckBox1.Value = True Then
.Enabled = True
.Locked = False
.TabStop = True
Else
.Enabled = False
.Locked = True
.TabStop = False
.Value = False
End If
End With
End Sub

A couple fewer lines of code and an example of creative use of a With
statement...
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

"Gordon Bentley-Mix" wrote:

> In your particular situation, I would probably use two CheckBoxes and use
> the state of the first CheckBox (selected or unselected) in a Change event
> procedure to enable or disable the second CheckBox. Something like this:
>
> Private Sub CheckBox1_Change()
> If CheckBox1.Value = True Then
> With CheckBox2
> .Enabled = True
> .Locked = False
> .TabStop = True
> End With
> Else
> With CheckBox2
> .Enabled = False
> .Locked = True
> .TabStop = False
> .Value = False
> End With
> End If
> End Sub

Ann
Tue Jun 17 04:08:01 PDT 2008

Thank you Gordon, for both examples. I'm going to add them to my file for
future use. Everyone is so nice to answer questions that are probably second
nature to you.

"Gordon Bentley-Mix" wrote:

> Ann,
>
> In situations where you only have two choices, it's often easier to use a
> CheckBox over a pair of OptionButtons. Of course you have to restructure the
> 'question' that selecting the CheckBox 'answers' a bit, but it's probably
> worth it in the long run. For one thing, it's easier for the user because:
> they only have to select and change the state of one control (and if the user
> is tabbing through the controls on the form, the space bar makes this really
> easy); and they don't have to tab past the other OptionButton and run the
> risk of changing its state accidentally.
>
> It's also easier for you as the developer because you don't have to put a
> CheckBox into Frame (option group in your terms) to achieve the 'exclusivity'
> functionality of OptionButtons. You also only have to evaluate the state of
> one control instead of two when doing things like writing Change event
> procedures or using UserForm data to control the creation of a document.
>
> In your particular situation, I would probably use two CheckBoxes and use
> the state of the first CheckBox (selected or unselected) in a Change event
> procedure to enable or disable the second CheckBox. Something like this:
>
> Private Sub CheckBox1_Change()
> If CheckBox1.Value = True Then
> With CheckBox2
> .Enabled = True
> .Locked = False
> .TabStop = True
> End With
> Else
> With CheckBox2
> .Enabled = False
> .Locked = True
> .TabStop = False
> .Value = False
> End With
> End If
> End Sub
>
> You could even go so far as to write separate procedures for enabling and
> disable CheckBox2. I would, but then I'm a bit obsessive about modular code.
> ;-P
> --
> Cheers!
> Gordon
> The Kiwi Koder
>
> Uninvited email contact will be marked as SPAM and ignored. Please post all
> follow-ups to the newsgroup.
>
>
> "Ann" wrote:
>
> > I am just starting to learn VBA and am not sure how to do if statements on
> > option groups if someone can help me. I tried a number of different things
> > but nothing happens.
> >
> > I have a User Form with two option groups. They are:
> >
> > frmGroup1
> > optYes
> > optNo
> >
> > frmGroup2
> > optYes
> > optNo
> >
> > If frmGroup1, optYes is clicked then frmGroup2, has to be optNo otherwise a
> > message needs to appear telling the user it must be No, the entry is canceled
> > and they stay in frmGroup2 so they can click No
> >
> > ElseIf frmGroup1, optNo is clicked and frmGroup2, optYes is clicked (this is
> > not a mandatory Yes option) a message needs to appear telling the user the
> > item needs to be put on hold. Any other Any other options are fine and they
> > can continue on with the form.
> >
> > Thanks in advance for all the help.
> >
> >