Hide rows using VBA based on cell value

S

Shazbot79

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top