Create button that adds rows w/ formatting

riedyp

Board Regular
Joined
Feb 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a tab in my workbook that has rows that hide/unhide based off of which radio button is selected. I am wondering if there is a way to use vba to create buttons in a template to make this easy to recreate. If not, do you have any suggestions on how I could go about this smoothly? I have attached a picture of the finished sheet I have with its code below. My other attachment is just what I was thinking on my button layout. Please feel free to add any suggestions you may have.
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 Q7
    Rows("10:10").hidden = False
    Rows("11:11").hidden = True
    Rows("12:12").hidden = True
End Sub
Sub No_1() ' No Macro for Q7
    Rows("10:10").hidden = True
    Rows("11:11").hidden = False
    Rows("12:12").hidden = True
End Sub
Sub NA_1() 'Na Macro for Q7
    Rows("11:11").hidden = True
    Rows("10:10").hidden = True
    Rows("12:12").hidden = False
End Sub
'''''''''''''''''''''''''''''''''''''''''''Q10

Sub Yes_4() ' Yes Macro for Q10
    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 Q10
    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 Q10
    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 Q10 Sub 1
    Rows("21:22").hidden = False
    Rows("23").hidden = True
End Sub
Sub No_5() ' No Macro for Q10 Sub 1
    Rows("21:22").hidden = True
    Rows("23").hidden = True
End Sub
Sub NA_5() ' NA Macro for Q10 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

  • forum2.PNG
    forum2.PNG
    8.2 KB · Views: 9
  • forum3.PNG
    forum3.PNG
    61.5 KB · Views: 11

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Watch MrExcel Video

Forum statistics

Threads
1,127,078
Messages
5,622,542
Members
415,906
Latest member
MrBoomer2U

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