Show/hide rows based on value entered at top of column based on single or multiple values in column

[AgentKMK]

VBA noob so any help is much appreciated as I have tried but failed to do this. I am using the latest version of Excel.

I need to be able to show/hide project rows depending on the value a user enters into a cell from a drop-down list in row 2 at the top of column in question. My sheet has a header row (row 1), followed by row 2 where the sort value is entered. The data starts at row 3 and ends at row 114. The rows are projects.

Here is a small snapshot.
https://www.dropbox.com/s/dpbha293ll3i853/PM Sheet Example.png?dl=0

1. Project Status (Column H)
Projects move through statuses as work progresses from start to finish. The status is selected from a data validation dropdown list from a range named Proj_Status. Although I may be adding more statuses in the future, the current statuses are: IRC, WCW, WVE, RTB, WMB, RTR, REV, MYPL, DONE, CANC. This has been applied to range: H3:H114.

I have applied a different data validation dropdown list to cell H2 named Proj_Stat_Sort. The current statuses are IRC, WCW, WVE, RTB, WMB, RTR, REV, MYPL, DONE, CANC, BLANK, ACTIVE, REVIEW.

If cell H2 is left empty, all project rows should be shown.

If the user selects a certain project status in cell H2, only those projects occupying that status should be shown. For example, if they select REV then only REV project rows should be displayed.

If the user selects BLANK in cell H2, only those projects with no data in the project status field should be shown.

If the user selects ACTIVE in cell H2, projects with the status DONE or CANC should be hidden.

If the user selects REVIEW in cell H2, only those projects with the status RTR or REV should be shown.

2. Team Member To Do (Column J)
Team members have their initials added in this column. As multiple team members can work on a project, projects can have more than one set of initials, separated by a comma.

How do I create a data validation list so the user can select from a dropdown range of initials, but where the cell can have more than one set of initials separated by a comma? Is that possible? The options, in any combination, are: MS, CH, KK, EE, JB, DT, NM, BN, IF. These initials are held in a range named Team_Member. This validation would be applied to the range: J2:J114.

In the search cell at the top of the column (J2), if a users selects a certain team member’s initials (one only), only those projects with their initials would be shown.

Thanks a million.

AgentKMK.

 

[Fluff]

Hi & welcome to MrExcel.
If you are willing to put your headers on row 2 & your dropdowns in row 1

You could try this for part 1

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Address = "$H$1" Then Exit Sub
   If Me.FilterMode Then Me.ShowAllData
   Select Case LCase(Range("H1").Value)
      Case ""
      Case "blank"
         Range("A2").AutoFilter 8, ""
      Case "active"
         Range("A2").AutoFilter 8, "<>DONE", xlAnd, "<>CANC"
      Case "review"
         Range("A2").AutoFilter 8, "RTR", xlOr, "REV"
      Case Else
         Range("A2").AutoFilter 8, Range("H1").Value
      End Select
End Sub

This needs to go in the sheet module

 

[AgentKMK]

Hi Fluff
Thank you for your reply. After I made the post I ended up changing a few things on the sheet.
Project Status column now Column J rather than H. I also changed the codes to something more descriptive for the user.
I moved the header row to row 2 and the dropdown to row 1 as you suggested, adjusted your code to reflect my changes and inserted it into the sheet as a module but nothing is happening. Not sure what I have done wrong.
Here is a link to a screenshot:
https://www.dropbox.com/s/naawqsyx2ei1ps3/PM Sheet Example 2.png?dl=0


Private Sub Project_Status(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Target.Address = "$J$1" Then Exit Sub
If Me.FilterMode Then Me.ShowAllData
Select Case LCase(Range("J1").Value)
Case ""
Case "Blank [no assigned status]"
Range("A2").AutoFilter 10, ""
Case "Active [ignores Published & Cancelled]"
Range("A2").AutoFilter 10, "<>11. Published", xlAnd, "<>Cancelled"
Case "In review"
Range("A2").AutoFilter 10, "08. Ready to review", xlOr, "09. With reviewer"
Case Else
Range("A2").AutoFilter 10, Range("J1").Value
End Select
End Sub

Thanks
AgentKMK

 

[Fluff]

It needs to go in the sheet module rather than a regular module & you need to keep the sub name as it was, ie

Private Sub Worksheet_Change(ByVal Target As Range)

It will then work automatically whenever you change the value in J1

You'll also need to change this line

[COLOR=#000000][FONT=Avenir]Select Case LCase(Range("J1").Value)[/FONT][/COLOR]

to

[COLOR=#000000][FONT=Avenir]Select Case Range("J1").Value[/FONT][/COLOR]

 

[AgentKMK]

Genius! It worked. I can see how useful AutoFilter is as well. That is going to help me endlessly. Thank you so much! I will be able to get rid of the top sort row and just use AutoFilter for all my needs now except for the below.

I know this can be done using AutoFilter but I would like to create a single button for the most used filter.
Could you help me create a show/hide toggle button for the Active projects where the caption changes on the button. I know how to create the ActiveX button, I just don't know how to write the VBA code. When the button says 'Hide Done', projects with the status '11. Published' and 'Cancelled' should be hidden. After the button is pressed, the caption should change to 'Show All'. Column is J and rows will now start at 2.

Thank you.

 

[Fluff]

I'd recommend using either a shape or a forms control button rather than an activeX button.
& you can then use this

Sub HideDone()
   Dim Shp As Shape
   
   Set Shp = ActiveSheet.Shapes(Application.Caller)
   If Shp.TextFrame.Characters.Text = "Hide Done" Then
      Shp.TextFrame.Characters.Text = "Show All"
      Range("A1").AutoFilter 10, "<>11. Published", xlAnd, "<>Cancelled"
   Else
      Shp.TextFrame.Characters.Text = "Hide Done"
      ActiveSheet.ShowAllData
   End If
End Sub

 

[AgentKMK]

Thanks again. You have been so helpful. It worked great. Had to use with a form button as you suggested.

 

[Fluff]

Glad to help & thanks for the feedback

 

[AgentKMK]

Hi. I spoke too soon. I added the button in Excel for Windows and it works, but when I opened on a Mac I get this message when I press the button. Run-time error '1004'
Method 'TextFrame' of object 'Shape' failed

When I select debug it highlight this code in yellow
If Shp.TextFrame.Characters.Text = "Hide Done" Then

 

[Fluff]

As I don't have a Mac, unfortunately I cannot help.