Enlève ceci de ta macro
CommandButton1_Click
C'est ce qui coince.
Car tu relances la macro après avoir remis à zéro la zone [douchette], du coup il ne trouve pas !!
Je te conseilles aussi d'ajouter Application.EnableEvents
Private Sub Worksheet_Change(ByVal Target As Range)
Dim derlig As Long
Dim qte As String
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("H1")) Is Nothing Then ' saisie douchette
derlig = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Unprotect
Application.EnableEvents = False
Cells(derlig + 1, 1).Value = [douchette].Value
Cells(derlig + 1, 2).Value = WorksheetFunction.VLookup(Cells(derlig + 1, 1), ActiveSheet.Range("A1:F10000"), 2, 0)
Cells(derlig + 1, 3).Value = WorksheetFunction.VLookup(Cells(derlig + 1, 1), ActiveSheet.Range("A1:F10000"), 3, 0) * -1
Cells(derlig + 1, 4).Value = WorksheetFunction.VLookup(Cells(derlig + 1, 1), ActiveSheet.Range("A1:F10000"), 4, 0)
Cells(derlig + 1, 5).Value = Cells(derlig + 1, 3).Value * Cells(derlig + 1, 4).Value / 1000
Cells(derlig + 1, 6).Value = Date
Application.EnableEvents = True
Range("H1").ClearContents
ActiveSheet.Protect
End If
End Sub