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
|
Private Sub Worksheet_Change(ByVal Target As Range)
Dim choix As String, nom As String, prénom As String, entreprise As String, heure As String, jour As String, sup As String
choix = ActiveCell.Value
nom = ActiveCell.Offset(0, -2).Value
prénom = ActiveCell.Offset(0, -1).Value
entreprise = ActiveCell.Offset(0, -5).Value
heure = ActiveCell.Offset(0, -6)
jour = ActiveCell.Offset(0, -7)
Select Case choix
Case Is = "Remplacé (absent)"
confirm_rempl = MsgBox("Voulez-vous remplacer ce RDV ?", vbYesNo, "Confirmation")
If confirm_rempl = vbNo Then ActiveCell.ClearContents
If confirm_rempl = vbYes Then
With Sheets("Absent")
.Rows("3").Insert
.Range("j3") = prénom
.Range("i3") = nom
.Range("h3") = heure
.Range("g3") = jour
End With
With ActiveCell.Offset(0, -3)
.ClearComments
.AddComment
.Comment.Visible = True
.Comment.Text Text:=nom & " " & prénom & " " & " " & entreprise & " " & "absent au RDV !"
End With
End If
MsgBox ("Maintenant, veuillez renseigner les champs du nouveau RDV")
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -5)).ClearContents
Case Is = "Absent !"
confirm_absent = MsgBox("La personne ne s'est pas présenté au RDV ?", vbYesNo, "Confirmation")
If confirm_absent = vbNo Then ActiveCell.ClearContents
If confirm_absent = vbYes Then
With Sheets("Absent")
.Rows("3").Insert
.Range("j3") = prénom
.Range("i3") = nom
.Range("h3") = heure
.Range("g3") = jour
End With
End If
Case Is = ""
ActiveCell.Offset(0, -3).ClearComments
Case Else: Exit Sub
End Select
End Sub |
Partager