AIDSNGO
05-22-2012, 08:49 AM
Hi All,
I have a spreadsheet (too large to upload) that has a long series of questions in it with yes or no ActiveX radio buttons next to them. When they are marked "no" I have written the code so that the rows between it and the next question hide.
This all works fine, but I have another Sub that unhides the first two rows of each question, but not these rows that have been hidden by the radio button. Unfortunately, given the number of questions that I have in the spreadsheet, having a series of steps that unhides each section separately is really bogging down the spreadsheet and making it run very slowly. Is there a way to have a series of non-contiguous rows all unhide in one process? Or some other way to speed this process up? I tried using something like
Rows("302:303,310:311").EntireRow.Hidden = False
But I get the error message "Run-time error '13': Type mismatch.
Help?
A sample of the VBA:
Private Sub OptionButton3_Click()
If OptionButton3.Value = True Then
Rows("208:213").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton4_Click()
If OptionButton4.Value = True Then
Rows("208:213").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton5_Click()
If OptionButton5.Value = True Then
Rows("216:220").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton6_Click()
If OptionButton6.Value = True Then
Rows("216:220").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton7_Click()
If OptionButton7.Value = True Then
Rows("223:227").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton8_Click()
If OptionButton8.Value = True Then
Rows("223:227").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton17_Click()
If OptionButton17.Value = True Then
Rows("230:233").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton18_Click()
If OptionButton18.Value = True Then
Rows("230:233").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton19_Click()
If OptionButton19.Value = True Then
Rows("236:240").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton20_Click()
If OptionButton20.Value = True Then
Rows("236:240").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton21_Click()
If OptionButton21.Value = True Then
Rows("243:246").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton22_Click()
If OptionButton22.Value = True Then
Rows("243:246").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton23_Click()
If OptionButton23.Value = True Then
Rows("249:254").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton24_Click()
If OptionButton24.Value = True Then
Rows("249:254").EntireRow.Hidden = False
End If
End Sub
If target.Address = "$L$190" Then
Select Case Worksheets("Instructions and Worksheet").Range("L190").Value
Case "0"
Rows("203:477").EntireRow.Hidden = True
Case "1"
Rows("257:477").EntireRow.Hidden = True
Rows("203:207").EntireRow.Hidden = False
Rows("214:215").EntireRow.Hidden = False
Rows("221:222").EntireRow.Hidden = False
Rows("228:229").EntireRow.Hidden = False
Rows("234:235").EntireRow.Hidden = False
Rows("241:242").EntireRow.Hidden = False
Rows("247:248").EntireRow.Hidden = False
Rows("255:256").EntireRow.Hidden = False
Case "2"
Rows("312:477").EntireRow.Hidden = True
Rows("203:207").EntireRow.Hidden = False
Rows("214:215").EntireRow.Hidden = False
Rows("221:222").EntireRow.Hidden = False
Rows("228:229").EntireRow.Hidden = False
Rows("234:235").EntireRow.Hidden = False
Rows("241:242").EntireRow.Hidden = False
Rows("247:248").EntireRow.Hidden = False
Rows("255:256").EntireRow.Hidden = False
Rows("257:262").EntireRow.Hidden = False
Rows("269:270").EntireRow.Hidden = False
Rows("276:277").EntireRow.Hidden = False
Rows("283:284").EntireRow.Hidden = False
Rows("289:290").EntireRow.Hidden = False
Rows("296:297").EntireRow.Hidden = False
Rows("302:303").EntireRow.Hidden = False
Rows("310:311").EntireRow.Hidden = False
I have a spreadsheet (too large to upload) that has a long series of questions in it with yes or no ActiveX radio buttons next to them. When they are marked "no" I have written the code so that the rows between it and the next question hide.
This all works fine, but I have another Sub that unhides the first two rows of each question, but not these rows that have been hidden by the radio button. Unfortunately, given the number of questions that I have in the spreadsheet, having a series of steps that unhides each section separately is really bogging down the spreadsheet and making it run very slowly. Is there a way to have a series of non-contiguous rows all unhide in one process? Or some other way to speed this process up? I tried using something like
Rows("302:303,310:311").EntireRow.Hidden = False
But I get the error message "Run-time error '13': Type mismatch.
Help?
A sample of the VBA:
Private Sub OptionButton3_Click()
If OptionButton3.Value = True Then
Rows("208:213").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton4_Click()
If OptionButton4.Value = True Then
Rows("208:213").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton5_Click()
If OptionButton5.Value = True Then
Rows("216:220").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton6_Click()
If OptionButton6.Value = True Then
Rows("216:220").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton7_Click()
If OptionButton7.Value = True Then
Rows("223:227").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton8_Click()
If OptionButton8.Value = True Then
Rows("223:227").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton17_Click()
If OptionButton17.Value = True Then
Rows("230:233").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton18_Click()
If OptionButton18.Value = True Then
Rows("230:233").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton19_Click()
If OptionButton19.Value = True Then
Rows("236:240").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton20_Click()
If OptionButton20.Value = True Then
Rows("236:240").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton21_Click()
If OptionButton21.Value = True Then
Rows("243:246").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton22_Click()
If OptionButton22.Value = True Then
Rows("243:246").EntireRow.Hidden = False
End If
End Sub
Private Sub OptionButton23_Click()
If OptionButton23.Value = True Then
Rows("249:254").EntireRow.Hidden = True
End If
End Sub
Private Sub OptionButton24_Click()
If OptionButton24.Value = True Then
Rows("249:254").EntireRow.Hidden = False
End If
End Sub
If target.Address = "$L$190" Then
Select Case Worksheets("Instructions and Worksheet").Range("L190").Value
Case "0"
Rows("203:477").EntireRow.Hidden = True
Case "1"
Rows("257:477").EntireRow.Hidden = True
Rows("203:207").EntireRow.Hidden = False
Rows("214:215").EntireRow.Hidden = False
Rows("221:222").EntireRow.Hidden = False
Rows("228:229").EntireRow.Hidden = False
Rows("234:235").EntireRow.Hidden = False
Rows("241:242").EntireRow.Hidden = False
Rows("247:248").EntireRow.Hidden = False
Rows("255:256").EntireRow.Hidden = False
Case "2"
Rows("312:477").EntireRow.Hidden = True
Rows("203:207").EntireRow.Hidden = False
Rows("214:215").EntireRow.Hidden = False
Rows("221:222").EntireRow.Hidden = False
Rows("228:229").EntireRow.Hidden = False
Rows("234:235").EntireRow.Hidden = False
Rows("241:242").EntireRow.Hidden = False
Rows("247:248").EntireRow.Hidden = False
Rows("255:256").EntireRow.Hidden = False
Rows("257:262").EntireRow.Hidden = False
Rows("269:270").EntireRow.Hidden = False
Rows("276:277").EntireRow.Hidden = False
Rows("283:284").EntireRow.Hidden = False
Rows("289:290").EntireRow.Hidden = False
Rows("296:297").EntireRow.Hidden = False
Rows("302:303").EntireRow.Hidden = False
Rows("310:311").EntireRow.Hidden = False