Multiple option buttons - Need VBA to disable second group of option buttns based on respo

Disable Option button.jpgDear All,

I am creating a survey in Excel 2010, where I need to disable the second group of option buttons based on response given/clicked in first group of option buttons.

What I want: If the user selects 'NA' radio/option button, assign its value as '0', and disable all corresponding options in the second group (option buttons 2).

My worksheet looks like this ... (attached workbook)



If only 'NA' is checked in Option buttons 1 then all corresponding options group 2 should get disabled, else user should be able to check/choose option in both groups of option buttons.

I have already created the option groups but unable to disable based on selection in the first group)

Unable to upload my workbook as file attachment in this forum.

Thanks for reading and look for your help (VBA code) to above problem.

Hi,

it's all about the naming of the option buttons:
'Naming convention (e.g. "OP_A1Q1R1C1"):Two sets of OptionButtons.xls
'OP : optionbutton
'_ : placeholder
'A1 : attribute 1
'Q1 : question 1
'R1 : response 1
'C1 : code 1

Name your option buttons accordingly and assign all OP's from response 1 to the macro "OP_Click", it will dis-/enable the corresponding option buttons in response 2, change the back color of the cell and set the value to 0:

contains:

Option Explicit

'Naming convention:
'OP : optionbutton
'_  : placeholder
'A1 : attribute 1
'Q1 : question 1
'R1 : response 1
'C1 : code 1

Sub OP_Click()

    Dim oOpClicked As Shape

    'get clicked option button
    Set oOpClicked = ActiveSheet.Shapes(Application.Caller)
    
    'check if response = 1
    If Mid(oOpClicked.Name, InStr(oOpClicked.Name, "R") + 1, 1) = "1" Then
        Application.ScreenUpdating = False
        
        'dis-/enable corresponding op's in response 2
        OP_Enable Left(oOpClicked.Name, 7), (Mid(oOpClicked.Name, InStr(oOpClicked.Name, "C") + 1, 1) <> "0")

        Application.ScreenUpdating = True
    End If
End Sub

Sub OP_Enable(strOpName As String, blnEnabled As Boolean)
    Dim oActive As Worksheet:    Set oActive = ActiveSheet
    Dim oOptButton As OptionButton
    Dim iButtonCount As Integer
    
    For iButtonCount = 1 To oActive.OptionButtons.Count
        Set oOptButton = oActive.OptionButtons(iButtonCount)
        With oOptButton
            'if same attribute and question
            If StrComp(Left(.Name, 7), strOpName, vbBinaryCompare) = 0 Then
                'if response 2
                If StrComp(Mid(.Name, InStr(.Name, "R") + 1, 1), "2", vbBinaryCompare) = 0 Then
                    'set enabled property
                    If .Enabled <> blnEnabled Then .Enabled = blnEnabled
                    If blnEnabled Then
                        .TopLeftCell.Interior.ColorIndex = 2
                    Else
                        .Value = 0
                        .TopLeftCell.Interior.ColorIndex = 16
                    End If
                End If
            End If
        End With
    Next iButtonCount

End Sub

1. Please do not quote entire posts, it makes the thread unreadable.

2.

Originally Posted by ex123

I tried running the macro, but getting run time vba error.

You got the error in the example file? which error number in which line?

3. I'll have a look at the complete file.

So,

the following works fine for me for the first 2 questions: MultipleOptionButtons_Dis-Enable_Forum_post.xlsm

Check the names of the OP buttons in the name box by right-clicking them, your part is to set the names of all the other OP buttons according to the naming convention in post #2 and assign the "Critically" buttons (1-4 and NA) to the macro "OP_Click()"

If you still have the runtime error I will have a look again, as I said, I need the error number and the line in which the error occurs.

Thanks for your support. I am not getting any error, but it's not dis/enabling.

Steps followed,
1. Named all option buttons in response group 1 as per naming convention in post #2 (have I done correct?!)
2. Assigned macro OP_Click() to all option buttons in response group 1

There is no error while running macro, but options in response group 2 are not disabled when a question is marked 'NA'.

For your reference, I am attaching my workbook. Let me know if I need to do few more settings....

Hi,

the names in response group 1 are assigned correctly, well done.

Seems like I forgot to tell you to also set names for response group 2 so the code will identify the OP buttons by their name. The name for the next button in response group 2 question 3 code 1 will be "OP_A1Q3R2C1"

Hi,

please check this: Multiple_OptionButtons_Dis-Enable_EXForum_post4.xlsm

There was a problem with code as it was not able to handle question numbers of 2 digits. I renamed all OP's accordingly and adapted the code.
Please note that for performance reasons there will only be 4 OP's changed in response 2, if you add one OP there you must adapt this in the code.