European Date format in Userform

Question

I have a Userform that populates Textboxes with dates from a worksheet. The idea is to be able to edit the dates and save them back to the worksheet. The problem is the dates show up in American format, not European. I understand that I need to use code to force the date to show as European. So I have tried using this code

Dim LValue As String

LValue = Format(Date, "dd/mm/YYYY")

I then have a function to populate the form, where I want the correct date format to show

Sub PopulateForm()
            Me.Location.Value = rngFound(1, 0).Value
            Me.ID.Value = rngFound(1, 1).Value
            Me.FirstName.Value = rngFound(1, 2).Value
            Me.LastName.Value = rngFound(1, 3).Value
            Me.Grade = rngFound(1, 4).Value
            Me.ARLFam = rngFound(1, 8).Value
            Me.ARLEvac = rngFound(1, 11).Value
            Me.HRDFam = rngFound(1, 16).Value
            Me.HRDEvac = rngFound(1, 19).Value
            Me.CRDFam = rngFound(1, 24).Value
            Me.CRDEvac = rngFound(1, 27).Value
            Me.RSQFam = rngFound(1, 32).Value
            Me.RSQEvac = rngFound(1, 35).Value
            Me.COVFam = rngFound(1, 40).Value
            Me.COVEvac = rngFound(1, 43).Value
            Me.LSQFam = rngFound(1, 48).Value
            Me.LSQEvac = rngFound(1, 51).Value
            Me.HPCFam = rngFound(1, 56).Value
            Me.HPCTrackFam = rngFound(1, 63).Value
            Me.HPCEvac = rngFound(1, 59).Value
            Me.KNBFam = rngFound(1, 67).Value
            Me.KNBEvac = rngFound(1, 70).Value
            
End Sub

I haven't figured out where to place LValue in the sub routine for it to change the dates to the correct format. Am I on the right track? Or am I barking up the wrong tree?

Next, when I have changed the dates and save the changes to the worksheet, I encounter a new problem. The cells the dates go into are set up as dates, and other cells have formulas working off the information provided by the date cells. When I save the dates from the Userform, they show up in the correct cells, but all the other cells reading from the date cell now have the #Value error showing. This is the code used to save the new dates to the worksheet.

Private Sub EnterButton_Click()
Dim LR As Long
Dim replace As Long
Dim response As Long
Dim LValue As String

LValue = Format(Date, "dd/mm/YYYY")
If Me.ID.Value = "" Then
    MsgBox "You have not entered an ID."
    Me.ID.SetFocus
    Exit Sub
End If

FindRecord (Val(Me.ID))
    If Not rngFound Is Nothing Then
       replace = MsgBox("This record already exists in this Database." & vbNewLine _
       & "Replace?", vbYesNo)
       If replace = vbYes Then
            LR = rngFound.Row
       Else
            ClearForm
            Me.ID.SetFocus
            Exit Sub
        End If
    Else
        LR = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
            
    With ws
          .Cells(LR, 1).Value = Me.Location
          .Cells(LR, 2).Value = Val(Me.ID)
          .Cells(LR, 3).Value = Me.FirstName
          .Cells(LR, 4).Value = Me.LastName
          .Cells(LR, 5).Value = Me.Grade
          .Cells(LR, 9).Value = Me.ARLFam
          .Cells(LR, 12).Value = Me.ARLEvac
          .Cells(LR, 17).Value = Me.HRDFam
          .Cells(LR, 20).Value = Me.HRDEvac
          .Cells(LR, 25).Value = Me.CRDFam
          .Cells(LR, 28).Value = Me.CRDEvac
          .Cells(LR, 33).Value = Me.RSQFam
          .Cells(LR, 36).Value = Me.RSQEvac
          .Cells(LR, 41).Value = Me.COVFam
          .Cells(LR, 44).Value = Me.COVEvac
          .Cells(LR, 49).Value = Me.LSQFam
          .Cells(LR, 52).Value = Me.LSQEvac
          .Cells(LR, 57).Value = Me.HPCFam
          .Cells(LR, 64).Value = Me.HPCTrackFam
          .Cells(LR, 60).Value = Me.HPCEvac
          .Cells(LR, 68).Value = Me.KNBFam
          .Cells(LR, 71).Value = Me.KNBEvac
    End With
    
            If replace = vbYes Then
                MsgBox "The existing record on " & ws.Name & " row# " & rngFound.Row & " was overwitten"
            Else
                MsgBox "The record was written to " & ws.Name & " row# " & LR
            End If
            
          response = MsgBox("Do you want to enter another record?", _
              vbYesNo)

          If response = vbYes Then
              ClearForm
              Me.ID.SetFocus
          Else
              Unload Me
          End If
End Sub

Is it because the date has been saved as text instead of a date? If so, how do I get it to save as a European date?

dates are numbers, so if you save them as text, that may explain the error on formulas. Also, formating the cell as `dd/mm/yyyy` should help instead of doing it in a variable. — Foxfire And Burns And Burns, Oct 27 '20 at 13:22
@Foxfire And Burns And Burns Thank you for the quick reply. The cells are formatted as _Dates_. What I am not sure about is what is being transferred from the userform to the cells, is it text, numbers or dates? And how do I use VBA to get it to transfer to the cells as _Dates_? — Ian Martin, Oct 27 '20 at 15:18
@FunThomas - Now I am worried I have completely missed something here. I have _right-clicked_ the cell, selected _Format Cells_, then chose the _Numbers_ tab. Selected _Date_ and chose the style I wanted from the box to the right. Is that what you are referring to? Or is it something different? — Ian Martin, Oct 27 '20 at 15:32
If you write "Hello World" into a cell that is formatted as date, the content of the cell is a string, not a date. If you write "ABC" into a cell and afterwards format this cell as date, it's still a string. *Formatting* tells Excel how it should *display* your data. If you write a date into a cell and format it as "General", it will display the date as number (as dates are in fact numbers) — FunThomas, Oct 27 '20 at 15:38
I tried to change the cells to _General_ and as you stated, numbers appear. The problem is they do not show as recognisable dates which defeats the purpose of what these cells are supposed to display — Ian Martin, Oct 27 '20 at 15:53

score 0 · Answer 1 · answered Oct 27 '20 at 13:17

0

you surely can change the current forat into an european one and here is some examples of how you can uses it :

Sub dates_et_heures()

'Now renvoie la date et l'heure en cours (07.02.2018 09:09:02)
date_test = Now()

'Renvoie : 07.02.18
Range("A1") = Format(date_test, "dd.mm.yy")

'Renvoie : mardi 7 février 2018
Range("A2") = Format(date_test, "dddd d mmmm yyyy")

'Renvoie : Mardi 7 Février 2018
Range("A3") = WorksheetFunction.Proper(Format(date_test, "dddd d mmmm yyyy"))

'Renvoie : mar. 07
Range("A4") = Format(date_test, "ddd dd")

'Renvoie : MAR 07
Range("A5") = "'" & Replace(UCase(Format(date_test, "ddd dd")), ".", "")

'Renvoie : FÉVRIER 2018
Range("A6") = UCase(Format(date_test, "mmmm yyyy"))

'Renvoie : 07.02.2018 09:09
Range("A7") = Format(date_test, "dd.mm.yyyy hh:mm")

'Renvoie : Le 7 février à 9h09'02''
Range("A8") = Format(date_test, "Le d mmmm à h\hmm'ss''")

'Renvoie : 9H09
Range("A9") = Format(date_test, "h\Hmm")

End Sub

I don't have the answer for the second part but i hope this could help too

answered Oct 27 '20 at 13:17

A.Amel

26
4

Assuming that the cells contain a *Date*, you shouldn't write a *String* to them. – FunThomas Oct 27 '20 at 14:00
@FunThomas What should `LValue` bet set as, if not set as a _String_ for it to work as a _Date_? – Ian Martin Oct 27 '20 at 15:15
@Ian Martin: When you write the data into the Textboxes, you write a String, that's fine. I was commenting this answer which is writing strings into Excel cells – FunThomas Oct 27 '20 at 15:17
@A.Amel - Thank you for the code. I am not sure how I am supposed to add one of them into the code I have already set up. As you can see, the date columns are placed specifically within the worksheet, and I need to be able to populate the Userform with the dates in European Format ("dd/mm/YYYY"), then once edited, to replace them into the correct cells on the worksheet. – Ian Martin Oct 27 '20 at 15:25

score 0 · Accepted Answer · answered Oct 27 '20 at 16:25

0

The following assumes that you have real dates in Excel (you can prove this for example by formatting a cell containing a date as General: It should display a number).

Background: dates are stored internally as numbers, the integer part gives the Date-part, counting the number of days starting from 1. January 1900. The fraction part is representing the time, 1/3 would be 8am (a third of the day)

A textbox in VBA contains always a String. When you want to write a date into the textbox and use code like tbStartDate = ActiveSheet.Cells("B2") and B2 contains a date, you are asking VBA to convert the date into a string. VBA will do so, but it has it's own rules for that and so you end up with a string that looks like an US date. Basically, you should always avoid that VBA does an automatic conversion for you. Instead, use a function for that: Format it the right function to convert a Date or a number into a string, you use it already correctly in the first 2 statements. To write the date into the textbox, you now write

tbStartDate = Format(ActiveSheet.Cells("B2"), "dd/mm/YYYY")

Now comes the tricky part: The user may change the date and you want to write it back to the cell. Again, you shouldn't let Excel do the conversion implicitly. The problem is that with a normal text box you cannot prevent that the user enters rubbish stuff (you might read Formatting MM/DD/YYYY dates in textbox in VBA).

But let's assume your user enters the date in the "correct" form: How do you convert a string into a date?

You often see the answer to use CDate that converts a string into a date, respecting the locale setting of the system. Fine, as long as all users have the same settings. But if you might have a user coming with a Laptop freshly imported from the US or that comes from any other part of the world, you have the same problem again: VBA will convert the date with wrong assumptions (eg changing the day- and month part).

Therefore I usually use a small custom function that splits the string and use the parts as parameters into another VBA function DateSerial. It will return 0 (=1.1.1900) if the input is complete nonsense, but doesn't check all invalid possibilities. A 13 as input is happily accepted (DateSerial, btw, accepts this also).

Function StrToDate(s As String) As Date
    ' Assumes input as dd/mm/yyyy or dd.mm.yyyy
    Dim dateParts() As String
    dateParts = Split(Replace(s, ".", "/"), "/")    ' Will work with "." and "/"
    If UBound(dateParts) <> 2 Then Exit Function
    
    Dim yyyy As Long, mm As Long, dd As Long
    dd = Val(dateParts(0))
    mm = Val(dateParts(1))
    yyyy = Val(dateParts(2))
    If dd = 0 Or mm = 0 Or yyyy = 0 Then Exit Function
    
    StrToDate = DateSerial(yyyy, mm, dd)
End Function

Now, writing the input back to the cell could be like

dim d as Date
d = StrToDate(tbStartdate)
if d > 0 then ActiveSheet.cells(B2) = d

answered Oct 27 '20 at 16:25

FunThomas

23,043
3
18
34

I have a Function with the following `Function FindRecord(ID) Set ws = Worksheets("Data Input") With ws.Columns Set rngFound = .Find(What:=ID, After:=.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, _ SearchDirection:=xlNext) End With End Function`, then the populate function uses `rngFound ` to find the cells to populate the Text boxes in the UserForm. Your `tbStartDate = Format(ActiveSheet.Cells("B2"), "dd/mm/YYYY")` code, should it replace `rngFound(1, 8).Value`, or be placed somewhere else? – Ian Martin Oct 27 '20 at 17:00
If `rngFound(1, 8)` it the cell tha contains the date you want to display, just use it instead of `ActiveSheet.Cells(B2)` I used it in my example – FunThomas Oct 27 '20 at 17:29
Thank you for your help so far. I am nearly there, just have a slight problem with your code `d = StrToDate(tbStartdate)`. If I leave it as it is I get the error "Variable not defined" and `tbStartdate` is highlighted. I tried replacing `tbStartdate` with `rngFound`, but then I get the error "ByRef argument type Mismatch". I think that I need to reference about 17 different variables as I have now got the form to populate correctly using this code `Me.ARLFam = Format(rngFound(1, 8).Value, "dd/mm/YYYY")` I suspect I should be replacing `tbStartdate` with `ARLFam`. Just not sure how to do it. – Ian Martin Oct 28 '20 at 13:39
The Variable `ARLFam` is just one of 17 variables that will all have to be included in the loop somehow. – Ian Martin Oct 28 '20 at 13:41
I used `tbStartdate` as example of a Textbox, replace it with the textbox name of your form - if your code lives within a Form, you should use the keyword Me (as you do already), so just try `Me.ARLFam = Format(rngFound(1, 8).Value, "dd/mm/YYYY")`. Do the same for all pairs textBox/ExcelCell that contain a date – FunThomas Oct 28 '20 at 13:46
I did that for the PopulateForm part, and that is working perfectly. I am trying to utilise your `Function StrToDate(s As String) As Date` code for when the data is placed back into their cells. This is where I am having the problem. I was trying to work out how to fit `d = StrToDate(tbStartdate) if d > 0 then ActiveSheet.cells(B2) = d` into the 17 lines I have at present, which currently consists of `If Not Me.ARLEvac = "" Then .Cells(LR, 12).Value = Me.ARLEvac`. – Ian Martin Oct 28 '20 at 14:30
I take it "d" is supposed to check the Function `StrToDate` to make sure the date is coded correctly before using the code I have to enter it back to the worksheet in the correct cells, so how do I fit "d" into the code I already have? – Ian Martin Oct 28 '20 at 14:30
Use `.Cells(LR, 12).Value = d` – FunThomas Oct 28 '20 at 14:32
I am having trouble trying to work out how to get my section `Private Sub EnterButton_Click()` to work when I add in your code `dim d as Date d = StrToDate(tbStartdate)`. How do I get it to go to the Function `StrToDate(s As String) As Date` ? I am getting a compile error "Variable not defined" and highlights `tbStartdate`. – Ian Martin Oct 28 '20 at 16:35
I already wrote: *I used `tbStartdate` as example of a Textbox, replace it with the textbox name of your form*. I don't know what else I could explain to you – FunThomas Oct 28 '20 at 16:39
I think I finally understand what to do. I need to replace `d = StrToDate(tbStartdate)` with `d = StrToDate(ARLFam)` and then repeat the process the the next 16 variables? – Ian Martin Oct 28 '20 at 16:44
I am struggling with getting your `Function StrToDate(s As String) As Date` sub routine to run within my `Private Sub EnterButton_Click() routine. I get errors "Variable not defined" if I try using `StrToDate`. Or Compile Error "Expected:List Separator if I try `Function StrToDate(s As String)`. Just tried `StrToDate (Val(Me.dateParts))` to see if I could make it work on my own. Got another Compile Error, this time it was "Method or data member not found" & `dateParts` was highlighted. What I am missing? – Ian Martin Oct 31 '20 at 13:37

European Date format in Userform

2 Answers2