Hide-Unhide Subtask Rows Relative to Main Project Row

C

CBisME123

New Member
Joined
Sep 6, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I wonder if you could help me, I'm relatively new to using VBA. I wanted a code that would allow my excel sheet to function a bit like project in the sense that I want to have sub tasks that you can hide and reveal with a click.

I want to have numerous projects on this sheet but they will all be to the same template: main project details followed by 5 'action/subtask' rows.

I would like to use data validation in the D column with hide/unhide. When 'HIDE' is selected the subtask rows are hidden, when 'UNHIDE' is selected they are unhidden.

I've tried to include the mini-sheet (this is an extract from a workbook with several sheets).


Whiteboard.xlsm
BCDEFGHIJKLMNOP
4Dept.START DATEHide/UnhideDescriptionManagerPriorityINFORMATIONValueDUE DATEAction RequiredAction DueOwnerInfo SentResponseFile Link
531/08/2021HIDETEMPLATEManager 1AComplete all tasks18-Sep (Sat)1
6TEMPLATE - Sub Task 1E16194 Task 101-Sep (Wed)
7TEMPLATE - Sub Task 2E16194 Task 204-Sep (Sat)
8TEMPLATE - Sub Task 3E16194 Task 307-Sep (Tue)
9TEMPLATE - Sub Task 4E16194 Task 417-Sep (Fri)
10TEMPLATE - Sub Task 5E16194 Task 518-Sep (Sat)
1131/08/2021UNHIDEPROJECT 1Manager 1BComplete all tasks18-Sep (Sat)2
12PROJECT 1 - Sub Task 1E16194 Task 101-Sep (Wed)
13PROJECT 1 - Sub Task 2E16194 Task 204-Sep (Sat)
14PROJECT 1 - Sub Task 3E16194 Task 307-Sep (Tue)
15PROJECT 1 - Sub Task 4E16194 Task 417-Sep (Fri)
16PROJECT 1 - Sub Task 5E16194 Task 518-Sep (Sat)
1701/09/2021PROJECT 2Manager 2ARD to price10-Sep3
18PROJECT 2 - Sub Task 1E16194 Task 101-Sep (Wed)
19PROJECT 2 - Sub Task 2E16194 Task 204-Sep (Sat)
20PROJECT 2 - Sub Task 3E16194 Task 307-Sep (Tue)
21PROJECT 2 - Sub Task 4E16194 Task 417-Sep (Fri)
22PROJECT 2 - Sub Task 5E16194 Task 518-Sep (Sat)
23
ActionBoard
Cell Formulas
RangeFormula
L5,L11L5=SUBTOTAL(4,L6:L10)
E6,E18,E12E6=E5&" - Sub Task 1"
E7,E19,E13E7=E5&" - Sub Task 2"
E8,E20,E14E8=E5&" - Sub Task 3"
E9,E21,E15E9=E5&" - Sub Task 4"
E10,E22,E16E10=E5&" - Sub Task 5"
L7:L8,L19:L20,L13:L14L7=L6+3
L9,L21,L15L9=L8+10
L10,L22,L16L10=L9+1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L10Cell Valuebetween 1 and NOW()textNO
L6:L9Cell Valuebetween 1 and NOW()textNO
L22Cell Valuebetween 1 and NOW()textNO
L18:L21Cell Valuebetween 1 and NOW()textNO
F17Cell Valuecontains "Andrew Galbraith"textNO
F17Cell Valuecontains "Colin McCurdy"textNO
F17Cell Valuecontains "Rob Denson"textNO
F17Cell Valuecontains "CB"textNO
F11Cell Valuecontains "Andrew Galbraith"textNO
F11Cell Valuecontains "Colin McCurdy"textNO
F11Cell Valuecontains "Rob Denson"textNO
F11Cell Valuecontains "CB"textNO
F10Cell Valuecontains "Andrew Galbraith"textNO
F10Cell Valuecontains "Colin McCurdy"textNO
F10Cell Valuecontains "Rob Denson"textNO
F10Cell Valuecontains "CB"textNO
K5Cell Valuecontains "MOVE TO PEND"textNO
K5Cell Valuecontains "MOVE TO PEND"textNO
K5Cell Valuecontains "MOVE TO PEND"textNO
N5:N10Cell Value>0textNO
M5:M10Cell Valuecontains "AG"textNO
M5:M10Cell Valuecontains "CMC"textNO
M5:M10Cell Valuecontains "RD"textNO
M5:M10Cell Valuecontains "CB"textNO
F5:F9Cell Valuecontains "Andrew Galbraith"textNO
F5:F9Cell Valuecontains "Colin McCurdy"textNO
F5:F9Cell Valuecontains "Rob Denson"textNO
F5:F9Cell Valuecontains "CB"textNO
L5Cell Valuebetween 1 and NOW()textNO
F22Cell Valuecontains "Andrew Galbraith"textNO
F22Cell Valuecontains "Colin McCurdy"textNO
F22Cell Valuecontains "Rob Denson"textNO
F22Cell Valuecontains "CB"textNO
N18:N22Cell Value>0textNO
M18:M22Cell Valuecontains "AG"textNO
M18:M22Cell Valuecontains "CMC"textNO
M18:M22Cell Valuecontains "RD"textNO
M18:M22Cell Valuecontains "CB"textNO
F18:F21Cell Valuecontains "Andrew Galbraith"textNO
F18:F21Cell Valuecontains "Colin McCurdy"textNO
F18:F21Cell Valuecontains "Rob Denson"textNO
F18:F21Cell Valuecontains "CB"textNO
F16Cell Valuecontains "Andrew Galbraith"textNO
F16Cell Valuecontains "Colin McCurdy"textNO
F16Cell Valuecontains "Rob Denson"textNO
F16Cell Valuecontains "CB"textNO
L16Cell Valuebetween 1 and NOW()textNO
K11Cell Valuecontains "MOVE TO PEND"textNO
K11Cell Valuecontains "MOVE TO PEND"textNO
K11Cell Valuecontains "MOVE TO PEND"textNO
K1:K4,K17,K23:K1048576Cell Valuecontains "MOVE TO PEND"textNO
N1:N4,N11:N17,N23:N1048576Cell Value>0textNO
M1:M4,M11:M17,M23:M1048576Cell Valuecontains "AG"textNO
M1:M4,M11:M17,M23:M1048576Cell Valuecontains "CMC"textNO
M1:M4,M11:M17,M23:M1048576Cell Valuecontains "RD"textNO
M1:M4,M11:M17,M23:M1048576Cell Valuecontains "CB"textNO
K1:K4,K17,K23:K1048576Cell Valuecontains "MOVE TO PEND"textNO
K1:K4,K17,K23:K1048576Cell Valuecontains "MOVE TO PEND"textNO
F12:F15Cell Valuecontains "Andrew Galbraith"textNO
F12:F15Cell Valuecontains "Colin McCurdy"textNO
F12:F15Cell Valuecontains "Rob Denson"textNO
F12:F15Cell Valuecontains "CB"textNO
L1:L4,L17,L11:L15,L23:L1048576Cell Valuebetween 1 and NOW()textNO
Cells with Data Validation
CellAllowCriteria
D11ListHIDE, UNHIDE
D5ListHIDE, UNHIDE
M5:M9List=$C$27:$C$33
M23List=$C$27:$C$33
M11:M15List=$C$27:$C$33
M17:M21List=$C$27:$C$33
 

Attachments

  • Whiteboard.JPG
    Whiteboard.JPG
    150.5 KB · Views: 9

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Welcome to the MrExcel board!

Try this Worksheet_Change event code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 And Target.Column = 4 Then
    If Target.Value = "HIDE" Then
      Target.Offset(1).Resize(5).EntireRow.Hidden = True
    ElseIf Target.Value = "UNHIDE" Then
      Target.Offset(1).Resize(5).EntireRow.Hidden = False
    End If
  End If
End Sub
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

Try this Worksheet_Change event code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 And Target.Column = 4 Then
    If Target.Value = "HIDE" Then
      Target.Offset(1).Resize(5).EntireRow.Hidden = True
    ElseIf Target.Value = "UNHIDE" Then
      Target.Offset(1).Resize(5).EntireRow.Hidden = False
    End If
  End If
End Sub
Click to expand...
Thanks very much, that works perfectly!
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
Peter_SSs said:
You're welcome. Thanks for the follow-up. :)
Click to expand...
Hi Peter, I don't know if you can help or have any suggestions but I thought I would ask... There are filters on this table and whenever something is unfiltered as typical all rows unhide (incldui9ng those affected by the VBA). Is there a way I can have the VBA keep the rows hidden that I want to even when filtering/unfiltering?
 
Upvote 0
Hmm, that sort of defeats the intention of filtering doesn't it?

See if adding this to the same area that that the Worksheet_Change code is in helps. Test with a copy of your workbook.

VBA Code: 
Private Sub Worksheet_Calculate()
  Dim c As Range
  
  Application.EnableEvents = False
  For Each c In Range("D4", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).SpecialCells(xlConstants, xlTextValues)
    Debug.Print c.Address
    If c.Value = "HIDE" Then
      c.Offset(1).Resize(5).EntireRow.Hidden = True
    ElseIf c.Value = "UNHIDE" Then
      c.Offset(1).Resize(5).EntireRow.Hidden = False
    End If
  Next c
  Application.EnableEvents = True
End Sub
 
Upvote 0
Solution
Peter_SSs said:
Hmm, that sort of defeats the intention of filtering doesn't it?

See if adding this to the same area that that the Worksheet_Change code is in helps. Test with a copy of your workbook.

VBA Code: 
Private Sub Worksheet_Calculate()
  Dim c As Range
 
  Application.EnableEvents = False
  For Each c In Range("D4", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).SpecialCells(xlConstants, xlTextValues)
    Debug.Print c.Address
    If c.Value = "HIDE" Then
      c.Offset(1).Resize(5).EntireRow.Hidden = True
    ElseIf c.Value = "UNHIDE" Then
      c.Offset(1).Resize(5).EntireRow.Hidden = False
    End If
  Next c
  Application.EnableEvents = True
End Sub
Click to expand...
Thanks
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

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
Back
Top