B
BJthebear
I have created a userform with a variety of options toward creating a
complex report
I can already autofilter on the first two option buttons using
If Me.optallprojects.Value = True Then
.Range("a1").CurrentRegion.AutoFilter field:=1 'select
all projects
Else
.Range("a1").CurrentRegion.AutoFilter field:=1,
criteria1:=Me.lstselectprojectno.Value 'select specific project'
End if
However if the 2nd option is selected I then need to further
autofilter the information in column 4 using two option buttons - on
whether they want all stages (field called optallclsstages) of the
selected project or just one aspect (optselectedclsstage) using the
value in a listbox lstselectedworkstage as the autofilter next
criteria.
I thought the following code would work:-
If Me.optallprojects.Value = True Then
.Range("a1").CurrentRegion.AutoFilter field:=1 'select
all projects
Else
.Range("a1").CurrentRegion.AutoFilter field:=1,
criteria1:=Me.lstselectprojectno.Value 'select specific project'
If Me.optallclsstages.Value = True Then
.Range("a1").CurrentRegion.AutoFilter
field:=4 'select all CLS Stages
Else
.Range("a1").CurrentRegion.AutoFilter
field:=4, criteria2:=Me.lstselectedclsworkstage.Value 'select
selected CLS work stage
End if
However it does not look at the autofilter on the second set of
options
What I am trying to do is end up with an autofilter range that can be
copied to another sheet.
Unfortunately this is not the end of the problem as after successfully
getting the above to work I then have to incorporate two+ further sets
of conditions in the same coding to reduce the number of selected
cases that make up the autofilter range based on userIDs and dates
These are also based on option buttons
Under normal filtering rules I would just have a list of criteria:-
Selection.AutoFilter field:=1, criteria1:="1002"
Selection.AutoFilter field:=4
Selection.AutoFilter field:=7, criteria1:="fred" etc etc
Selection.AutoFilter field:=8, criteria1:="19/01/2010" etc etc
but this is impossible when you are using option buttons and values
from listboxes
Can anyone throw any light on how I can solve the first problem and
then incorporate the other options
I really am getting desperate and need to solve this asap
Any help would be most appreciated
Brian Jermain
Scotland
The three sections in the userform are as follows:-
Section one
Option button to select all projects
Option button to select specific project from list box called
lstselectprojectno
These are grouped
If a specifc project is selected (option 2) then a further two options
are available optallclsstages or optselectedclsstage - if the second
of these options are selected then I would like the value from
lstselectedworkstage to be selected otherwise all stages will be
filtered
The second part is also grouped
Section 2
Option button to select All users- optallusers
Option button to select specific user - optselecteduser and use the
value in the list box lstselecteduser
These options are grouped
Section 3
Option button to select all dates
Option button to select a specific month from a list box
lstselectedcalendarmonth
Option button to select between specific dates ie startdate and
enddate
These options are grouped
complex report
I can already autofilter on the first two option buttons using
If Me.optallprojects.Value = True Then
.Range("a1").CurrentRegion.AutoFilter field:=1 'select
all projects
Else
.Range("a1").CurrentRegion.AutoFilter field:=1,
criteria1:=Me.lstselectprojectno.Value 'select specific project'
End if
However if the 2nd option is selected I then need to further
autofilter the information in column 4 using two option buttons - on
whether they want all stages (field called optallclsstages) of the
selected project or just one aspect (optselectedclsstage) using the
value in a listbox lstselectedworkstage as the autofilter next
criteria.
I thought the following code would work:-
If Me.optallprojects.Value = True Then
.Range("a1").CurrentRegion.AutoFilter field:=1 'select
all projects
Else
.Range("a1").CurrentRegion.AutoFilter field:=1,
criteria1:=Me.lstselectprojectno.Value 'select specific project'
If Me.optallclsstages.Value = True Then
.Range("a1").CurrentRegion.AutoFilter
field:=4 'select all CLS Stages
Else
.Range("a1").CurrentRegion.AutoFilter
field:=4, criteria2:=Me.lstselectedclsworkstage.Value 'select
selected CLS work stage
End if
However it does not look at the autofilter on the second set of
options
What I am trying to do is end up with an autofilter range that can be
copied to another sheet.
Unfortunately this is not the end of the problem as after successfully
getting the above to work I then have to incorporate two+ further sets
of conditions in the same coding to reduce the number of selected
cases that make up the autofilter range based on userIDs and dates
These are also based on option buttons
Under normal filtering rules I would just have a list of criteria:-
Selection.AutoFilter field:=1, criteria1:="1002"
Selection.AutoFilter field:=4
Selection.AutoFilter field:=7, criteria1:="fred" etc etc
Selection.AutoFilter field:=8, criteria1:="19/01/2010" etc etc
but this is impossible when you are using option buttons and values
from listboxes
Can anyone throw any light on how I can solve the first problem and
then incorporate the other options
I really am getting desperate and need to solve this asap
Any help would be most appreciated
Brian Jermain
Scotland
The three sections in the userform are as follows:-
Section one
Option button to select all projects
Option button to select specific project from list box called
lstselectprojectno
These are grouped
If a specifc project is selected (option 2) then a further two options
are available optallclsstages or optselectedclsstage - if the second
of these options are selected then I would like the value from
lstselectedworkstage to be selected otherwise all stages will be
filtered
The second part is also grouped
Section 2
Option button to select All users- optallusers
Option button to select specific user - optselecteduser and use the
value in the list box lstselecteduser
These options are grouped
Section 3
Option button to select all dates
Option button to select a specific month from a list box
lstselectedcalendarmonth
Option button to select between specific dates ie startdate and
enddate
These options are grouped