Combine hiding rows with Yes/No validated cells and by cells which are decided by formula.

[razor911]

Hi all,
I have used Mr Excel several times and always found an answer or a lead to an answer. I now have a few months of experience with VB.
So until now I managed by Googling and this is the first time I need to post a problem myself. Let's dig in:

I have a fairly simple code (see below) on a worksheet ("Condition") which hides rows based on a cell with Data Validation pulldown (for instance Yes/No or Ja/Nee in Dutch). I use these type of things all the time.
But now I also have a cell (M35) that isn't populated via a Data Validation but via a formula: it counts the number of empty cells in 3 columns on the sheet which are populated by info from another worksheet ("Products"). If it's 3 empty cells, I don't need that block of info to show. See also a visual below the code.
But if I manipulate the data on "Products"-worksheet and I go from 2 empty cells to 3 on the "Condition"-worksheet, nothing happens. What do I need to do?

I tried the Worksheet_calculate in combination with this but I couldn't have it working. Maybe it will work when I put it in a workbook module that gets triggered everytime anything changes somewhere on the sheets involved, but I need help with that.

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
If Target.Value = "Nee" Then Rows("4:120").EntireRow.Hidden = True
If Target.Value = "Nee, heeft wel al bestaande condities (in hierarchie bijv)" Then Rows("4:1000").EntireRow.Hidden = True
If Target.Value = "Ja" Then Rows("4:120").EntireRow.Hidden = False
End If
If Target.Address = "$C$4" Then
If Target.Value = "Nee" Then Rows("5:120").EntireRow.Hidden = False
If Target.Value = "Ja" Then Rows("5:120").EntireRow.Hidden = True
End If
If Target.Address = "$C$6" Then
If Target.Value = "Hierarchie niveau" Then Rows("8").EntireRow.Hidden = False
If Target.Value = "Klantniveau" Then Rows("8").EntireRow.Hidden = True
End If
If Target.Address = "$C$23" Then
If Target.Value = "Nee" Then Rows("24:41").EntireRow.Hidden = True
If Target.Value = "Ja" Then Rows("24:41").EntireRow.Hidden = False
End If
If Target.Address = "$M$35" Then
If Target.Value = "3" Then Rows("34:38").EntireRow.Hidden = True
If Target.Value <> "3" Then Rows("34:38").EntireRow.Hidden = False
End If
If Target.Address = "$C$46" Then
If Target.Value = "Nee" Then Rows("47:50").EntireRow.Hidden = True
If Target.Value = "Ja" Then Rows("47:50").EntireRow.Hidden = False
End If
End Sub


Example of the data: Feeders 8, 9 and 10 are empty so # of empty cells is 3. I want then the block for feeders 8,9,10 to disappear.

Feeders	5	6	7
Type feeder	Soft Care Line zeepdispenser	Soft Care vouwhanddoekdispenser	Suma Revoflow 3			0
Bruikleen	Ja	Ja	Ja
Installatie door Diversey	Ja	Ja	Ja
Korting of prijs
Feeders	8	9	10
Type feeder						3
Bruikleen
Installatie door Diversey
Korting of prijs

<tbody>
</tbody>

 

[dmt32]

Hi,
Your thinking is right in that Worksheet_Change event does not trigger with formula changes – you need to use the calculate event. Unfortunately, this does not have the Target parameter so a little bit of trickery is needed by using a helper cell.

See if following does what you want:

Place code in your worksheets code page:

 Private Sub Worksheet_Calculate()    
   On Error GoTo exitsub
    With Me.Range("$M$35")
        If .Value <> Me.Cells(1, Me.Columns.Count).Value Then
            Application.EnableEvents = False
            Me.Cells(1, Me.Columns.Count).Value = .Value
            Me.Rows("34:38").EntireRow.Hidden = CBool(.Value = 3)
        End If
    End With
    
exitsub:
    Application.EnableEvents = True
End Sub

The error trap is there to ensure that EnableEvents is reset True should an error occur.

Dave

 

[razor911]

Hi, this is a terribly late reply, but thanks for your help. You set me on the right path. The enable events neede some more research from me but that went fine.