Hide/unhide row based off result of IF formula in cell

[kmiceski]

Hi Everyone,

I am very new to VBA and I am trying to make a row disappear based off the result of an 'IF' formula in a cell.

So if the the formula in (L86) comes back as 'Fail', I would like row 87 to appear however if the formual comes back as "" (Blank) i want the row to disappear.

This is my formula for L86: =IFERROR(IF(AND(L83>200,L85<=7,L25="Yes"),"","Fail"),"") which works fine but i can't get the VBA to work:

This is my current code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = ("$C$67") Then
    Sheet11.Unprotect Password:="XX"
        If Target.Text = "All in One" Then
             Rows("81").EntireRow.Hidden = True
        Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "Credit Card" Then
             Rows("81").EntireRow.Hidden = True
        Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "Personal Loan" Then
             Rows("81").EntireRow.Hidden = True
        Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "Overdraft" Then
             Rows("81").EntireRow.Hidden = True
        Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "" Then
             Rows("81").EntireRow.Hidden = True
        Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "Home Loan" Then
             Rows("81").EntireRow.Hidden = False
        Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "Invest Loan" Then
             Rows("81").EntireRow.Hidden = False
        Sheet11.Protect Password:="XX"
            If Target.Address = ("$L$86") Then
    Sheet11.Unprotect Password:="XX"
        If Target.Text = "" Then
             Rows("87").EntireRow.Hidden = True
        Sheet11.Protect Password:="CreditARA"
        ElseIf Target.Text = "Fail" Then
             Rows("87").EntireRow.Hidden = False
        Sheet11.Protect Password:="CreditARA"
    End If
    End If
    End If
    End If
    End Sub

The unlighted section works fine, it the red section that is not working. Hoping someone could assist. Thank you for you assistance

 

[bobsan42]

The change event only happens when the contents of a cell is changed. If cell L86 contains a formula its contents is not really changed - only the result of the formula changes.
The first part of your code works because you probably change the contents of C86 (manually or by code).
To make the second (red) part work you have to use the range itself (NOT the Target object).
The other part to the problem is: the check for target address = L86 is in a check if the target address is C67 - this can never both be true. If you indent your code properly you will probably see what I mean. You only ever check if the address is L86 if C67 contains text "Invest Loan".
This is actually what your code looks like:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = ("$C$67") Then
        Sheet11.Unprotect Password:="XX"
        If Target.Text = "All in One" Then
            Rows("81").EntireRow.Hidden = True
            Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "Credit Card" Then
            Rows("81").EntireRow.Hidden = True
            Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "Personal Loan" Then
            Rows("81").EntireRow.Hidden = True
            Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "Overdraft" Then
            Rows("81").EntireRow.Hidden = True
            Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "" Then
            Rows("81").EntireRow.Hidden = True
            Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "Home Loan" Then
            Rows("81").EntireRow.Hidden = False
            Sheet11.Protect Password:="XX"
        ElseIf Target.Text = "Invest Loan" Then
            Rows("81").EntireRow.Hidden = False
            Sheet11.Protect Password:="XX"
            If Target.Address = ("$L$86") Then
                Sheet11.Unprotect Password:="XX"
                If Target.Text = "" Then
                    Rows("87").EntireRow.Hidden = True
                    Sheet11.Protect Password:="CreditARA"
                ElseIf Target.Text = "Fail" Then
                    Rows("87").EntireRow.Hidden = False
                    Sheet11.Protect Password:="CreditARA"
                End If
            End If
        End If
    End If
End Sub

However it will be better to check this upon Calculate event because the result of the formula may change without actual change of a call on the sheet. I assume that you don't really use two different passwords to lock the sheet.
Try the code like this - this would be my approach:

Private Sub Worksheet_Calculate()
    With Range("L86")
        Sheet11.Unprotect Password:="XX"
        Rows("87").EntireRow.Hidden = (.Text <> "Fail")
        Sheet11.Protect Password:="XX"
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Address = ("C67") Then
            Sheet11.Unprotect Password:="XX"
            Select Case .Text
                Case "All in One", "Credit Card", "Personal Loan", "Overdraft", ""
                    Rows("81").EntireRow.Hidden = True
                Case "Home Loan", "Invest Loan"
                    Rows("81").EntireRow.Hidden = False
            End Select
            Sheet11.Protect Password:="XX"
        End If
    End With
End Sub

However, this will mean that row 87 will be checked for hiding with each calculation.
If the result in L86 can only change after a change in C67, you should use this version:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Address = ("C67") Then
            Sheet11.Unprotect Password:="XX"
            Select Case .Text
                Case "All in One", "Credit Card", "Personal Loan", "Overdraft", ""
                    Rows("81").EntireRow.Hidden = True
                Case "Home Loan", "Invest Loan"
                    Rows("81").EntireRow.Hidden = False
            End Select
           
            Rows("87").EntireRow.Hidden = (Range("L86").Text <> "Fail")
           
            Sheet11.Protect Password:="XX"
        End If
    End With
End Sub

 

[kmiceski]

Hi Bobsan,
Appreciate your response, correct the first part of the code runs from a drop down lists with hard coded text.

I see what you mean from the indent how this is incorrect. I terms of the password, it is the same error on my part when posting on the forum.

I attempted to use both these codes however with the first one I received and ‘out of stack’ error. I then tried using the second part (worksheet_change event)of the first code provided to see if that works, however the row does not unhide when the false items are selected.

I tried your second code however the same issues occurred again where the row (81) would not unhide when the false items were selected from the drop down list. Nothing happened with row (87) either.

Thank you for your assistance.

 

[bobsan42]

Can you debug the code line by line and see what's is not working? Any error messages?
Did you enter the correct password?
Are you a 100 % sure about the cell addresses?
try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Address = ("C67") Then
            Dim sh As Worksheet
            Set sh = .Parent
            sh.Unprotect Password:="XX"
            If sh.ProtectContents = False Then
                Select Case .Text
                    Case "All in One", "Credit Card", "Personal Loan", "Overdraft", ""
                        sh.Rows("81").EntireRow.Hidden = True
                    Case "Home Loan", "Invest Loan"
                        sh.Rows("81").EntireRow.Hidden = False
                End Select
   
                sh.Rows("87").EntireRow.Hidden = (sh.Range("L86").Text <> "Fail")
   
                sh.Protect Password:="XX"
            Else
                MsgBox sh.Name & ": Sheet is still protected. No changes are made."
            End If
        End If
    End With
    Set sh = Nothing
End Sub

 

[gmain1978]

I am doing something very similar by hiding a column based on the result of a formula in a cell. This works perfectly fine for me so assume you'll need to change the value = 4 bit to true/false and the columns to hide. I added this to the sheet I was hiding the column in not to a module. New to VBA as well so hopefully this works for you.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("G1").Value = 4 Then
        Columns("F").EntireColumn.Hidden = True
    Else
        Columns("F").EntireColumn.Hidden = False
    End If
End Sub