VBA - HIDE/UNHIDE Rows Based on Output of a Formulated Cell

[HazelOC]

Hi All,
Hoping you can help.
I have a very long spreadsheet which I want to be able to hide unhide rows/sections based on formulated answers.
I can get the code to work when I use a dropdown list however unable to get it working using a formulated cell. I am new to VBA so any help is much appreciated.


Private Sub Worksheet_Calculate()
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

[SamT]

Since the calculate Event sub has no "Target" Parameter, the use of "Target" in the code is meaningless.

Change both

If Target.Column = 5 And Target.Row = 17 And Target.Value =
to

If Range("E17").Value =

And Change the others to the similar. Range("E26")

[p45cal]

Private Sub Worksheet_Calculate()
If Cells(17, 5).Value = "Non Significant Change - Minor Local Governance applies" Then
Range("1:18,29:29,31:121,129:129").EntireRow.Hidden = False
Range("19:28,30:30,122:128,130:301").EntireRow.Hidden = True

ElseIf Cells(17, 5).Value = "Significant Change - complete the additional materiality questions below" Then
Range("1:17,19:26").EntireRow.Hidden = False
Range("18:18,27:301").EntireRow.Hidden = True

ElseIf Cells(26, 5).Value = "Significant Non Material [Standard]" Then
Range("1:17,19:27,30:82,99:121,129:300").EntireRow.Hidden = False
Range("18:18,28:29,83:98,122:128,301:301").EntireRow.Hidden = True

ElseIf Cells(26, 5).Value = "Significant Change - Material" Then
Range("1:17,19:26,28:28,30:82,99:121,129:129,301:301").EntireRow.Hidden = False
Range("18:18,27:27,29:29,83:98,122:128,300:300").EntireRow.Hidden = True
End If
End Sub

[HazelOC]

Thank you so much for your help!!!
I am having another issue when I input the new code (have amended some of the range rows stated above)
Runtime error 2147417848 (80010108) Method Hidden of Object Range failed.
When I remove Macro I get a Runtime error 1004 - Unable to set the Hidden property of Range class.
I have attached the document form as probably easier for you to look at - My formulas are stored under hidden columns K-S.
Really appreciate your help with this.


Private Sub Worksheet_Calculate()
If Cells(17, 5).Value = "Non Significant Change - Minor Local Governance applies" Then
Range("1:18,29:29,31:123,131:163").EntireRow.Hidden = False
Range("19:28,30:30,124:130,164:313").EntireRow.Hidden = True

ElseIf Cells(17, 5).Value = "Significant Change - complete the additional materiality questions below" Then
Range("1:17,19:26").EntireRow.Hidden = False
Range("18:18,27:313").EntireRow.Hidden = True

ElseIf Cells(26, 5).Value = "Significant Non Material [Standard]" Then
Range("1:17,19:27,30:123,131:312").EntireRow.Hidden = False
Range("18:18,28:29,124:130,313:313").EntireRow.Hidden = True

ElseIf Cells(26, 5).Value = "Significant Change - Material" Then
Range("1:17,19:26,28:28,30:123,131:311,313:313").EntireRow.Hidden = False
Range("18:18,27:27,29:29,124:130,312:312").EntireRow.Hidden = True
End If
End Sub

[p45cal]

The business of hiding/showing rows itself sparks off another Worksheet_Calculate event (I haven't looked in depth as to why), and so the routine calls itself again and again until it runs out of resources.
Temporarily disabling Events will stop that:
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Cells(17, 5).Value = "Non Significant Change - Minor Local Governance applies" Then
Range("1:18,29:29,31:123,131:163").EntireRow.Hidden = False
Range("19:28,30:30,124:130,164:313").EntireRow.Hidden = True

ElseIf Cells(17, 5).Value = "Significant Change - complete the additional materiality questions below" Then
Range("1:17,19:26").EntireRow.Hidden = False
Range("18:18,27:313").EntireRow.Hidden = True

ElseIf Cells(26, 5).Value = "Significant Non Material [Standard]" Then
Range("1:17,19:27,30:123,131:312").EntireRow.Hidden = False
Range("18:18,28:29,124:130,313:313").EntireRow.Hidden = True

ElseIf Cells(26, 5).Value = "Significant Change - Material" Then
Range("1:17,19:26,28:28,30:123,131:311,313:313").EntireRow.Hidden = False
Range("18:18,27:27,29:29,124:130,312:312").EntireRow.Hidden = True
End If
Application.EnableEvents = True
End Sub
While you're developing this, if there's an error in the code it should stop on the offending line to alert you as to where the error occurred, however, if you stop the code running at this point, that final line:
Application.EnableEvents = True
may not be executed, and that would stop other events firing (and this one too), so be ready in the Immediate pane with that line to apply it manually.

[HazelOC]

Thank you so much for helping with this, I can get this working now, have been scratching my head the past few days.
Really appreciate it.