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 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118
| Option Explicit 'oblige à déclarer toutes les variables
Private Sub ComboBox1_Change()
End Sub
Private Sub ComboBox1_Click()
Dim i As Byte
CommandButton3.Visible = True
CommandButton4.Visible = True
Label27.Caption = "mode : Modification"
With ComboBox1
For i = 1 To 14
Controls("TextBox" & i) = Sheets("Liste").Cells(.List(.ListIndex, 1), i)
Next i
End With
End Sub
Private Sub CommandButton1_Click() 'bouton "Ok"
Dim Dl As Long 'déclare la variable Dl (Dernière ligne)
Dim x As Byte 'déclare la variable x
With Sheets("Liste")
.Activate 'active l'onglet "Liste"
Dl = .Range("A65536").End(xlUp).Row + 1 'définit la variable Dl
'place les donnés dans le tableau
'On vérifie que les champs obligatoires sont correctement remplis
If TextBox1.Text = "" Then
MsgBox "Veuillez entrer la date du devis", vbCritical, "Champs manquant"
TextBox1.SetFocus
ElseIf TextBox2.Text = "" Then
MsgBox "Veuillez remplir le nom du patient", vbCritical, "Champs manquant"
TextBox2.SetFocus
ElseIf TextBox7.Text = "" Then
MsgBox "Veuillez entrer le nombre de devis", vbCritical, "Champs manquant"
TextBox7.SetFocus
ElseIf TextBox8.Text = "" Then
MsgBox "Veuillez entrer le montant du devis", vbCritical, "Champs manquant"
TextBox8.SetFocus
Else
For x = 1 To 14 'boucle sur 14 éléments
.Cells(Dl, x).Value = Me.Controls("TextBox" & x).Value
Next x
Unload Me 'vide et ferme l'UserFOrm
End If
'tri selon le nom
.Range("A2").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
Private Sub CommandButton2_Click() 'bouton "Annuler"
Unload Me 'vide et ferme l'UserFOrm
End Sub
Private Sub CommandButton3_Click()
Dim i As Byte
With ComboBox1
For i = 1 To 14
Sheets("Liste").Cells(.List(.ListIndex, 1), i) = Controls("TextBox" & i)
Controls("TextBox" & i) = ""
Next i
.ListIndex = -1
End With
CommandButton3.Visible = False
CommandButton4.Visible = False
Label27.Caption = "mode : création"
initialisecombo
End Sub
Private Sub CommandButton4_Click()
Dim i As Byte
With ComboBox1
For i = 1 To 14
Controls("TextBox" & i) = ""
Next i
.ListIndex = -1
End With
CommandButton3.Visible = False
CommandButton4.Visible = False
Label27.Caption = "mode : création"
End Sub
Private Sub CommandButton5_Click() 'bouton "Fermer"
Unload Me 'vide et ferme l'UserForm
End Sub
Private Sub UserForm_Initialize()
Label27.Caption = "mode : création"
initialisecombo
End Sub
Public Sub initialisecombo()
Dim i As Integer
With ComboBox1
.Clear
.ColumnCount = 2
.ColumnWidths = "50;0"
End With
With Sheets("Liste")
For i = 2 To .Range("a65536").End(xlUp).Row
'Afficher Colonnes Nom Prénom et Date dans ComboBox1
ComboBox1.AddItem
ComboBox1.List(ComboBox1.ListCount - 1, 0) = .Cells(i, 2) & " " & .Cells(i, 3) & " - " & .Cells(i, 1)
ComboBox1.List(ComboBox1.ListCount - 1, 1) = i
Next i
End With
End Sub |
Partager