Hide/Unhide Rows Based on a Formulated Cell

HOC · Feb 7th 2017

Hi I am looking to see what I am doing wrong.
I have an excel template which I want to open in stages based on formulated answers. i.e E17, E26
The formulated field outputs an answer based on Yes/No scoring in other cells.
However, when the answer formulates when all questions answered the rows do not hide/unhide. I can get it working on dropdown list but really need it to calculate based on the formulated answer.
Any ideas where I am going wrong??

Code

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Target.Column = 5 And Target.Row = 17 And Target.Value = "Non Significant Change - Minor Local Governance applies" Then
        Rows("1:18").EntireRow.Hidden = False
        Rows("19:28").EntireRow.Hidden = True
        Rows("29").EntireRow.Hidden = False
        Rows("30").EntireRow.Hidden = True
        Rows("31:121").EntireRow.Hidden = False
        Rows("122:128").EntireRow.Hidden = True
        Rows("129").EntireRow.Hidden = False
        Rows("130:301").EntireRow.Hidden = True
        
ElseIf Target.Column = 5 And Target.Row = 17 And Target.Value = "Significant Change - complete the additional materiality questions below" Then
        Rows("1:17").EntireRow.Hidden = False
        Rows("18").EntireRow.Hidden = True
        Rows("19:26").EntireRow.Hidden = False
        Rows("27:301").EntireRow.Hidden = True
ElseIf Target.Column = 5 And Target.Row = 26 And Target.Value = "Significant Non Material [Standard]" Then
        Rows("1:17").EntireRow.Hidden = False
        Rows("18").EntireRow.Hidden = True
        Rows("19:27").EntireRow.Hidden = False
        Rows("28:29").EntireRow.Hidden = True
        Rows("30:82").EntireRow.Hidden = False
        Rows("83:98").EntireRow.Hidden = True
        Rows("99:121").EntireRow.Hidden = False
        Rows("122:128").EntireRow.Hidden = True
        Rows("129:300").EntireRow.Hidden = False
        Rows("301").EntireRow.Hidden = True
ElseIf Target.Column = 5 And Target.Row = 26 And Target.Value = "Significant Change - Material" Then
        Rows("1:17").EntireRow.Hidden = False
        Rows("18").EntireRow.Hidden = True
        Rows("19:26").EntireRow.Hidden = False
        Rows("27").EntireRow.Hidden = True
        Rows("28").EntireRow.Hidden = False
        Rows("29").EntireRow.Hidden = True
        Rows("30:82").EntireRow.Hidden = False
        Rows("83:98").EntireRow.Hidden = True
        Rows("99:121").EntireRow.Hidden = False
        Rows("122:128").EntireRow.Hidden = True
        Rows("129").EntireRow.Hidden = False
        Rows("300").EntireRow.Hidden = True
        Rows("301").EntireRow.Hidden = False  


End If
End Sub

Display More

**StephenR** · Feb 7th 2017

Re: Hide/Unhide Rows Based on a Formulated Cell

Please make sure you use code tags in future as per forum rules.

If these are as a result of a formula result, the change event will not fire. You need to use the Calculate event, though this does not relate to a specific range.

HOC · Feb 7th 2017

Re: Hide/Unhide Rows Based on a Formulated Cell

Quote from StephenR;785134

Please make sure you use code tags in future as per forum rules.

If these are as a result of a formula result, the change event will not fire. You need to use the Calculate event, though this does not relate to a specific range.

Thank you for replying, apologies not aware of what code tags are. I' m new to VBA.

Would you mind giving me an example of what I need to change in the VBA code?

The Select/Yes/No dropdown list answers are input in F10-F16

Code

I use the following formulas to calculate the overall answer:
[B]P3 [/B]COUNTIF(F10:F16,"Yes") and [B]R3 [/B]IF(AND(P3>=1,P10=0),"Significant Change","FALSE")
[B]P9[/B] COUNTIF(F10:F16,"No") and [B]R9[/B] IF(AND(P9>=1,P3=0,P10=0),"Non Significant","FALSE")
[B]P10 [/B]COUNTIF(F10:F16,"Select") and [B]R10 [/B]IF(P10>=1,"TBA")
[B]E17[/B]: IF(R$3="Significant Change","Significant Change - complete the additional materiality questions below",IF(R$9="Non Significant","Non Significant Change - Minor Local Governance applies",IF(R$10="TBA","TBA")))


Private Sub Worksheet_Change(ByVal Target As Range)
 
  If Target.Column = 5 And Target.Row = 17 And Target.Value = "Non Significant Change - Minor Local Governance applies" Then
        Rows("1:18").EntireRow.Hidden = False
        Rows("19:28").EntireRow.Hidden = True
        Rows("29").EntireRow.Hidden = False
        Rows("30").EntireRow.Hidden = True
        Rows("31:121").EntireRow.Hidden = False
        Rows("122:128").EntireRow.Hidden = True
        Rows("129").EntireRow.Hidden = False
        Rows("130:301").EntireRow.Hidden = True
       
ElseIf Target.Column = 5 And Target.Row = 17 And Target.Value = "Significant Change - complete the additional materiality questions below" Then
        Rows("1:17").EntireRow.Hidden = False
        Rows("18").EntireRow.Hidden = True
        Rows("19:26").EntireRow.Hidden = False
        Rows("27:301").EntireRow.Hidden = True


    End If
End Sub

Display More

Thank you for your time

**StephenR** · Feb 7th 2017

Re: Hide/Unhide Rows Based on a Formulated Cell

See at the top of each post, under your name, it says "I agreed to these rules"? Have a read and all will become clear.

Hide/Unhide Rows Based on a Formulated Cell

Participate now!

Share

Users Viewing This Thread