Option Explicit
Dim WS As Worksheet
'Dim WS As Worksheet ' Pas besoin, il existe déjà un objet Worksheet de la rubrique Microsoft Excel Objets _
qui représente auprès de VBA la feuille Excel "PARAMETRE": c'est Feuil3 (À votre place je le renommerais FParam)
Private LCou As Long ' Numéro de la ligne courante. Vaut 0 si création en cours.
Private VLgn() As Variant ' Valeurs de la lignes en cours de mise à jour.
Private Sub BoutQuitte_Click()
Unload Me
End Sub
Private Sub CmdAjout_Click()
Dim CTRL As Control 'Variable pour la collection des controls
Dim L As Integer, y As Integer 'Variable pour connaitre le numéro de derniere ligne vide
Dim LeChoix As String
Dim Ctr As Control, col As Integer, lig As Long
With Worksheets("PARAMETRE")
For Each Ctr In Me.Controls
If TypeName(Ctr) = "ComboBox" Then
If Ctr.ListIndex = -1 And Ctr.Value <> "" Then
col = Right(Ctr.Name, Len(Ctr.Name) - 8)
lig = .Cells(Cells.Rows.Count, col).End(xlUp).Row + 1
.Cells(lig, col) = Ctr
End If
End If
Next
End With
L = ThisWorkbook.Worksheets("BDD").Range("A65536").End(xlUp).Row + 1 ' On identifie la dernière ligne vide en partant du bas
For y = 2 To 30
Select Case y
Case 1 To 10
With Feuil2
.Cells(L, y) = Me.Controls("ComboBox" & y).Value
End With
Case 11
LeChoix = Switch(Me.OptionButton4, "oui", Me.OptionButton5, "Non")
Feuil2.Cells(L, y) = LeChoix
Case 12
LeChoix = Switch(Me.OptionButton10, "Homme", Me.OptionButton11, "Femme")
Feuil2.Cells(L, y) = LeChoix
Case 13
LeChoix = Switch(Me.OptionButton12, "Homme", Me.OptionButton13, "Femme")
Feuil2.Cells(L, y) = LeChoix
Case 14 To 26
With Feuil2
.Cells(L, y) = Me.Controls("ComboBox" & y).Value
End With
Case 27
LeChoix = Switch(Me.OptionButton14, "Conforme", Me.OptionButton15, "Non Conforme")
Feuil2.Cells(L, y) = LeChoix
Case 28
LeChoix = Switch(Me.OptionButton16, "Conforme", Me.OptionButton17, "Non Conforme")
Feuil2.Cells(L, y) = LeChoix
Case 29 To 30
With Feuil2
.Cells(L, y) = Me.Controls("ComboBox" & y).Value
End With
End Select
Next y
End Sub
Private Sub ComboBox1_Change()
LCou = ComboBox1.ListIndex + 1
If LCou > 0 Then
VLgn = Feuil2.Rows(1 + LCou).Resize(, 30).Value
Else
ReDim VLgn(1 To 1, 1 To 30) ' Comme ça les instructions après vident les contrôles
End If
ComboBox2.Text = VLgn(1, 2)
ComboBox3.Text = VLgn(1, 3)
ComboBox4.Text = VLgn(1, 4)
ComboBox5.Text = VLgn(1, 5)
ComboBox6.Text = VLgn(1, 6)
ComboBox7.Text = VLgn(1, 7)
ComboBox8.Text = VLgn(1, 8)
End Sub
Private Sub ComboBox25_Change()
If UCase(ComboBox25) = "OUI" Then ComboBox26.Visible = True
End Sub
Private Sub UserForm_Activate()
Me.ComboBox1.SetFocus
End Sub
Private Sub UserForm_Initialize()
Dim TE(), TS(), L As Long
TE = Feuil2.UsedRange.Resize(, 2).Value
ReDim TS(0 To UBound(TE, 1) - 2)
For L = 2 To UBound(TE, 1)
TS(L - 2) = Format(TE(L, 1) + TE(L, 2), "dd/mm/yyyy hh:mm")
Next L
Me.ComboBox1.List = TS
ComboBox26.Visible = False
End Sub