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

kmiceski

New Member
Joined
Aug 11, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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:

Rich (BB 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
 
Last edited by a moderator:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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:
VBA 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
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:

VBA Code:
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:
VBA Code:
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

New Member
Joined
Aug 11, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Jun 23, 2021
Messages
5
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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.

VBA Code:
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
 

Forum statistics

Threads
1,147,960
Messages
5,744,047
Members
423,843
Latest member
alex2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top