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
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