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
| Private WS As Worksheet
Private CTRL As Control
Private Sub MultiPage1_Change()
End Sub
Private Sub UserForm_Initialize()
Dim J As Long
Dim I As Integer
Set WS = Sheets("Redevance")
ComboBox2.List() = Array("", "Rue", "Av", "Bd", "Allée", "Place", "Ch", "Imp", "Square", "Quai", "RP")
With Me.ComboBox1
For J = 8 To WS.Range("A" & Rows.Count).End(xlUp).Row
.AddItem WS.Range("A" & J)
Next J
End With
'pourquoi cette boucle ? à l'initialisation tes textboxes sont par défaut "Visible"
For I = 1 To 79
Me.Controls("TextBox" & I).Visible = True
Next I
End Sub
Private Sub ComboBox1_Change()
Dim Ligne As Long
Dim I As Integer
If ComboBox1 = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
Ligne = Me.ComboBox1.ListIndex + 8
For Each CTRL In Me.Controls
If CTRL.Tag <> "" Then CTRL.Value = WS.Cells(Ligne, CByte(CTRL.Tag)).Value
If TypeOf CTRL Is MSForms.CheckBox Then CTRL.Value = IIf(WS.Cells(Ligne, CByte(CTRL.Tag)).Value = "", False, True)
Next CTRL
Me.CommandButton1.Visible = False
End Sub
Private Sub CommandButton1_Click()
Dim L As Integer
Dim CTRL As Control
If MsgBox("Confirmez-vous linsertion de ce nouveau contact ?", vbYesNo, "Demande de confirmation dajout") = vbYes Then
L = WS.Cells(Application.Rows.Count, 1).End(xlUp).Row + 1
For Each CTRL In Me.Controls
If CTRL.Tag <> "" Then WS.Cells(L, CByte(CTRL.Tag)).Value = CTRL.Value
If TypeOf CTRL Is MSForms.CheckBox Then WS.Cells(Ligne, CByte(CTRL.Tag)).Value = IIf(CTRL.Value = True, "X", "")
Next CTRL
End If
End Sub
Private Sub CommandButton2_Click()
Dim Ligne As Long
Dim I As Integer
If MsgBox("Confirmez-vous la modification de ce contact ?", vbYesNo, "Demande de confirmation de modification") = vbYes Then
If Me.ComboBox1.ListIndex = -1 Then Exit Sub
Ligne = Me.ComboBox1.ListIndex + 8
WS.Cells(Ligne, "B") = ComboBox2
For Each CTRL In Me.Controls
If CTRL.Tag <> "" Then WS.Cells(Ligne, CByte(CTRL.Tag)).Value = CTRL.Value
If TypeOf CTRL Is MSForms.CheckBox Then WS.Cells(Ligne, CByte(CTRL.Tag)).Value = IIf(CTRL.Value = True, "X", "")
Next CTRL
End If
End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub |
Partager