Select Cells Case Statement

[chatnet26]

Dear Gentlemen,

I wanted to come up a result of using a select cells case statement. When after select cells of criteria, the field headers will filter so on and forth, as there were 29 table headers out of 12 data sheets maintaining in daily basis.

I can do this task using turn on filtering cells but keeping scrolling over to the right and left is trickier.

Will it be possible to do this using a macro?

Case select “Type”
Case Project
Activesheet.Range().autofilter

Case MOC
Activesheet.Range().Autofilter
Else if no matching criteria found
Then
Message box, no records criteria found

In this project, I have four criteria to complete this action. Please see requirement below.

Type Status Dept Actionee
Project Closed Dept1 Discipline 2
Project Active Dept 1 Discipline 1
Project Overdue Dept 2 Discipline 1
Project Overdue Dept 2 Discipline 2
Project Overdue Dept 2 Discipline 2
Project Active Dept 2 Discipline 2
Project Active Dept 1 Discipline 1
Project Active Dept 1 Discipline 1
Project Closed Dept 1 Discipline 1
Project Closed Dept 2 Discipline 2
MOC Closed Dept 2 Discipline 1
MOC Active Dept 2 Discipline 2
MOC Active Dept 2 Discipline 1
MOC Active Dept 1 Discipline 1
MOC Overdue Dept 1 Discipline 2
MOC Overdue Dept 2 Discipline 2
MOC Overdue Dept 1 Discipline 2

Appreciate your kind support. Thanks

Best Regards,

 

[chatnet26]

Dear Sir,

I observed also that when I press delete to cell G1 it filters also while suppose it will clear contents at cell G1.

I think there is something need to correct. Please I need your advice. Thanks

Best regards,

 

[chatnet26]

Dear Sir royUK,

I try to play around to put the code in worksheet it seems it complicates the method.

Will you able please to figure out what is wrong I did!

Best regards,

 

[chatnet26]

Dear Sir royUK,

Until now I've been playing around but I could not able to figured out how it works!

I believe there might be something wrong and or need additional syntax to ran. You had given me the principle but still I don't know how to apply the method.

Best regards,

 

[royUK]

I have no idea why you have put it into the ThisWorkBook Sheetchange module. Code in that module will work on every change made in any sheet. I would think that the code should be in a Standard Module and use a button to call it. Really, though I can see no advantage to just using AutoFilter instead of code.

 

[ttdk1]

I see a problem with your setup. The cells G2,G3, and G4 all disappear if the autofilter hides rows 2 to 4. Also, if you dislike having to scroll left and right to fill in the election, why not put the selection in the upper right and scroll lock the upper pane portion?

 

[chatnet26]

Dear Sir,

Ok apologize; I thought that I could come up a solution of an auto filter while selecting a drop down list from selected cell.

My objective, to have a drop down list in selected cell instead of using the turn on filtering tool. Because as I mentioned in the beginning there were 29 table headers to narrow from left to right back and forth of 12 sheets to maintain. So to save my time that’s why I came up with this principle.

Instead, is to put above from the table a drop down list selection cell.

Will it be possible you can help me out of this objective? Thanks

Best regards,

 

[chatnet26]

Dear ttdk1,

Many thanks to your response of my issue.

I had try your recommendation since yesterday while playing around but still I could not get the result. Based on the code that had been provided by royUK, while on the test ran it gives me the result. However, after adding the additional 2 criteria it didn't give me the result.

This is why I wonder how to apply the next 2 remaining criteria.

Best Regards,

 

[ttdk1]

why not put the four criteria in A2, B2, C2, and D2, where the header for those criteria in row 1? Then set autofilter for rows 4 and beyond. Now there won't be any conflict

 

[ttdk1]

if you put the four criteria in A2, B2, C2, and D2, then the following code would work

Private Sub Worksheet_Change(ByVal Target As Range)


  If Not Intersect(Target, Range("A2:D2")) Is Nothing Then
    With ActiveSheet
      .AutoFilterMode = False
      If .Range("A2").Value <> "" Then .Range("A4:AC2004").AutoFilter Field:=1, Criteria1:=.Range("A2").Value
      If .Range("B2").Value <> "" Then .Range("A4:AC2004").AutoFilter Field:=2, Criteria1:=.Range("B2").Value
      If .Range("C2").Value <> "" Then .Range("A4:AC2004").AutoFilter Field:=3, Criteria1:=.Range("C2").Value
      If .Range("D2").Value <> "" Then .Range("A4:AC2004").AutoFilter Field:=4, Criteria1:=.Range("D2").Value
    End With
  End If
End Sub

 

[royUK]

AdvancedFilter would be a better option.