I am having many troubles with user

[Navvy]

I am having many troubles with userforms!

My second field on the form is called agedate. This needs to be in the format dd/mm/yyyy and depending on this date entered I want to search the worksheet (column D) for anything that has that date entered including 2 days either side. Is this possible?

I use the autofilter to search and so far this has worked.

Selection.AutoFilter Field:=1, Criteria1:=Name.Value

 

[chandlm]

NAVVY


Add a command button to your userform and add this code to the command button click event:

agedate.text = Format(TextBox1.Text, "dd/mm/yyyy&quot
call check_dates(agedate.text)

Add this code to a module in the workbook.

Sub check_dates(dates As String)
Dim dateval
A = 1

Do Until IsEmpty(Range("d" & A).Value)
dateval = DateValue(Range("d" & A).Value)
If dateval = DateValue(dates) - 2 Then
Range("d" & A).Interior.Color = vbGreen
ElseIf dateval = DateValue(dates) - 1 Then
Range("d" & A).Interior.Color = vbBlue
ElseIf dateval = DateValue(dates) Then
Range("d" & A).Interior.Color = vbRed
ElseIf dateval = DateValue(dates) + 1 Then
Range("d" & A).Interior.Color = vbMagenta
ElseIf dateval = DateValue(dates) + 2 Then
Range("d" & A).Interior.Color = vbCyan

End If


A = A + 1
Loop

End Sub

I have just changed the interior color of the cells that match just to highlight which cells were selected, You can of course change the code to do whatever you want to do with the cells matching the criteria

Tested this in Excel 2000 and it works fine.

HTH

Matt

 

[bryanbayfield]

Hmmmm ... I just tried this in Excel and it is so bizarre - I just can't get it to work at all.

Assuming that your field is a text box called txtAgeDate:

Private Sub txtAgeDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ActiveSheet.UsedRange
On Error Resume Next
ActiveSheet.UsedRange.AutoFilter Field:=4, Criteria1:=CDate(DateValue(txtAgeDate))
End Sub

It supplies a date to Excel in the correct format but for some reason Excel can't see it.

Very odd, but that seems to be the norm with dates and Excel.

Sorry I couldn't solve your problem but maybe somebody can take it on from there ...