Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a formula in sheet1 range D4 that can show yes or no. I need to do the following based on the output.

    If yes, hide rows 20-37 (show 38-55)
    If No, hide rows 38-55 (show 20-37)

    How can I attach this to a worksheet change event?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you use the Worksheet_Change event, you'd have to check every cell that contributes to the value of cell D4, for this event does not react if the result of a formula changes.
    Alternatively, you could use the Worksheet_Calculate event. This event does occur when a formula is recalculated. But it may have a larger overhead since the event occurs for every calculation.

    Code:
    Private Sub Worksheet_Calculate()
      Range("A20:A37").EntireRow.Hidden = (Range("D4") = "Yes")
      Range("A38:A55").EntireRow.Hidden = Not (Range("D4") = "Yes")
    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That made my w/b go nuts, as there are literally thousands of formula's. Any other approach?

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Actually, I can work off D2 changing, which is a validation list. Whenever D2 changes, evaluate D4. However D4 is evaluated when D2 changes, so could this be done with an intermediate pause?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    As I wrote, you can use the Workbook_Change event but then you have to make the code check the cells that are modified by the user and that contribute to the result of cell D4. Let's say that D4 depends on A1, B3 and C4.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Union(Range("A1"), Range("B3"), Range("C4")), Target) Is Nothing Then
    	Range("A20:A37").EntireRow.Hidden = (Range("D4") = "Yes")
    	Range("A38:A55").EntireRow.Hidden = Not (Range("D4") = "Yes")
      End If
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='VegasNath' post='791376' date='30-Aug-2009 18:33']Actually, I can work off D2 changing, which is a validation list. Whenever D2 changes, evaluate D4. However D4 is evaluated when D2 changes, so could this be done with an intermediate pause?[/quote]
    You can modify my previous reply to use D2.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='791379' date='30-Aug-2009 17:40']You can modify my previous reply to use D2.[/quote]

    Sorry to be a pain, but how? D2 is a validation list with 200 items in the list.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='VegasNath' post='791380' date='30-Aug-2009 18:47']Sorry to be a pain, but how? D2 is a validation list with 200 items in the list.[/quote]
    Change

    If Not Intersect(Union(Range("A1"), Range("B3"), Range("C4")), Target) Is Nothing Then

    to

    If Not Intersect(Union(Range("D2"), Target) Is Nothing Then

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans

    If Not Intersect(Range("D2"), Target) Is Nothing Then

    works.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •