Worksheet change macro query

[Corleone]

hi
I have the following code in my spreadsheet which works fine but i need to make an amendment to it & im unsure how

Private Sub WORKSHEET_CHANGE(ByVal TARGET As Range)
If Not Intersect(TARGET, Range("c2")) Is Nothing Then
Select Case Range("c2")
Case "Scott Louder": Scott_Louder
Case "Nikki Grice Sims": Nikki_Grice_Sims
Case "David Binnie": David_Binnie
Case "Tim Thorne": Tim_Thorne
Case "All Projects": Unfilter_POAP
End Select
End If
End Sub

The code is activated whenever the user selects from the dropdown in Cell C2 which is a list of Programme managers - the macros that it is activating is just performing a filter on column A which is hidden (so for example when the user selects "Scott Louder" from the dropdown in cell C2 it will activate the Scott louder macro and filter on his name in column A (filtering on all the rows with Scott Louder in Column A)

The query i have been set is that they want to also have the function to be able to filter on the project manager - so I have set up a dropdown in Cell G2 which contains all of the Project Managers names and the project managers entries against each task line are all contained in Column B (Also hidden)


In summary

I would like to be able to have it set up so that whenever the user selects a name from the dropdown in C2 it clears the current filter then filters on that name using the entries in column A, but then if the user selects a name from the dropdown in cell G2 it clears the current filter and filters on column B based in the entry in Cell G2

So in effect it will be adding in the code below in to the code above


If Not Intersect(TARGET, Range("G2")) Is Nothing Then
Select Case Range("G2")
Case "PM1": Project Manager name 1
Case "PM2": Project Manager name 2
Case "PM3": Project Manager name 3
Case "PM4": Project Manager name 4

End Select
End If
End Sub





Thanks

 

[DanteAmor]

Hi @Corleone

This is what you need?

Private Sub WORKSHEET_CHANGE(ByVal Target As Range)
  If Not Intersect(Target, Range("c2")) Is Nothing Then
    Select Case Range("c2")
      Case "Scott Louder": Scott_Louder
      Case "Nikki Grice Sims": Nikki_Grice_Sims
      Case "David Binnie": David_Binnie
      Case "Tim Thorne": Tim_Thorne
      Case "All Projects": Unfilter_POAP
    End Select
  End If
  If Not Intersect(Target, Range("G2")) Is Nothing Then
    Select Case Range("G2")
      Case "PM1": ProjectManagername1
      Case "PM2": ProjectManagername2
      Case "PM3": ProjectManagername3
      Case "PM4": ProjectManagername4
    End Select
  End If
End Sub

Comment please.

 

[Corleone]

This works perfectly - Many thanks