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

Printable View

02-16-2017, 07:25 AM
HazelOC

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

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.

Code:

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
02-16-2017, 02:09 PM
SamT

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

Change both

Code:

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

to

Code:

If Range("E17").Value =

And Change the others to the similar. Range("E26")
02-16-2017, 06:16 PM
p45cal

Code:

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
02-17-2017, 03:01 AM
HazelOC

1 Attachment(s)

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.

Code:

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
02-17-2017, 04:23 AM
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:

Code:

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.
02-17-2017, 07:12 AM
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.