Deactivate option button and cell

[mani_bbc05]

Hi friends,

What I need to do is,
- If fruits is selected in question 1,
then automatically OptionButton3, OptionButton4 & OptionButton5 should get activated
and OptionButton6, OptionButton7 and cell E9 should get deactivated (means user should be restricted to use them).
- if vegetables is selected in qusetion1 ,
then automatically OptionButton6, OptionButton7 and cell E9 should get activated
and OptionButton3, OptionButton4 & OptionButton5 should get deactivated

I have used activeX option buttons as that is the requirement from my boss.
I tried to use If-Then-Else but its not working .

Link for sample file

 

[Sam Mathai Chacko]

To keep away the nuances of sheet protection and cell locking, I would suggest using a text box.

And just use this code in the respective sheet module

Private Sub OptionButton1_Change()

    Me.OLEObjects(Array("OptionButton3", "OptionButton4", "OptionButton5")).Enabled = Me.OptionButton1.Value
    Me.OLEObjects(Array("OptionButton6", "OptionButton7", "TextBox1")).Enabled = Not Me.OptionButton1.Value
    
End Sub

 

[mani_bbc05]

Hi Sam. Thanks a lot, exactly this is the thing which i was looking for.

 

[mani_bbc05]

Hi Sam, if you don't mind can you please tell me

• why we are using Me.OLEObjects and not just Me.objects
• what is the use of Me

 

[Abhijeet R. Joshi]

ME.objects stands for the userform that you have created(if any) in your module.

 

[Sam Mathai Chacko]

mani_bbc05, sorry for being cryptic there.

the Me that I used for your solution was referring to the parent sheet where you had those option buttons. Any class object that offers a code module can be referred to within that code module as Me

In an Excel VBA project, the code modules for Sheet, UserForm, Workbook and custom class allow for using Me

Now, the activex controls like option buttons, text boxes etc fall under the OLEObjects collection. On the other hand, there is no collection called Objects, but only an Object which is used to define the class

 

[mani_bbc05]

mani_bbc05, sorry for being cryptic there.

the Me that I used for your solution was referring to the parent sheet where you had those option buttons. Any class object that offers a code module can be referred to within that code module as Me

In an Excel VBA project, the code modules for Sheet, UserForm, Workbook and custom class allow for using Me

Now, the activex controls like option buttons, text boxes etc fall under the OLEObjects collection. On the other hand, there is no collection called Objects, but only an Object which is used to define the class

Thanks a lot Sam for the explanation .​