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
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
If you find any spam message in the group, please send an email to:
Ayush Jain @ [email protected] or
Ashish Jain @ [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
