Magic_Doctor
Board Regular
- Joined
- Mar 18, 2009
- Messages
- 56
Hello,
To control date entries, I wrote this:
- In a standard module:
It seems to be working well. On the other hand, if I enter, for example, 12320 (which has nothing to do with 12/3/20), the error is not recognized and it returns me, of course, 23/09/1933.
I'm sorry, but I use the Latin notation for dates (France / Spain / Italy ...).
To try to solve the problem, I added this:
- In the sheet module:
It works well, however there is a problem, I would say crippling, when you just select the cell where you enter the date. Once selected, you have to enter something, otherwise you are left with a result that is at least disconcerting for those who are not champions of mental arithmetic. The last problem is, therefore, how to make so that, when selecting said cell, it keeps an intelligible result? But maybe that's not possible ...
To control date entries, I wrote this:
- In a standard module:
VBA Code:
Function VérifieEntréeDate(cel As Variant) As Boolean
VérifieEntréeDate = IsDate(cel.Text) And Format(cel.Text, "dd/mm/yyyy") = cel.Text
End Function
I'm sorry, but I use the Latin notation for dates (France / Spain / Italy ...).
To try to solve the problem, I added this:
- In the sheet module:
VBA Code:
Option Explicit
Public remember As Date
----------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [MaDate]) Is Nothing Then
If VérifieEntréeDate([MaDate]) = True Then 'the entry is valid
[Vérif] = True
[MaDateBis] = [MaDate] 'the cell named "MaDateBis" retrieves the new valid entered date
Else 'we brought in anything
[Vérif] = False
[MaDate] = remember 'the cell takes the last valid date
End If
[Format] = TypeName([MaDate].Value) 'format of the cell where the date is entered
' Target.Select
End If
End Sub
----------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [MaDate]) Is Nothing Then
remember = [MaDateBis] 'as soon as you select the cell named "MaDate", the "remember" variable retrieves the value of the last valid entry, in case ...
Target.NumberFormat = "General" 'we exit from the "Date" format for a possible entry
[Format] = TypeName([MaDate].Value) 'format of the cell where the date is entered
End If
End Sub