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
| Private Sub CommandButton1_Click()
Dim Dcel As Range 'variable qui ira chercher la 1ère cellule vide
Dim i As Integer, verif As Byte
verif = 0
For i = 1 To 30
If Me.Controls("OptionButton" & i) Then verif = verif + 1
Next i
If TextBox1 <> "" Then verif = verif + 1
If TextBox3 <> "" Then verif = verif + 1
If verif <> 5 Then MsgBox "Vérifier vos saisies": Exit Sub
For i = 1 To 30
If Me.Controls("OptionButton" & i) Then verif = verif + 1
Next i
With Sheets("Données iso") 'With evite de répéter le nom de feuille
Set Dcel = .Range("A" & .Rows.Count).End(xlUp)(2, 1) 'la 1ère cellule vide
End With
Dcel = Label1: Dcel(1, 2) = CDate(Label2): Dcel(1, 3) = Label3
'ci-dessous, on cherche le numero de semaine
Dcel(1, 4).FormulaR1C1 = "=INT((RC[-2]-SUM(MOD(DATE(YEAR(RC[-2]-MOD(RC[-2]-2,7)+3),1,1),{1E+99;7})*{1;-1})+5)/7)"
'et on ajoute un "S"
Dcel(1, 4).Value = "S" & Dcel(1, 4).Value
'ci-dessous le mois
Dcel(1, 5) = Month(CDate(Dcel(1, 2)))
'pour les 1er boutons d'option
For i = 1 To 3
If Me.Controls("OptionButton" & i) Then Dcel(1, 6) = Me.Controls("OptionButton" & i).Caption: Exit For
Next i
Dcel(1, 7) = TextBox1
If OptionButton4 Then
Dcel(1, 8) = OptionButton4.Caption
Else
Dcel(1, 8) = OptionButton5.Caption
End If
'ci-dessous on boucle sur le reste des controles
For i = 8 To 30
If Me.Controls("OptionButton" & i) Then Dcel(1, 9) = Me.Controls("OptionButton" & i).Caption: Exit For
Next i
Dcel(1, 10) = TextBox2
Dcel(1, 13) = TextBox3
Unload Me
UserForm1.Show
End Sub |
Partager