Deactivate option button and cell

Ravi321

New Member
Joined
Jul 9, 2013
Messages
5
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
.

Sample workbook
https://www.dropbox.com/s/j36csnsj3gcfbbq/Option button deactivation.xlsm

<input id="ext_msg_elm" type="hidden">
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
If you go into Design Mode (Developer tab and click on the Ruler, Pencil and Set Square icon) then double-click one of your checkboxes it will take you to the macro editing screen. You will automatically be given the first and last line of a macro which you must not change. In between those two lines you can add vba commands. You need to change the Enabled property of the checkboxes to False to disable them and true to enable them. For instance:

Code:
Private Sub OptionButton1_Click()
    OptionButton2.Enabled = False
    MsgBox "OptionButton1_Click() False"
End Sub
The above code will prevent changes being made to OptionButton2.

I suspect that you will also need a WorksheetOpen event to make the correct initial settings for these checkboxes because if a checkbox is not actually clicked then the actions will not be performed and everything will start enabled.

You can find out which properties are available by right-clicking a checkbox and selecting properties.

I hope this helps.
 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,240
For starters Group OptionButton1 and OptionButton2 so that only one of them may be selected. This can be set from the control properties. Then in the Sheet Code module:

Code:
Private Sub OptionButton1_Click()
testValues
End Sub

Private Sub OptionButton2_Click()
testValues
End Sub

Sub testValues()
If OptionButton1.Value Then
    OptionButton3.Enabled = True
    OptionButton4.Enabled = True
    OptionButton5.Enabled = True
    OptionButton6.Enabled = False
    OptionButton7.Enabled = False
Else
    OptionButton3.Enabled = False
    OptionButton4.Enabled = False
    OptionButton5.Enabled = False
    OptionButton6.Enabled = True
    OptionButton7.Enabled = True
End If
End Sub
 

Ravi321

New Member
Joined
Jul 9, 2013
Messages
5
Thanks a lot Teeroy and RickXL for your reply, it helped me a a lot :) <input id="ext_msg_elm" type="hidden">
 

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,531
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top