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: 6
  • notcorrect.PNG
    notcorrect.PNG
    13.8 KB · Views: 6

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
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.
 

riedyp

Board Regular
Joined
Feb 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,795
Messages
5,638,366
Members
417,023
Latest member
Zimbo38

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
Top