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
| Private Sub CdButton_Update_AP_Click()
'************************************************************************
'Check filled textboxes and send the updated data to the PA_IFE
'************************************************************************
Application.ScreenUpdating = False
'------------------------------------------------------------------------
'Check the modification and make the updates onto the worksheet "Plan d'Actions IFE"
'------------------------------------------------------------------------
If CboBox_IFE_ID.Value <> "" Then
Set Find_IFE = WsIFEAP.Range("PA_IFE[Réf. IFE]").Find(What:=CboBox_IFE_ID, LookIn:=xlValues, SearchOrder:=xlByRows)
IFE = Find_IFE.Row
count_IFE = Application.WorksheetFunction.CountIf(Range("PA_IFE[Réf. IFE]"), CboBox_IFE_ID)
End If
With Worksheets("Plan d'Actions IFE").Activate
For j = 1 To count_IFE
'Check that the required fields "Pilote" and "Délais" are filled
If Me.Controls("TxtBox_Action" & j) <> "" And Me.Controls("TxtBox_Pilote" & j) = "" Or Me.Controls("TxtBox_Deadline" & j) = "" And WsIFEAP.Cells(IFE, 13) <> "" Then
MsgBox "Veuillez désigner un pilote et/ou définir un délais", vbExclamation + vbOKOnly
Exit Sub
End If
'When an action is modify
If Me.Controls("TxtBox_Action" & j).Value <> Cells(IFE, 7) Then
Cells(IFE, 7) = Cells(IFE, 7) & vbNewLine & Me.Controls("TxtBox_Action" & j).Value & " (" & Label_User.Caption & " - " & CDate(Format(CStr(Date), "dd/mm/yyyy")) & ")"
End If
'When a DI is modify
If Me.Controls("TxtBox_DI" & j).Value <> Cells(IFE, 8) Then
Cells(IFE, 8) = Me.Controls("TxtBox_DI" & j).Value
End If
'When a Pilote is modify
If Me.Controls("TxtBox_Pilote" & j).Value <> Cells(IFE, 9) Then
Cells(IFE, 9) = Me.Controls("TxtBox_Pilote" & j).Value
End If
'Check if the date in the cell "Délais" changes and apply the new deadline date value in the column "Délais Révisé" with author name
'and date of modification
If Me.Controls("TxtBox_Deadline" & j).Value <> Cells(IFE, 10) And Cells(IFE, 11) = "" Then
Cells(IFE, 11) = CDate(Format(CStr(Me.Controls("TxtBox_Deadline" & j).Value), "dd/mm/yyyy"))
Cells(IFE, 12) = Label_User.Caption & " (" & CDate(Format(CStr(Date), "dd/mm/yyyy")) & ")"
End If
If Me.Controls("TxtBox_Deadline" & j).Value <> Cells(IFE, 10) And Cells(IFE, 11) <> "" Then
Cells(IFE, 11) = CDate(Format(CStr(Me.Controls("TxtBox_Deadline" & j).Value), "dd/mm/yyyy"))
Cells(IFE, 12) = Cells(IFE, 12) & vbNewLine & Label_User.Caption & " (" & CDate(Format(CStr(Date), "dd/mm/yyyy")) & ")"
End If
IFE = IFE + 1
Next j
End With
Unload Me
End Sub |
Partager