Hide a row including all option buttons

[unch80]

Hi there,

I have a wee problem with hidden rows and hiding the option buttons contained in that row. I have several questions in a sheet which depending on the answer (yes or no) will unhide rows to ask more questions.

All buttons are working OK (and have group frame around them) but when I hide the rows the options buttons remain visible. I got the code below from a previous post which works great BUT it's taking about a minute to run each time and I have about 15 'trigger' which if answered no, unhides several other rows.

Can anyone give me any tips on how I speed up the macro please?

Many thanks
Rach

Dim Rng As Range, Dn As Range
Dim shp As Shape
Set Rng = Range("F31:L105")
For Each shp In ActiveSheet.Shapes
If shp.Type = msoFormControl Then
If shp.FormControlType = xlOptionButton Then
shp.Visible = True
End If
End If
Next shp
For Each Dn In Rng
If Dn.EntireRow.Hidden = True Then

For Each shp In ActiveSheet.Shapes
If shp.Type = msoFormControl Then
If shp.FormControlType = xlOptionButton Then
If Dn.Row >= shp.TopLeftCell.Row And _
Dn.Row <= shp.BottomRightCell.Row Then
shp.Visible = False
End If
End If
End If
Next shp

End If
Next Dn
End Sub

 

[AgentSmith]

Try turning screenupdating off at the start of your macro.

Application.ScreenUpdating = False

Also, are these option buttons causing recalculation? I'm not sure it will help but you could temporarily set calculation to manual too.

Application.Calculation = xlCalculationManual

Then at the end switch these back to defaults (ScreenUpdating to True and Calculation to xlCalculationAutomatic).

Worth a shot anyway!

 

[unch80]

Thanks for that but unfortunately it didn't make a difference. I wonder if it could be something to do with my laptop, rather than the actual code?

Although saying that, other macros seem to work OK...

Thanks anyway!

 

[AgentSmith]

What triggers this code? Is it part of a Worksheet event perhaps?

 

[unch80]

It's triggered when clicking on an option button. If the user selects 'no' then it unhides hidden rows (containing further questions):

If Sheets("Risk Rating - Mitigation").Range("U7") = 2 Then
Rows("74:78").EntireRow.Hidden = False
Call hide_buttons
Else
Rows("74:78").EntireRow.Hidden = True

(The code I'm having problems with is in the macro hide_buttons.)

Do you think it might be because I'm calling up a separate macro? I did it this way because I have 15 trigger questions which determine which rows should remain hidden and which to unhide & I didn't want to have the entire code written for each option button macro.

Cheers

 

[RoryA]

Does this work?

Sub SetOptions()
   Dim lngRow As Long
   Dim opt As OptionButton
   Application.ScreenUpdating = False
   For Each opt In ActiveSheet.OptionButtons
      If Not opt.Visible Then opt.Visible = True
      For lngRow = 31 To 105
         If Rows(lngRow).Hidden = True Then
            If lngRow >= opt.TopLeftCell.Row And _
               lngRow <= opt.BottomRightCell.Row Then
               opt.Visible = False
               Exit For
            End If
         End If
      Next lngRow
   Next opt
   Application.ScreenUpdating = True
End Sub

 

[unch80]

Much better - many thanks!

Now it only takes a few seconds rather than 30 or more!

Thanks very much for all your help!!