Tweet
Bottom-line:
I'm having difficulty getting a macro to filter as I need it to. In the below code, given both check boxes are marked, I want the spreadsheet to show both 0411 and 0431 rows (the filter criteria). Instead, the macro only shows 0431 since that subroutine comes after the 0411 one. (There will be about 20 such designators so I cannot use a combined check box)
More Information:
Situation:
I want to have a spreadsheet which tracks the training of individual Marines. Each of about 120 Marines are spread across about twenty occupational specialties. Each of these specialties has about 30 mission essential tasks. The first few columns will have identifying information while the rest will have headers for each mission essential task designator. A Marine will be placed on each row with the date of his achievement of each relevant task.
This will mean the spreadsheet will have upwards of 600 columns, the vast majority of which will be blank for most Marines since those tasks will be outside of their occupational specialty.
To resolve this for reporting and entry purposes, I want clickable check boxes (marked with the name of each occupational specialty) to show hidden irrelevant columns and use the autofilter to show the occupational specialty designator (which will be in one of the columns with identifying information).
I plan to set it up so that a user can select any and all of the 20 check boxes (they are not mutually exclusive) and when he hits a button, the macro will show the appropriate hidden columns and filter accordingly.
Problem:
The showing of hidden columns works well but the filter does not. If more than 1 checkbox is selected (ie- 0411 and 0431 in the above code), I want the macro to show both 0411 and 0431. Instead, only 0431 will be shown since its filter comes after the 0411 one.
Please help.
Thank you.
I'm having difficulty getting a macro to filter as I need it to. In the below code, given both check boxes are marked, I want the spreadsheet to show both 0411 and 0431 rows (the filter criteria). Instead, the macro only shows 0431 since that subroutine comes after the 0411 one. (There will be about 20 such designators so I cannot use a combined check box)
Code:
Sub Run()
If CheckBox21.Value = True Then
Call Sort0411
End If
If CheckBox22.Value = True Then
Call Sort0431
End If
End Sub
Sub Sort0411()
If CheckBox21.Value = True Then
Columns("E:F").Hidden = False
ActiveSheet.Range("$A$10:$BV$14").AutoFilter Field:=4, Criteria1:="=0411", _
Operator:=xlOr, Criteria2:="="
End If
End Sub
Sub Sort0431()
If CheckBox22.Value = True Then
Columns("G:H").Hidden = False
ActiveSheet.Range("$A$10:$BV$14").AutoFilter Field:=4, Criteria1:="=0431", _
Operator:=xlOr, Criteria2:="="
End If
End Sub
Situation:
I want to have a spreadsheet which tracks the training of individual Marines. Each of about 120 Marines are spread across about twenty occupational specialties. Each of these specialties has about 30 mission essential tasks. The first few columns will have identifying information while the rest will have headers for each mission essential task designator. A Marine will be placed on each row with the date of his achievement of each relevant task.
This will mean the spreadsheet will have upwards of 600 columns, the vast majority of which will be blank for most Marines since those tasks will be outside of their occupational specialty.
To resolve this for reporting and entry purposes, I want clickable check boxes (marked with the name of each occupational specialty) to show hidden irrelevant columns and use the autofilter to show the occupational specialty designator (which will be in one of the columns with identifying information).
I plan to set it up so that a user can select any and all of the 20 check boxes (they are not mutually exclusive) and when he hits a button, the macro will show the appropriate hidden columns and filter accordingly.
Problem:
The showing of hidden columns works well but the filter does not. If more than 1 checkbox is selected (ie- 0411 and 0431 in the above code), I want the macro to show both 0411 and 0431. Instead, only 0431 will be shown since its filter comes after the 0411 one.
Please help.
Thank you.
Comment