Message d'erreur si le format de saisie n'est pas le bon

Bonjour à tous,

Je voudrais intégrer un msg box d'alerte "Error! You must put a number format as DD/MM/YYYY for intership starting date" si la saisie n'est pas une date.

Voici mon fichier xlsm : Excel fill form internship request ci-dessous:

2] Voici mon code ci-dessous:

Option Explicit

Sub Fill_in_form()
Dim format_date_starting_internship As Date
Dim date_input_box_starting_internship As Date
Dim interval_type As String
Dim number_of_months_to_add As Integer
Dim registered_company_name As String
Dim commercial_company_name As String

Dim Msg

date_input_box_starting_internship = InputBox("Enter internship starting date : format [DD/MM/YYYY]")
If IsDate(date_input_box_starting_internship) Then
   format_date_starting_internship = CDate(date_input_box_starting_internship)
   Range("B2") = format_date_starting_internship
Else
  MsgBox "This isn't a date, try again (with format DD/MM/YYYY)"
End If

interval_type = "m"
number_of_weeks_to_add = InputBox("Enter number of months for the internship")
Range("B3") = number_of_months_to_add
Msg = DateAdd(interval_type, number_of_months_to_add, format_date_starting_internship)
MsgBox Msg
Range("B4") = Msg
registered_company_name = InputBox("Enter registered trade name of the company")
Range("B8") = registered_company_name
commercial_company_name = InputBox("Enter commercial name of the company")
Range("B9") = commercial_company_name
End Sub

Bonjour à tous,

Je suis nul en VBA, mais dans ton cas, il suffit de choisir date dans Données==>validation de données

Crdlmt

Merci pour la réponse DjiDji59430,

Mais je souhaite réaliser cela en VBA.

Bonjour toutes et tous

Coucou DjiDji59430

note: si la date n'est pas correcte, un retour goto pour avoir le MsgBox ' Goto à 3' et on revient sur le 'goto à 5' au début

@ tester (peut-être que le format retourné est incorrect donc, à revérifier)

Option Explicit

Sub Fill_in_form()
Dim format_date_starting_internship As Date
Dim date_input_box_starting_internship
Dim interval_type As String
Dim number_of_months_to_add As Integer
Dim registered_company_name As String
Dim commercial_company_name As String
Dim number_of_weeks_to_add As Integer
Dim Msg

On Error Resume Next

5
' à rajouter ==> Dim number_of_weeks_to_add As Integer ' <<
date_input_box_starting_internship = InputBox("Enter internship starting date : format [DD/MM/YYYY]", _
"Format jour/mois/année")
If date_input_box_starting_internship = "" Then GoTo 3 'Exit Sub

'date_input_box_starting_internship = InputBox("Enter internship starting date : format [DD/MM/YYYY]")
If IsDate(date_input_box_starting_internship) Then
   format_date_starting_internship = CDate(date_input_box_starting_internship)
   Range("B2") = format_date_starting_internship
Else
3  MsgBox "This isn't a date, try again (with format DD/MM/YYYY)"
GoTo 5
End If

interval_type = "m"
number_of_weeks_to_add = InputBox("Enter number of months for the internship")
Range("B3") = number_of_months_to_add
Msg = DateAdd(interval_type, number_of_months_to_add, format_date_starting_internship)
MsgBox Msg
Range("B4") = Msg
registered_company_name = InputBox("Enter registered trade name of the company")
Range("B8") = registered_company_name
commercial_company_name = InputBox("Enter commercial name of the company")
Range("B9") = commercial_company_name
On Error GoTo 0
End Sub

Salut le fil

tu dois utiliser une boucle...

If IsDate(date_input_box_starting_internship) Then
   format_date_starting_internship = CDate(date_input_box_starting_internship)
   Range("B2") = format_date_starting_internship
Else
  MsgBox "This isn't a date, try again (with format DD/MM/YYYY)"
End If

Dans ce style

    Dim strTemp As String
    Do
        strTemp = InputBox("Entrez la date Format dd/mm/yy")
        If Not IsDate(strTemp) Then MsgBox "Entrez une date valide"

    Loop While Not IsDate(strTemp)

Merci pour l'aide jean paul.

1] Voici le fichier amélioré.

2] Voici le code amélioré.

Sub MyNewProcedure()
  Call Fill_in_form

  Call Fill_in_form2
End Sub

Sub Fill_in_form()
Dim starting_date_internship As String
Dim interval_type As String
Dim ending_date_internship As Date
Dim number_of_months_to_add As Integer
Dim objectives_and_missions_internship As String
Dim registered_company_name As String
Dim commercial_company_name As String
Dim VATno_number_company As Variant
Dim contact_number_company As Variant
Dim department_internship As String

Do
  starting_date_internship = InputBox("Enter starting date internship [DD/MM/YYYY]")
  If Not IsDate(starting_date_internship) Then MsgBox "Enter a valid format for starting date internship DD/MM/YYYY"
Loop While Not IsDate(starting_date_internship)
Range("B2") = starting_date_internship
interval_type = "m"
number_of_months_to_add = InputBox("Enter number of months for the internship")
Range("B3") = number_of_months_to_add
ending_date_internship = DateAdd(interval_type, number_of_months_to_add, starting_date_internship)
Range("B4") = ending_date_internship
objectives_and_missions_internship = InputBox("Enter objectives and missions during internship")
Range("B5") = objectives_and_missions_internship
registered_company_name = InputBox("Enter registered trade name of the company")
Range("B8") = registered_company_name
commercial_company_name = InputBox("Enter commercial name of the company")
Range("B9") = commercial_company_name
Do
    VATno_number_company = InputBox("Please enter the VATno number of the company")
    If Not Len(VATno_number_company) = 10 Then MsgBox VATno_number_company & " is not a 10 digit number"
Loop Until Len(VATno_number_company) = 10
Range("B10") = VATno_number_company
contact_number_company = InputBox("Enter contact number of the company")
Range("B15") = contact_number_company
department_internship = InputBox("Enter department of internship")
Range("B17") = department_internship
End Sub

Sub Fill_in_form2()

Dim first_name_supervisor As String
Dim last_name_supervisor As String
Dim Gender_OptionButton1 As OptionButton
Dim gender_supervisor As String
Dim job_title_supervisor As String
Dim email_adress_supervisor As String
Dim professional_phone_number_supervisor As Variant
Dim frequency_of_pay As String

first_name_supervisor = InputBox("Enter first name of supervisor")
Range("B19") = first_name_supervisor
last_name_supervisor = InputBox("Enter last name name of supervisor")
Range("B20") = last_name_supervisor
If Gender_OptionButton1.Value Then
gender_supervisor = "M"
Else
gender_supervisor = "F"
End If
Range("B21") = gender_supervisor
job_title_supervisor = InputBox("Enter job title of supervisor")
Range("B22") = job_title_supervisor
email_adress_supervisor = InputBox("Enter email adress of supervisor")
Range("B23") = email_adress_supervisor
professional_phone_number_supervisor = InputBox("Enter professionnal phone number of supervisor")
Range("B24") = professional_phone_number_supervisor
frequency_of_pay = InputBox("Enter frequency of pay in local currency [daily, monthly or one-off payment]")
Range("B26") = frequency_of_pay

End Sub