Filter records basis inputs in form

[JohnyG]

Hi,

I would like to create a macro that allows me to enter the date type (for eg. Issue Reported Date or Planned Date) and Date range (from date & to date) in User form and basis that it will filter the records in worksheet and display the output in Sheet2. I have somewhat 500 records to filter.

For instance if user selects Planned Date as first value in User form and then select any date range (from date and to date) then the data available in worksheet needs to be filtered on Planned Date only.

Please help.
Regards,

[JohnyG]

Really appretiate if some one help me on this.


Regards,

[GTO]

Greetings Johny,

Filtering programmatically isn't too tough (if I can do it, it must not be), but I wasn't sure how you wanted to "display the output". I guessed at copying it, as this will give you the filtered records on a similar looking sheet.

In this code, I kept your controls named as you had them. I would note that once you have a few controls on a form, you will find that renaming them (while creating) makes the code a lot easier to read.

For instance, instead of: Textbox1, Textbox2, and so on..., txtStartDate, txtEndDate will be easier on your eyes; especially later as you add more code to a project.

Anyways, here's a way of doing what you wanted, albeit a bit simplified:

[vba]Option Explicit
Dim _
dtmStartDate As Date, _
dtmEndDate As Date, _
lngCol As Long

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If ComboBox1.Value = "Issue report Date" Then
'// Set which column we'll filter. //
lngCol = 3
ElseIf ComboBox1.Value = "Planned Date" Then
lngCol = 6
Else
ComboBox1.Value = Empty
End If

End Sub

Private Sub CommandButton1_Click()
Dim _
rngFiltered As Range, _
rngUsed As Range, _
rngSht_1 As Range

'// Make sure the user entered values where needed. //
If Not TextBox1.Value = Empty _
And Not TextBox2.Value = Empty _
And Not ComboBox1.Value = Empty Then

'// Set range to the used portion of Sheet 1. I happen to prefer to use //
'// the sheet's codename, but you could use 'With Worksheets("Sheet1")' as //
'// well.. //
With Sheet1
'// Set the range to encompass entered resords. //
Set rngUsed = .Range(.Cells(1, 1), _
.Cells(.Cells(65536, 2).End(xlUp).Row, 8))
'// Set the column we want to filter by as a second range. //
Set rngSht_1 = .Columns(lngCol)

End With

'// Filter our column using both criteria you listed. //
rngSht_1.AutoFilter Field:=1, Criteria1:=">=" & dtmStartDate, _
Operator:=xlAnd, Criteria2:="<=" & dtmEndDate

'// In this example, I just 'Clear All', adjust to suit. //
Sheet2.Cells.Clear

'// Now set a "special" range that will only take the rows that met our //
'// criterias and... //
On Error Resume Next
Set rngFiltered = rngUsed.SpecialCells(xlCellTypeVisible).EntireRow
On Error GoTo 0

'// ...copy this to Sheet2. //
rngFiltered.Copy Worksheets("Sheet2").Range("A1")

'// Running AutoFilter w/no args simply undoes the filter, same as //
'// un-ticking the check box under Data | Filter. //
rngUsed.AutoFilter

End If

'// Not really necessary, but decent practice to explicitly release stuff. //
Set rngFiltered = Nothing
Set rngUsed = Nothing
Set rngSht_1 = Nothing

'// Dismiss the dialog from memory. //
Unload Me

End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

'// Change to preferences, but insist that the user puts in a date (to prevent //
'// errors. //
With TextBox1
If Not .Text Like "##/##/##" _
And Not .Value = Empty Then
ErrMsg .Value
.Value = Empty
ElseIf .Text Like "##/##/##" Then
dtmStartDate = Format(.Value, "MM/DD/YY")
End If
End With

End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'// Change to preferences, but insist that the user puts in a date (to prevent //
'// errors. //
With TextBox2
If Not .Text Like "##/##/##" _
And Not .Value = Empty Then
ErrMsg .Value
.Value = Empty
ElseIf .Text Like "##/##/##" Then
dtmEndDate = Format(.Value, "MM/DD/YY")
End If
End With
End Sub

Private Sub UserForm_Initialize()
With ComboBox1 '//
'// Let's make the combo box a drop-down list, so as to disallow the user //
'// from putting in goofy stuff. You could do this in the properties window//
'// as well. //
.Style = fmStyleDropDownList

.AddItem "Issue report Date"
.AddItem "Planned Date"
End With
End Sub

Private Sub ErrMsg(str As String)
MsgBox """" & str & """ is not a valid entry. Please enter a legitimate date," & _
vbCrLf & "formatted like: MM/DD/YY.", vbCritical, ""
End Sub[/vba]

Hope this helps ,

Mark