Jun 12 2019 08:05 AM
Jun 12 2019 08:05 AM
Hello there,
I'm trying to set only one format of date acceptable into excel cells. In no instance a user shall feed in a different format of a date or even if he/she does, Excel sheet must be able to auto correct and set it back to the acceptable format.
I've tried 'Format Cells' and Data Validation which were of no help.
The acceptable format I'm trying to freeze is DD Month YYYY (Eg. 12 June 2019)
Please enlighten me with more options if available or to use data validation in excel to achieve.
Thank You
Jun 12 2019 09:26 AM
Jun 12 2019 09:29 AM
Jun 12 2019 09:49 AM
Jun 12 2019 11:57 AM
Jun 12 2019 12:04 PM
So it looks like I'll have to depend on VBA for this. Any idea what the code would probably look like?
Jun 13 2019 05:18 AM
Jun 17 2019 01:51 PM
Jun 17 2019 01:51 PM
I have used the following URL to set up the date picker in Excel, which has been successful. However, I couldn't get the required format which is mm-dd-yyyy. I have used the same VBA code as in the URL. Could someone please help?
https://github.com/Amallard/excelTricks/blob/master/DatePickerToCells.md
Thanks,
Karthick.S
Jun 17 2019 04:13 PM - edited Jun 17 2019 04:16 PM
@Deleted
Conditional formatting appears to work.
Number formatting set as a conditional format to any positive number greater than 30000 (say) will override any input default or user setting.
That is, until someone overwrites the conditional format of course.
Jun 18 2019 01:40 AM
Jun 18 2019 05:01 AM
Jun 18 2019 05:02 AM
@Peter Bartholomew Thank you, will have to try and let you know.
Jun 18 2019 05:54 AM
@Deleted Change the code to match the code below and format your cells with the proper date format.
Option Explicit Private Sub DTPicker1_Change() ActiveCell.Value = CDate(DTPicker1.Value) End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Sheet1.DTPicker1 .Height = 20 .Width = 20 If Not Intersect(Target, Range("A:A")) Is Nothing Then .Visible = True .Top = Target.Top .Left = Target.Offset(0, 1).Left .Value = Target.Value Else .Visible = False End If End With End Sub
Jun 18 2019 06:10 AM
Thank you so much, it works!!. However, one cell alone changes back to a different format by itself. Not sure why. Have attached the sheet.
Jun 18 2019 06:59 AM
Jun 18 2019 07:10 AM
Jun 18 2019 10:44 AM
Jun 18 2019 10:44 AM
By the way, will this work when I send the sheet to users that may not have date picker control installed in their systems?
Jun 19 2019 01:15 AM