Force user to fill date values in cells

**Snoopy2003** · 09-03-2010, 03:09 PM

Hello,

I need to define validation rule to a range of cells.
All cells should accept only DATE inputs
in format "mm/dd/yy".

I used VBA to set the format of a RANGE:

rng.Select
Selection.NumberFormat = "mm/dd/yy"

but excel accept general string inputs etc.

Can you help me ?

Thank you

**blane245** · 09-03-2010, 03:24 PM

Why not use the Data Validation.. menu item for the cells you want to constrain to date input?

**Snoopy2003** · 09-03-2010, 03:35 PM

Hello,

Thank you for your help.

If I choose DATA->VALIDATION->DATE I need to set valuse for FROM DATE, TILL DATE.
Is there any way to write this in VBA and check only if a DATE was filled in the cell ?
I do not mind which date the user will fill in the cell. I just want it to be a date in the format mm/dd/yy.

I hope you can help me.

Thank you

**Code Flunkie** · 09-03-2010, 03:37 PM

Have you tried using data validation instead of a macro? you can force a date to be entered like that

you can also do it through vba

With Range("A2:A100").Validation
        .Delete
        .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="1/1/1900", Formula2:="1/1/3000"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Warning"
        .InputMessage = "Insert valid date"
        .ErrorMessage = "Date invalid!"
        .ShowInput = True
        .ShowError = True
    End With

Chris

**blane245** · 09-03-2010, 03:42 PM

You could do all of your data validation in VBA, but you may want to let the Excel Data Validation help you out. You could set the from and to dates to very smal and very large dates so that they have little on no effect.