Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 And Target.Row > 5 And Target.Count = 1 Then
If Target.Value = "AFGEHANDELD" Then
If MsgBox(" Wil je deze offerte verplaatsen" & vbNewLine & vbNewLine & " naar Afgehandelde offertes?" & vbNewLine & vbNewLine & vbNewLine & " Let op! Indien nodig: lopend offertedossier verplaatsen!", vbYesNo, "Attentie") = vbNo Then
ActiveCell.Value = HuidigeWaarde
Exit Sub
End If
If Target.Offset(, -3).Value = "JA" Then
If Target.Offset(, -1).Value <> "" Then
ActiveSheet.Unprotect Password:="123"
Range("M" & Target.Row).Resize(, 2) = Array(Format(Now, "mm-dd-yy"), Environ("username"))
Sheets("Afgehandelde offertes").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 17) = Cells(Target.Row, 1).Resize(, 17).Value
[U][B] Sheets("Afgehandelde offertes").ListObjects("Tabel1").Resize Range("$A$5:$N" & Sheets("Afgehandelde offertes").ListObjects("Tabel1").Range.Rows.Count + 5)[/B][/U]
Rows(Target.Row).Delete
ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, _
AllowFiltering:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Else
MsgBox "Gesloten bij verzekeraar is niet gevuld!", vbCritical
ActiveCell.Value = HuidigeWaarde
Exit Sub
End If
ElseIf Target.Offset(, -3).Value = "NEE" Then
ActiveSheet.Unprotect Password:="123"
Cells(Target.Row, 9).ClearContents
Range("M" & Target.Row).Resize(, 2) = Array(Format(Now, "mm-dd-yy"), Environ("username"))
Sheets("Afgehandelde offertes").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 17) = Cells(Target.Row, 1).Resize(, 17).Value
[U][B] Sheets("Afgehandelde offertes").ListObjects("Tabel1").Resize Range("$A$5:$N" & Sheets("Afgehandelde offertes").ListObjects("Tabel1").Range.Rows.Count + 5)[/B][/U]
Rows(Target.Row).Delete
ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, _
AllowFiltering:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Else
MsgBox "Kies JA of Nee in kolom Gesloten!", vbCritical
ActiveCell.Value = HuidigeWaarde
Exit Sub
End If
End If
End If
End Sub