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
| Private Sub CommandButton_SaveData_Click()
Dim I As Long, Plage, Fnd As Range, Ligne, test, test2 As Long, Nom As String
Dim Ctrl As Control
Application.DisplayAlerts = False
If MsgBox("Are you sure you want to save your changes?", vbYesNo, "Confirmation request") = vbYes Then
With Sheets("Param") ' charge les param?tre
Set Plage = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
End With
With Sheets("DB Application")
Sheets("DB Application").Select
Ligne = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
' cherche la val dans la colonne A
Set Fnd = .Range("A3", .Cells(Rows.Count, "A").End(xlUp)).Find( _
What:=Me.TextBox_EquipementSAP.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
If Not Fnd Is Nothing Then ' si trouve la ligne d?ja enregistr?
For I = 1 To 89 ' travaille sur les colonne 1 ? 89
Nom = Plage(I).Offset(, 1)
If Nom <> "" Then
If TypeName(Me.Controls(Nom)) = "CheckBox" Then ' si checkbox activer
If Me.Controls(Nom).Value = True Then
Fnd.EntireRow.Select
test2 = .Cells(Ligne, I) = "YES"
?????.Replace What:=?????, Replacement:=???'
End If
Else
Fnd.EntireRow.Select
test = Me.Controls(Nom).Value
?????.Replace What:=?????, Replacement:=???'
End If
End If
Next I
Else ' si ?lement n'est pas encore enregistr?
For I = 1 To 89
Nom = Plage(I).Offset(, 1)
If Nom <> "" Then
If TypeName(Me.Controls(Nom)) = "CheckBox" Then
If Me.Controls(Nom).Value = True Then
.Cells(Ligne, I) = "YES"
End If
Else
.Cells(Ligne, I) = Me.Controls(Nom).Value
End If
End If
Next I
End If
End With
End If
Application.DisplayAlerts = True
End Sub |
Partager