Restrict the way a user can add data to a cell

[JTBorton]

Is it possible to force a certain format that information can be entered into a normal spreadsheet cell, similar to the way the date-time picker control forces a user to type in the date in a very specific format?

I hope that makes sense.... for example:

Suppose I want the user to add a date into a cell, and only a date, and I want them to strictly follow the format of dd/MMM/yy (just to making their life difficult). And suppose I want to make it so they can only add integers into the cell can cannot delete the /'s or add too many digits.

Can this be done? If so how would one go about doing it?

-JTBorton
Another Day, Another Disaster

 

[PHV]

I's use an UserForm.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

Yah, I was kind of hoping to get away from a user-form. Is it possible to just do it straight from the excel sheet?

-JTBorton
Another Day, Another Disaster

 

[combo]

A partial solution:
1. Format specific range in required date format,
2. Unlock formatted cells,
3. Protect sheet,
4. Add validation code (basic) to sheet's module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target.Cells
    If Not IsDate(Cell) Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
    End If
Next Cell
End Sub

As PH suggested, the userform is the best and simplest solution. Excel is flexible, data can be entered directly or pasted. It is possible to paste more than one cell. Validation does not work for pasted data.
Concerning formatting - excel tries to guess data format for not formatted cells ('General' format). This does not happen for preformatted cells. You can format cell as date and enter 39973, excel will display today's date. So the step (1) above.


combo