Option Explicit
Private Sub ComboBox1_Change() 'Recherche : item modifié => MAJ des champs
Dim lig&: lig = ComboBox1.ListIndex
If lig = -1 Then
'si on a appuyé sur Échap, on efface les champs
ComboBox2 = "": NOM = "": ADRESSE = "": ComboBox3.ListIndex = -1: CP = ""
TextBox2 = "": PriseEnCharge = "": Trajet = "": FinCourse = "": Exit Sub
End If
With Worksheets("Client").Cells(lig + 2, 2)
'on remplit les champs avec les cellules de la feuille "Client"
ComboBox2 = .Value 'Civilité
NOM = .Offset(, 1) 'NOM
ADRESSE = .Offset(, 2) 'ADRESSE
ComboBox3 = .Offset(, 4) 'VILLE
TextBox2 = .Offset(, 7) 'Date Évènement
End With
End Sub
Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 27 Then Unload Me 'Échap sur le champ Recherche
End Sub
Private Sub ComboBox3_Change() 'VILLE modifiée => mise à jour du Code Postal
If ComboBox3.ListIndex <> -1 Then CP = Format(ComboBox3.Column(1), "00 000")
End Sub
Private Sub CP_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'CP
If CP <> "" Then CP = Format(CP, "00 000") 'on formate le Code Postal
End Sub
Private Sub TextBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean) 'Date Évènement
Calendar.Affiche Me, Me.TextBox1.Name, ActiveControl.Name: Cancel = True
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'Date Évènement
TextBox2 = Format(TextBox2, "dddd d mmmm yyyy ") 'on formate la Date Évènement
End Sub
Private Sub Valider1_Click() '1er bouton Valider
Application.ScreenUpdating = 0
With [Devis!B2]
If Jour <> "" Then .Offset(, 1) = Jour 'DATE (du jour)
If TextBox1 <> "" Then .Offset(2) = TextBox1 'N° Devis
If ComboBox2 <> "" Or NOM <> "" _
Then .Offset(4, 1) = ComboBox2 & " " & NOM 'Civilité & NOM
If ADRESSE <> "" Then .Offset(5, 1) = ADRESSE 'ADRESSE
If CP <> "" Or ComboBox3 <> "" _
Then .Offset(6, 1) = CP & " " & ComboBox3 'CP & VILLE
If TextBox2 <> "" Then .Offset(9) = TextBox2 'Date Évènement
End With
Application.ScreenUpdating = -1
End Sub
Private Sub Valider2_Click() '2ème bouton Valider
Application.ScreenUpdating = 0
With [Devis!A14]
If PriseEnCharge <> "" Then .Value = PriseEnCharge 'Prise en Charge
If Trajet <> "" Then .Offset(2) = Trajet 'Trajet
If FinCourse <> "" Then .Offset(4) = FinCourse 'Fin de Course
End With
Application.ScreenUpdating = -1
End Sub
Private Sub Estimatif_Click() 'bouton rouge Estimatif
Estimatif_Coût.Show 'affiche le UserForm Estimatif
End Sub
Sub MAJ_HT(Q$, s$) 'Mise à jour du Montant HT
Dim k As Byte: s = Replace$(s, Chr$(160), ""): k = Len(s)
If Right$(s, 2) = " €" Then k = k - 2: s = Left$(s, k)
If Q = "" Or k = 0 Then lblHT.Caption = "": Exit Sub
lblHT.Caption = IIf(Val(Q) = 0 Or Val(s) = 0, _
0, Format(Q * Val(s), "#,##0 €"))
End Sub
Private Sub Désignation_Change() 'Désignation : item modifié => MAJ PU & HT
Dim k%: k = Désignation.ListIndex
lblPU = Format(Round(Val(Replace$([Prestation!B13].Offset(k), _
",", ".")), 0), "#,##0 €"): MAJ_HT Qté, lblPU.Caption
End Sub
Private Sub Qté_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'Qté
MAJ_HT Qté, lblPU.Caption
End Sub
Private Sub Ajouter_Click()
Application.ScreenUpdating = 0
With Worksheets("Devis")
With .ListObjects("ListDevis").ListRows.Add()
If Désignation.ListIndex > -1 Then .Range(1, 1) = Désignation.Value
If IsNumeric(Qté.Text) Then .Range(1, 2) = CDbl(Qté.Text)
If IsNumeric(lblPU.Caption) Then .Range(1, 3) = CDbl(lblPU.Caption)
If IsNumeric(lblHT.Caption) Then .Range(1, 4) = CDbl(lblHT.Caption)
End With
End With
Application.ScreenUpdating = -1
End Sub
Private Sub ArchiverDevis_Click()
Dim A
With Worksheets("Archive Devis").ListObjects(1).ListRows.Add()
A = Array(TextBox1.Value, Jour.Value, , NOM, ADRESSE, CP & " " & ComboBox3, _
TextBox1, Désignation, CDbl(Qté.Text), CDbl(lblPU.Caption), _
Int(CDbl(lblPU.Caption) * CDbl(Qté.Text)), PriseEnCharge, Trajet, FinCourse, "")
.Range.Resize(, UBound(A) + 1).Value = A
End With
End Sub
Private Sub Quitter_Click()
Dim sh As Worksheet: Set sh = Worksheets("Devis")
With sh.ListObjects("ListDevis")
If Not .DataBodyRange Is Nothing Then
If MsgBox("Êtes-vous certain de vouloir vider le tableau ?", _
vbYesNo, "Demande de confirmation") = vbYes Then
.DataBodyRange.Delete
sh.Range("C2, B4, C6, C7, C8, B11, A14, A16, A18") = ""
End If
End If
End With
Unload Me: Accueil.Show 0
End Sub
Private Sub UserForm_Initialize()
Dim chn$, lig&, n&: Jour = Format(Date, "dd/mm/yyyy"): lig = 2
Do
chn = Worksheets("Client").Cells(lig, 3): If chn = "" Then Exit Do
ComboBox1.AddItem chn: lig = lig + 1 'liste déroulante Recherche
Loop
Désignation.List = Worksheets("Prestation").Range("ListForfaits").Value
n = Worksheets("Archive Devis").ListObjects("ArchiveDevis").ListRows.Count + 1
TextBox1 = Year(Date) & "-" & Format(n, "000") 'N° Devis
End Sub