VINAY DALMIA

[TATA STEEL]

Can i force users to enter data in a column only in dd/mm/yy hh:mm format
i..e that should not be able to save the file if they enter data in any
other format .

The call can either be blank or with data in dd/mm/yy hh:mm format

 

[goutamrudra81]

Can i force users to enter data in a column only in dd/mm/yy hh:mm format
i..e that should not be able to save the file if they enter data in any
other format .

The call can either be blank or with data in dd/mm/yy hh:mm format

Mr.Vinay
Saving the file dose not depend on the format we use to put data in an
MS Excel Sheet. You should tell the users to put data in the format
which will be useful to the purpose of the work. And again,I coundn't
make out why you force any user to do that...If you face problem , you
can mail me to" (e-mail address removed)". You are mostly welcome.

Thanks
Goutam

 

[Shane Devenshire]

Hi,

You can set the format of the cell before hand and then you can require that
they make an entry that Excel can interpret as a date/time, but since Excel
stores dates and times as numbers they could enter a number and Excel would
not have a problem. But at least you can require that it not be text:

1. Select the cells in question and format them to the format you wish,
2. Choose the command Data, Validation, and pick Date from the Allow list
3. Leave or change the Data option
4. (assume you left between on in the previous step) in the Start date box
enter something like 1/1/1900
5. In the End date box enter something like 1/1/2100

Now since they must make an entry that is equivalent to a number, if they
don't the cell will be empty. You can block them from saving the file until
those cell(s) are not empty by using VBA code: Suppose the cell where you
want to require data is A1 of Sheet1:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("Sheet1").Range("A1") = "" Then
Cancel = True
MsgBox "You need to enter a date and time in cell A1 of Sheet1"
End If
End Sub

1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find thisWorkbook object under
your file name and double click it.
3. Paste in or type the code above.