Consulting

Results 1 to 6 of 6

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

  1. #1
    VBAX Newbie
    Joined
    Feb 2017
    Posts
    3
    Location

    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.

    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

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,273
    Location
    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")
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,042
    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
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Newbie
    Joined
    Feb 2017
    Posts
    3
    Location
    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
    Attached Files Attached Files
    Last edited by HazelOC; 02-17-2017 at 07:15 AM.

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,042
    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.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Newbie
    Joined
    Feb 2017
    Posts
    3
    Location
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •