Force User to Enter Date but in UK format

[cct27]

Hi Guys

I found this answer to my query originally but it has a slight problem for me. (sorry cant insert the URL link, please search Google "Force the user to enter date in a cell")

or URL is : vbaexpress.com/kb/getarticle.php?kb_id=299

When the user enters the UK date it outputs it in US format. e.g. I enter 1/5/18 but it appears as 05/01/18. I have tried data validation but it has no effect. I used the VB code exactly as in the post

Does anyone have any idea on how to force the date in UK format?

Thanks for any help
C

[Paul_Hossler]

1. Where are you entering the date? Cell, Textbox, Inputbox, etc?

2. What are your format settings for dates?

3. What region is your PC set to?

4. Where do you want the formatted date returned, and do you need/want to do any calculations with it?

[cct27]

Hi Paul

1. I am just entering the dates into normal cells but I do use a macro to clear the sheet first, once it clears the first date cell it asks for new date, here I enter yesterday's date usually, then it will clear the second date cell and ask for new date, usually today's date. The Cell first cell is I18, the second cell is I19. The cells are in Date format.

2. The format settings are set as Date *14/03/2012 UK

3. PC Region is set to UK

4. I want the date entered into the two cells as above, I18 and I19. No other calculations are needed although 2 other cells have a formula to replicate what is in cell I18 i.e.=SUM(I18)

Hope this makes sense or if sending you the spreadsheet would help please let me know how I can do this.

Thank you for helping

Regards
C

[Paul_Hossler]

Hmmm -- that 'should work'

Post your workbook here. I can change where my PC thinks it is and take a look

[cct27]

New Millburn Lodgement Page.xlsm

Hi Paul

Workbook attached (i think), bear in mind there is a macro in it for clearing parts of the sheet so it may give a warning.

Thanks
C

[SamT]

I don't know which Cells you are using, so I will use A1 and B1

This goes in the WorkSheet Code page

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Or Target.Address = "$B$1" Then _
  Target.Value = Format(CDate(Target), "dd/mm/yyyy")
End Sub

However I hate overloading an Event sub, so I, personally, would use

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Or Target.Address = "$B$1" Then UKDates Target
End Sub



Private Sub UKDates(ByVal Target As Range)
  Target.Value = Format(CDate(Target), "dd/mm/yyyy")
End Sub

[cct27]

Hi SamT

I tried your second option but I got a runtime error, see attached. I will try the first option now
(second option also gives run-time error)

Thank you

C

excel-error.jpg

[SamT]

Try

Target = Format(CDate(Target), "dd/mm/yyyy")

[cct27]

Thanks SamT but unfortunately I still get the run-time error.

excel-error-2.jpg

[Paul_Hossler]

Try this. Your sheet was protect and I couldn't try everything I wanted to try. I tried to re-create the situation on Sheet2

a. I removed Data Validation
b. Set PC to UK
c. Changed number format on the cell to m/dd/yyyy
d. Rearranged your WS event code and decided there was no need for _SelectionChange
e. Always good idea to use Application.EnableEvents = False in an event handler
f. Date formats in Excel are slightly persistent; seems like once a cell has a date, it always thinks it has a date
g. You ClearSheet macro does not to .Select a Range in order to act on it .ClearContents
h. Excel is region-aware and if the formats are picked right, just changing the PC location will update the formats


UK.JPG
US.JPG

 
Option Explicit

Private Sub Worksheet_Activate()
    DoWeHaveDates
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
    DoWeHaveDates
End Sub
 

Private Sub DoWeHaveDates()
    Dim D As Date
    Application.EnableEvents = False
    
    Do While Not IsDate(Cells(18, 9).Value)
        D = InputBox("Enter the date for Money", "No Date")
        If IsDate(D) Then
            Cells(18, 9).NumberFormat = Application.International(xlGeneralFormatName)
            Cells(18, 9).Value = DateSerial(Year(D), Month(D), Day(D))
        End If
    Loop
        
    Do While Not IsDate(Cells(19, 9).Value)
        D = InputBox("Enter the date for Lodge", "No Date")
        If IsDate(D) Then
            Cells(19, 9).NumberFormat = Application.International(xlGeneralFormatName)
            Cells(19, 9).Value = DateSerial(Year(D), Month(D), Day(D))
        End If
    Loop
    
    Application.EnableEvents = True
    
End Sub

Option Explicit

Sub ClearSheet()
'
    Range("B3:B6").ClearContents
    Range("B8").ClearContents
    Range("C4:C5").ClearContents
    Range("B11").ClearContents
    Range("B13").ClearContents
    Range("B18:D22").ClearContents
    Range("I18").ClearContents
    Range("I19").ClearContents
    Range("I20").ClearContents
End Sub

[cct27]

Hi Paul

Sorry for late reply, Got called away and didn't get a chance to back to this yet. I can see what you have done here and seems to do exactly what I'm looking for, and will now try and get it implemented into the sheet I'm using. Hopefully I'll figure it all out.

Much appreciated, I'll let you know how I get on.

Regards
C