Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Rows based on value of a cell | Excel Programming | |||
Hide Rows Based on Cell Result | Excel Programming | |||
hide rows based on cell value | New Users to Excel | |||
hide rows based on value in cell | Excel Discussion (Misc queries) | |||
How to Hide Rows based on conditional value in cell | Excel Programming |