Hide rows using VBA based on cell value
 

I have also posted this in another group by mistake. Apologies.


Hi,

I have various drop downs on a worksheet. What I want to do is if the
value of B3 is 2 then unhide rows 4 & 5 along with drop down 7 and
drop down 8. If the value of B3 isn't 2 then hide the rows and drop
downs. I also want to do the same thing with rows 15-18 and drop
downs
3, 4,5 if the value of B14 is 2 etc. I can get it to work fine for
one
of these with this code:


================================================== ====================


Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False


With Sheets("form")


If Range("B14").Value = 2 Then
Rows("15:18").EntireRow.Hidden = False
ActiveSheet.Shapes("Drop Down 3").Visible = True
ActiveSheet.Shapes("Drop Down 4").Visible = True
ActiveSheet.Shapes("Drop Down 5").Visible = True


Else
Rows("15:18").EntireRow.Hidden = True
ActiveSheet.Shapes("Drop Down 3").Visible = False
ActiveSheet.Shapes("Drop Down 4").Visible = False
ActiveSheet.Shapes("Drop Down 5").Visible = False
End If


End With


End Sub


================================================== ========================


but when I adapt the above code to include the other option, so my
code reads:


================================================== ====================


Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False


With Sheets("form")


If Range("B3").Value = 2 Then
Rows("4:5").EntireRow.Hidden = False
ActiveSheet.Shapes("Drop Down 7").Visible = True
ActiveSheet.Shapes("Drop Down 8").Visible = True


Else
Rows("4:5").EntireRow.Hidden = True
ActiveSheet.Shapes("Drop Down 7").Visible = False
ActiveSheet.Shapes("Drop Down 8").Visible = False
End If


If Range("B14").Value = 2 Then
Rows("15:18").EntireRow.Hidden = False
ActiveSheet.Shapes("Drop Down 3").Visible = True
ActiveSheet.Shapes("Drop Down 4").Visible = True
ActiveSheet.Shapes("Drop Down 5").Visible = True


Else
Rows("15:18").EntireRow.Hidden = True
ActiveSheet.Shapes("Drop Down 3").Visible = False
ActiveSheet.Shapes("Drop Down 4").Visible = False
ActiveSheet.Shapes("Drop Down 5").Visible = False
End If


End With


End Sub


================================================== ========================


it seems to be looping or recalculating over and over and I have to
hit Esc to get it to stop.


I can't work out why it can do one set fine but not 2.


Can anyone help?


Thanks