Finalisation de date sur USF
Bonjour
J'ai un USF avec trois combobox qui donne Jour, Mois et Année et j'ai un textbox auquel j'aimerais qui me donne la date au complet aprés avoir rempli les trois combobox au format (jj mmm yyyy)
Voici mon code
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
| Option Explicit
Dim lig&, flag As Boolean 'mémorisation
Private Sub CommandButton1_Click()
ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
ComboBox3.SetFocus
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Activate()
Dim i As Integer
For i = 2012 To 2020
ComboBox1.AddItem i
Next
For i = 1 To 12
ComboBox2.AddItem Application.Proper(Format(CDate("1/" & i), "mmmm"))
Next
For i = 1 To 31
ComboBox3.AddItem i
Next
'CommandButton1.Visible = False
End Sub
Private Sub CheckBox1_Change()
If CheckBox1 And Not flag Then
ComboBox1 = Year(Date)
ComboBox2.ListIndex = Month(Date) - 1
ComboBox3 = Day(Date)
End If
End Sub
Private Sub ComboBox1_Change()
Recherche
End Sub
Private Sub ComboBox2_Change()
Recherche
End Sub
Private Sub ComboBox3_Change()
Recherche
End Sub
Sub Recherche()
Dim dat$, tablo, i&
lig = 0
CheckBox1 = False
'CommandButton1.Visible = False
If ComboBox1.ListIndex < 0 Then ComboBox1 = ""
If ComboBox2.ListIndex < 0 Then ComboBox2 = ""
If ComboBox3.ListIndex < 0 Then ComboBox3 = ""
If ComboBox1 = "" Or ComboBox2 = "" Or ComboBox3 = "" Then Exit Sub
dat = ComboBox3 & "/" & ComboBox2.ListIndex + 1 & "/" & ComboBox1
If Not IsDate(dat) Then MsgBox "Date non valide !", 48: ComboBox3.SetFocus: Exit Sub
If CDate(dat) = Date Then flag = True: CheckBox1 = True: flag = False
With Feuil4
tablo = .Range("B6:D" & .[B65536].End(xlUp).Row)
For i = 1 To UBound(tablo)
If ComboBox3 = CStr(tablo(i, 1)) And ComboBox2 = tablo(i, 2) _
And ComboBox1 = CStr(tablo(i, 3)) Then lig = i + 5: Exit For
Next
' CommandButton1.Caption = IIf(lig, "Modifier", "Créer")
' CommandButton1.Visible = True
' If lig = 0 Then Exit Sub
'TextBox10 = .Cells(lig, "E")
'TextBox11 = .Cells(lig, "F")
'TextBox12 = .Cells(lig, "I")
'TextBox15 = .Cells(lig, "J")
'TextBox13 = .Cells(lig, "L")
'TextBox14 = .Cells(lig, "N")
End With
End Sub |
Je vous remercie d'avance
Cordialement
Max
Il reste à tester les jours de fin de mois
Voici un exemple de code....
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
| ' Déclaration des variables au niveau module
Dim Vjour As Integer
Dim VmoisT As String
Dim VmoisI As Integer
Dim van As Integer
Dim VMadate As Date
Private Sub CommandButton1_Click()
ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
ComboBox3.SetFocus
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Activate()
Dim i As Integer
For i = 2012 To 2020
ComboBox1.AddItem i
Next
For i = 1 To 12
ComboBox2.AddItem Application.Proper(Format(CDate("1/" & i), "mmmm"))
Next
For i = 1 To 31
ComboBox3.AddItem i
Next
'CommandButton1.Visible = False
End Sub
Private Sub Combobox3_Change()
Vjour = Me.ComboBox3.Value
End Sub
Private Sub ComboBox2_Change()
VmoisT = Me.ComboBox2.Value
Select Case VmoisT
Case Is = "Janvier"
VmoisI = 1
Case Is = "Février"
VmoisI = 2
Case Is = "Mars"
VmoisI = 3
Case Is = "Avril"
VmoisI = 4
Case Is = "Mai"
VmoisI = 5
Case Is = "Juin"
VmoisI = 6
Case Is = "Juillet"
VmoisI = 7
Case Is = "Août"
VmoisI = 8
Case Is = "Septembre"
VmoisI = 9
Case Is = "Octobre"
VmoisI = 10
Case Is = "Novembre"
VmoisI = 11
Case Is = "Décembre"
VmoisI = 12
End Select
End Sub
Private Sub ComboBox1_Change()
van = Me.ComboBox1.Value
VMadate = DateSerial(van, VmoisI, Vjour)
VMadate = Format(DateSerial(van, VmoisI, Vjour), "dd mmmm yyyy")
Me.TextBox1.Value = VMadate
End Sub |
