Conditional Expression For Radio Button

L

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,171
Office Version
  1. 365
Platform
  1. Windows
I have two controls in my form and each has a radio button. In control 1 the user selects "No", then I want control 2 to automatically select "No" for the radio button. If the user selects "Yes" in control 1 then I want control 2 to do nothing and have the user the option to pick "Yes" or "No"

Will I have to build IIF statement in control 2 in the Validation Rule property? Or something in the Event properties?
 
Last edited:
Sort by date Sort by votes
Joe4 said:
You are missing a bunch of "End Sub" statements. Note that you cannot next Subs inside of other Subs. You need to have an "End Sub" at the end of each Sub, before you start the next Sub.

I really am not sure what you are trying to do with [Text101]. I assume that it is some Text box outside of your Option Groups? In any event, you should be able to combine the two Frame103_AfterUpdate Event procedures like this:
Code: 
Private Sub Frame103_AfterUpdate()


    Select Case Me![Frame103]
        Case 1
            Me![Text101] = "Y"
        Case 2
            Me![Text101] = "N"
            Me.Frame112 = 2
            Me.Frame112.Locked = True
        Case 3
            Me![Text101] = "TBD"
    End Select


End Sub
Click to expand...

Yeah the text box is outside my Option Groups. The text box points to the specific field in my Table (under the control source) and is set to invisible so it does not show on my Form. Do you think the textbox is necessary? Can I use the Option Groups control source to point to the field in my Table?

Your code worked pretty good, except for one thing:

If the user accidently selects "No" in Frame103 then reselects "Yes" or "TBD", Frame112 is still locked on "No". Frame112 should be unlocked if "Yes" or "TBD" is reselected in Frame103.

Thanks.
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Take a close look at the code above. Notice the line that is locking Frame112 when No is selected.
Simply add a line just like that under the Yes selection that unlocks Frame112 (Locked=False instead of True).
 
Upvote 0
Joe4 said:
Take a close look at the code above. Notice the line that is locking Frame112 when No is selected.
Simply add a line just like that under the Yes selection that unlocks Frame112 (Locked=False instead of True).
Click to expand...


Thanks I got it. However, If "Yes" or "TBD" is selected in Frame103 then user should not be able to select "No" in in Frame112. Here is my updated code:

Code: 
Private Sub Frame103_AfterUpdate()
    Select Case Me![Frame103]
        Case 1
            Me![Text101] = "Y"
            Me.Frame112.Locked = False
        Case 2
            Me![Text101] = "N"
            Me.Frame112 = 2
            Me.Frame112.Locked = True
        Case 3
            Me![Text101] = "TBD"
            Me.Frame112.Locked = False
    End Select
End Sub
 
Upvote 0
Good job! I thought if you looked at the code, you would pick up on the methodology and figure out how to adjust it for those other options.
 
Upvote 0
Joe4 said:
Good job! I thought if you looked at the code, you would pick up on the methodology and figure out how to adjust it for those other options.
Click to expand...

I can't seem to figure how to be able to select "TBD" in Case 1 and Case 3 (see below). I was able to only select only "Yes". How do I say or/and in the VBA code?

Code: 
Private Sub Frame103_AfterUpdate()
    Select Case Me![Frame103]
        Case 1
            Me![Text101] = "Y"
            Me.Frame112 = 1
            Me.Frame112.Locked = True

        Case 2
            Me![Text101] = "N"
            Me.Frame112 = 2
            Me.Frame112.Locked = True
        Case 3
            Me![Text101] = "TBD"
            Me.Frame112 = 1
            Me.Frame112.Locked = True
                               
            
    End Select
End Sub
 
Upvote 0
legalhustler said:
I can't seem to figure how to be able to select "TBD" in Case 1 and Case 3 (see below). I was able to only select only "Yes". How do I say or/and in the VBA code?

Code: 
Private Sub Frame103_AfterUpdate()
    Select Case Me![Frame103]
        Case 1
            Me![Text101] = "Y"
            Me.Frame112 = 1
            Me.Frame112.Locked = True

        Case 2
            Me![Text101] = "N"
            Me.Frame112 = 2
            Me.Frame112.Locked = True
        Case 3
            Me![Text101] = "TBD"
            Me.Frame112 = 1
            Me.Frame112.Locked = True
                               
            
    End Select
End Sub
Click to expand...

Also tried the below code, but I was able to select all three options in Frame112, not just "Yes" and "TBD" as I need.

Code: 
Private Sub Frame103_AfterUpdate()
    Select Case Me![Frame103]
        Case 1
            Me![Text101] = "Y"
            Me.Frame112 = 1
            Me.Frame112.Locked = False
            Me.Frame112 = 2
            Me.Frame112.Locked = True
            Me.Frame112 = 3
            Me.Frame112.Locked = False
        Case 2
            Me![Text101] = "N"
            Me.Frame112 = 2
            Me.Frame112.Locked = True
        Case 3
            Me![Text101] = "TBD"
            Me.Frame112 = 1
            Me.Frame112.Locked = False
            Me.Frame112 = 2
            Me.Frame112.Locked = True
            Me.Frame112 = 3
            Me.Frame112.Locked = False
                    
            
    End Select
End Sub
 
Last edited:
Upvote 0
Also if the user selects "Yes" or "TBD" in Frame103, Frame112 should not show a selection (a checkmark). Frame112 should not allow a "No" to be selected AFTER "Yes" or "TBD" is selected in Frame103.
 
Upvote 0
First, it is important to understand how a SELECT CASE statement works? It is sort of like an expanded IF ... THEN statement (see MS Access: Case Statement).

Code: 
Select Case Me![Frame103]
means get the value that Frame103 is equal to (or another way of saying that is what is selected for that Option Group?) Each selection has a numeric value to it, 1="Yes", 2="No", 3="Yes". You see this when you set up the Option Group using the Wizard
Code: 
   Case 1
means that if Frame103 equals 1 (that is, "Yes" is selected), then perform the following actions found underneath it.
Code: 
        Me.Frame112=1
is telling it to select Option 1 ("Yes") from Frame112. If you want "TBD" to be selected in Frame112, you would set it equal to 3.

Under each "Case" line, you should only have one "Me.Frame112..." line and one"Me.Frame112.Locked..." line. If you have multiple ones (like in your last code, each successive one is just overwriting the previous (so the previous ones serve no purpose then).

If you want Frame112 to be set to "TBS" and Frame112 to be locked when "Yes" is selected from Frame103, that block would just look like:
Code: 
Select Case Me![Frame103] 
    Case 1
        Me![Text101]="Y"
        Me.Frame112=3
        Me.Frame112.Locked=True
and that is it, nothing else underneath Case 1.

Does that make sense? It is important to understand how all this works, so you can make the necessary adjustments.
 
Upvote 0
Joe4 said:
First, it is important to understand how a SELECT CASE statement works? It is sort of like an expanded IF ... THEN statement (see MS Access: Case Statement).

Code: 
Select Case Me![Frame103]
means get the value that Frame103 is equal to (or another way of saying that is what is selected for that Option Group?) Each selection has a numeric value to it, 1="Yes", 2="No", 3="Yes". You see this when you set up the Option Group using the Wizard
Code: 
   Case 1
means that if Frame103 equals 1 (that is, "Yes" is selected), then perform the following actions found underneath it.
Code: 
        Me.Frame112=1
is telling it to select Option 1 ("Yes") from Frame112. If you want "TBD" to be selected in Frame112, you would set it equal to 3.

Under each "Case" line, you should only have one "Me.Frame112..." line and one"Me.Frame112.Locked..." line. If you have multiple ones (like in your last code, each successive one is just overwriting the previous (so the previous ones serve no purpose then).

If you want Frame112 to be set to "TBS" and Frame112 to be locked when "Yes" is selected from Frame103, that block would just look like:
Code: 
Select Case Me![Frame103] 
    Case 1
        Me![Text101]="Y"
        Me.Frame112=3
        Me.Frame112.Locked=True
and that is it, nothing else underneath Case 1.

Does that make sense? It is important to understand how all this works, so you can make the necessary adjustments.
Click to expand...


Yeah makes sense. Thanks for the link (looks like alot of good info).

The question is if a user selects "Yes" or "TBD" in Frame103 , how do I give them the option to only select between "Yes" and "TBD" in Frame112? Another way of saying it, they should not have the option to select "No" in Frame112. In addition, when the user selects "Yes" or "TBD" in Frame103, I do not want Frame112 to display a checkmark/bubble. This means that the user cannot pick "No" if they click on it in Frame112. Read this slowly and hopefully it makes sense.
 
Last edited:
Upvote 0
My previous post was more about post #16 (I hadn't seen post #17 when I posted it). The unnecessary redundancy in the code is what caught my eye there, so that is what I was focusing on before moving forward (making sure we get the initial stuff straight before going to new stuff).

The question is if a user selects "Yes" or "<acronym title="To be discussed" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">TBD</acronym>" in Frame103 , how do I give them the option to only select between "Yes" and "<acronym title="To be discussed" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">TBD</acronym>" in Frame112?
Click to expand...
OK, now we are getting in to some fun stuff! So basically, you want to Disable the "No" option in Frame112 if they select "Yes" or "TBD" in Frame103. So, first thing is go into Design View, and click on the "No" radio button in Frame112 to see what it is named. If you haven't changed it, then the name will start with "Option". So it might be something like Option16.

So, in our code under both Case 1 and Case 3, you will want to add the following line:
Code: 
Me.Option16.Enabled = False
You will probably also want to add code to Case 2 that says:
Code: 
Me.Option16.Enabled = True
if you want "No" re-enabled again if they select "No" in Frame103.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA code for Next button to move to each record in Textbox1
Replies
2
Views
214
welshgasman
welshgasman
C
  • Question
Access application gives 'Not responding' at this code line. Having difficulty in resolving
Replies
6
Views
243
chazrab
C
K
  • Question
Fill in form text box based on selection from a combobox
Replies
1
Views
598
Micron
M
D
  • Solved
Combo Box changing customer name to random number
Replies
1
Views
302
DarkSmile
D
MCTampa
  • Solved
Mixing Number and Text Query Criteria from a Form
Replies
5
Views
432
MCTampa
MCTampa

Forum statistics

Threads
1,215,374
Messages
6,124,571
Members
449,173
Latest member
Kon123

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top