Filtering and user input

[Lvenom]

I have a macros to filter data based on user input that works just fine.
My problem is when the user input is supposed to be the data excluded
from the filter my macro doesn't seem to filter out the undesired data.
(Data is date related with one filter asking to see data from a specific
date and the other fliter asking not to see data from a specific date.
My code is below...can anyone see my mistake? Thank you in advance for
your help

To include specific date:
'Get the filter's criteria from the user
FilterCriteria = InputBox("Enter Scheduled Downday.(MM/DD/YY)")
'Filter the data based on the user's input
Selection.AutoFilter Field:=12, Criteria1:=FilterCriteria

To exclude specific date
'Get the filter's criteria from the user
FilterCriteria = InputBox("Enter Scheduled Downday.(MM/DD/YY)")
'Filter the data based on the user's input
Selection.AutoFilter Field:=12, Criteria1:="<>FilterCriteria"

 

[Executor]

Hi Lvanom,

You are close:

'Get the filter's criteria from the user
FilterCriteria = InputBox("Enter Scheduled Downday.(MM/DD/YY)")
'Filter the data based on the user's input
Selection.AutoFilter Field:=12, Criteria1:="<>" & FilterCriteria

HTH,

Executor

 

[strive4peace]

Hi Lvenom,

put this code on a general module sheet

'~~~~~~~~~~~~~~~~~~~~
Sub filterOnOff( _
pBooEqual As Boolean)

'written by Crystal
'strive4peace2007 at yahoo.com

'PARAMETERS
'pBooEqual --> true to match, false to exclude


On Error GoTo Proc_Err

Dim FilterCriteria As String, mMsg As String

If pBooEqual Then
FilterCriteria = InputBox( _
"Enter Scheduled Downday to Match (MM/DD/YY)")

'user clicked Cancel
If FilterCriteria = "" Then Exit Sub

Selection.AutoFilter Field:=12, _
Criteria1:=FilterCriteria
Else

'To exclude specific date
FilterCriteria = InputBox( _
"Enter Scheduled Downday to Ignore(MM/DD/YY)")

'user clicked Cancel
If FilterCriteria = "" Then Exit Sub

Selection.AutoFilter Field:=12, _
Criteria1:="<>" & FilterCriteria, _
Operator:=xlAnd
End If

Proc_Exit:
On Error Resume Next
Exit Sub

Proc_Err:
Select Case Err.Number
Case 1004
'turn on autofilter
'assume L2 is valid
Range("L2").Select
Selection.AutoFilter

Resume
Case Else
MsgBox Err.Description, _
, "ERROR " & Err.Number _
& " filterOnOff"
'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
End Select
Resume Proc_Exit
End Sub

'~~~~~~~~~~~~~~~~~~~~
and then here is code for 2 command buttons behind the
respective sheet:

'~~~~~~~~~~~~~~~~~~~~
Private Sub IsEqual_Click()
filterOnOff True
End Sub

Private Sub IsNotEqual_Click()
filterOnOff False
End Sub
'~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Lvenom]

Thanks to both of you. The simple fix supplied by Executor worked quit
well and does give the results I expect. I will keep your suggestion o
file strive4peace in case the bugs start coming out. again thanks t
both of you for your quick response

 

[strive4peace]

you're welcome, Lvenom happy to help

you could also make the sub more generic by sending the
operator, field number, and InputBox message as
parameters... that way, you can use it for other columns and
other conditions...

'~~~~~~~~~~~~
ie:
button code behind the sheet

'~~~~~~~~~~~~
Private Sub IsEqual_Click()
filterOnOff "=", 12, _
"Enter Scheduled Downday to Match (MM/DD/YY)"
End Sub

Private Sub IsLessThanOrEqual_Click()
filterOnOff "<=", 12, _
"Enter Last Scheduled Downday to Show (MM/DD/YY)"
End Sub

Private Sub IsNotEqual_Click()
filterOnOff "<>", 12, _
"Enter Scheduled Downday to Exclude (MM/DD/YY)"
End Sub
'~~~~~~~~~~~~

general module

'~~~~~~~~~~~~
Sub filterOnOff( _
pOperator As String, _
pFieldNum As Integer, _
pMsg As String)

'written by Crystal
'strive4peace2007 at yahoo.com

'PARAMETERS
'pOperator --> "=", "<>" ... any valid operator
'pFieldNum --> Field number in AutoFilter
'assume field corresponds to column for err 2004
'pMsg --> message for InputBox

On Error GoTo Proc_Err

Dim FilterCriteria As String, mMsg As String

FilterCriteria = InputBox(pMsg)

'user clicked Cancel
If FilterCriteria = "" Then Exit Sub

Selection.AutoFilter _
Field:=pFieldNum, _
Criteria1:=pOperator & FilterCriteria

Proc_Exit:
On Error Resume Next
Exit Sub

Proc_Err:
Select Case Err.Number
Case 1004
'turn on autofilter
'assume row 2 is valid
'assume Field number is column number
Range(Cells(2, pFieldNum)).Select
Selection.AutoFilter

Resume
Case Else
MsgBox Err.Description, _
, "ERROR " & Err.Number _
& " filterOnOff"
'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
End Select
Resume Proc_Exit
End Sub
'~~~~~~~~~~~~

if you really want to get fancy, you could use an option
group to set the operator (=, <>, <=, >=, etc) and combine
your code even more...




Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*