Drop down questions with radio buttons

riedyp

Board Regular
Joined
Feb 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Hello,
I am trying to complete a form that originally shows 6 questions, each with 3 radio buttons assigned a macro and grouped in a "group box". The macros will reveal a a row to a follow up question that may have another set of radio buttons grouped with a "group box." My issue is that when everything is hidden under its question(or rolled up) the group boxes and radio buttons cluster and then the buttons start to think they are in different group boxes i believe. Is there any way to fix this?
VBA Code:
'Radio Button


'Private Sub OptionButton1_Click()
 '   Set Q7 = Worksheets("Radio button").Shapes("OptionButton1")
  '  If Q7.ControlFormat.Value = xlOn Then
   ' Me.Rows("11").Visible = xlRowsHidden
    'Me.Rows("10").Visible = xlRowsVisible
    'Else
    'Me.Rows("11").Visible = xlRowsVisible
    'Me.Rows("10").Visible = xlRowsHidden
    'End If
  
'End Sub



Sub Yes_1() ' Yes Macro for Q1
    Rows("10:10").Hidden = False
    Rows("11:11").Hidden = True
    Rows("12:12").Hidden = True
End Sub
Sub No_1() ' No Macro for Q1
    Rows("10:10").Hidden = True
    Rows("11:11").Hidden = False
    Rows("12:12").Hidden = True
End Sub
Sub NA_1() 'Na Macro for Q1
    Rows("11:11").Hidden = True
    Rows("10:10").Hidden = True
    Rows("12:12").Hidden = False
End Sub
'''''''''''''''''''''''''''''''''''''''''''Q10

Sub Yes_4() ' Yes Macro for Q4
    Rows("19:20").Hidden = False
    Rows("21:23").Hidden = True
    Rows("24:24").Hidden = False
    Rows("25:27").Hidden = True
    Rows("28").Hidden = True
    
    ActiveSheet.Shapes("Option Button 115").Visible = True 'Yes Sub 1
    ActiveSheet.Shapes("Option Button 116").Visible = True 'No  Sub 1
    ActiveSheet.Shapes("Option Button 117").Visible = True 'N/A Sub 1
    
    ActiveSheet.Shapes("Option Button 120").Visible = True 'Yes Sub 2
    ActiveSheet.Shapes("Option Button 121").Visible = True 'No  Sub 2
    ActiveSheet.Shapes("Option Button 122").Visible = True 'N/A Sub 2
End Sub
Sub No_4() ' No Macro for Q4
    Rows("19:20").Hidden = True
    Rows("21:23").Hidden = True
    Rows("24:24").Hidden = True
    Rows("25:27").Hidden = True
    Rows("28").Hidden = True
    
    ActiveSheet.Shapes("Option Button 115").Visible = False 'Yes Sub 1
    ActiveSheet.Shapes("Option Button 116").Visible = False 'No  Sub 1
    ActiveSheet.Shapes("Option Button 117").Visible = False 'N/A Sub 1
    
    ActiveSheet.Shapes("Option Button 120").Visible = False 'Yes Sub 2
    ActiveSheet.Shapes("Option Button 121").Visible = False 'No  Sub 2
    ActiveSheet.Shapes("Option Button 122").Visible = False 'N/A Sub 2
End Sub
Sub NA_4() ' NA Macro for Q4
    Rows("19:20").Hidden = True
    Rows("21:23").Hidden = True
    Rows("24:24").Hidden = True
    Rows("25:27").Hidden = True
    Rows("28").Hidden = False
    
    ActiveSheet.Shapes("Option Button 115").Visible = False 'Yes Sub 1
    ActiveSheet.Shapes("Option Button 116").Visible = False 'No  Sub 1
    ActiveSheet.Shapes("Option Button 117").Visible = False 'N/A Sub 1
    
    ActiveSheet.Shapes("Option Button 120").Visible = False 'Yes Sub 2
    ActiveSheet.Shapes("Option Button 121").Visible = False 'No  Sub 2
    ActiveSheet.Shapes("Option Button 122").Visible = False 'N/A Sub 2
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''Q10 Subquestion 1
Sub Yes_5() ' Yes Macro for Q4 Sub 1
    Rows("21:22").Hidden = False
    Rows("23").Hidden = True
End Sub
Sub No_5() ' No Macro for Q4 Sub 1
    Rows("21:22").Hidden = True
    Rows("23").Hidden = True
End Sub
Sub NA_5() ' NA Macro for Q4 Sub 1
    Rows("21:22").Hidden = True
    Rows("23").Hidden = False
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''Q10 Subquestion 2
Sub Yes_6() ' Yes Macro for Q10 Sub 2
    Rows("25:26").Hidden = False 'Yes
    Rows("27").Hidden = True 'NA
End Sub
Sub No_6() ' No Macro for Q10 Sub 2
    Rows("25:26").Hidden = True 'Yes
    Rows("27").Hidden = True 'NA
End Sub
Sub NA_6() ' NA Macro for Q10 Sub 2
    Rows("25:26").Hidden = True 'Yes
    Rows("27").Hidden = False 'NA
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''Q11
Sub Yes_7() ' NO Macro for Q11
    Rows("31").Hidden = True 'Yes
End Sub
Sub No_7() ' NO Macro for Q11
    Rows("31").Hidden = False 'No
End Sub
Sub NA_7() ' NA Macro for Q11
    Rows("31").Hidden = True 'NA
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''Q12
Sub Yes_8() ' Yes Macro for Q12
    Rows("34").Hidden = False 'Yes
End Sub
Sub No_8() ' No Macro for Q12
    Rows("34").Hidden = True 'No
End Sub
Sub NA_8() ' NA Macro for Q12
    Rows("34").Hidden = True 'NA
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''Hide Group Boxes
Sub ToggleVisible()
Dim myGB As GroupBox
For Each myGB In ActiveSheet.GroupBoxes
myGB.Visible = False
Next myGB
End Sub
 

Attachments

  • dropdown.PNG
    dropdown.PNG
    42.7 KB · Views: 17
  • notcorrect.PNG
    notcorrect.PNG
    13.8 KB · Views: 17

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Did you see my reply in your other thread about not using Group Boxes?


Instead, set the GroupName property of every Option Button, giving the same GroupName to each group of option buttons.
 
Upvote 0
I did but my issue is that I needed to use Form Control option buttons to have a cell link to another to change its color. I figured out my issue, but is it possible to have a Activex option button control the formatting of another cell?
 
Upvote 0

Forum statistics

Threads
1,223,954
Messages
6,175,603
Members
452,660
Latest member
Zatman

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