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
| Private Sub ComboBox1_Change()
For i = 3 To 9
Me.Controls("TextBox" & i - 2) = Sheets("Formulaire").Cells(ComboBox1.ListIndex + 2, i)
Next
Me.ComboBox2 = Cells(ComboBox1.ListIndex + 2, 2)
Me.modifier.Enabled = True
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub modifier_Click()
ligne = ComboBox1.ListIndex + 2
enregistrement ligne
modifier.Enabled = False
End Sub
Private Sub nouveau_Click()
ligne = Sheets("Formulaire").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
enregistrement ligne, True
End Sub
Private Sub UserForm_Initialize()
liste
End Sub
Sub enregistrement(ligne, Optional Lnew = False)
first = IIf(Lnew, Cells(ligne, 1).Offset(-1, 0).Value + 1, ComboBox1.Value)
Dim CONTct
With Me
CONTct = Array(first, .ComboBox2.Value, .TextBox1.Text, .TextBox2.Text, .TextBox3.Text, .TextBox4.Text, .TextBox5.Text, .TextBox6.Text, .TextBox7.Text)
End With
Sheets("Formulaire").Cells(ligne, 1).Resize(1, UBound(CONTct)) = CONTct
liste
End Sub
Sub liste()
Me.ComboBox2.List = Array("Mr", "Mme", "Melle")
Me.ComboBox1.List() = Sheets("Formulaire").Range("A2:A" & Sheets("Formulaire").Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub |
Partager