Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, [C1]) Is Nothing Then Application.EnableEvents = False Target = Target - [A1] Application.EnableEvents = True End If End Sub
Public flag As Boolean Private Sub Worksheet_Change(ByVal Target As Range) If flag Then Exit Sub If Target.Address = "$C$1" Then flag = True Target = Target - [A1] flag = False End If End Sub
Dim X As Integer Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$1" Then Application.EnableEvents = False Range("C1").Value = Range("C1").Value - Range("A1").Value Application.EnableEvents = True End If If Target.Address = "$A$1" Then Application.EnableEvents = False Range("C1").Value = Range("C1").Value - Range("A1").Value + X Application.EnableEvents = True End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" Then X = Target.Value Else X = 0 End If End Sub
Private anc As Range '====>> absolument indispensable dans la partie générale du code Private Sub Worksheet_Change(ByVal Target As Range) Set anc = Target End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not anc Is Nothing Then If anc = Range("C1") Then Range("C1").Value = Range("C1").Value - Range("A1").Value End If End If End Sub
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionensuite, il faudra faire attention aux boucles infinies.
Private Sub Worksheet_Change(ByVal Target As Range) Static lsLock as Boolean If Target.Address="C1" And Not lsLock Then lsLock =True Target.Value=Target.Value- Range("A1").Value lsLock =False End If End Sub
Application.EnableEvents
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "C1" Then Application.EnableEvents = False Range("C1").Value = Range("C1").Value - Range("A1").Value Application.EnableEvents = True End If End Sub
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, [C1]) Is Nothing Then Application.EnableEvents = False Target = Target - [A1] Application.EnableEvents = False End If End Sub
Private Sub Workbook_Open() On Error Resume Next X = Worksheets("Feuil5").[Erreur].Value On Error GoTo 0 End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim Cell As Range Select Case Sh.Name Case "Feuil5" If Target.Address = [Erreur].Address Then DernLig = Worksheets("Feuil7").Range("C65536").End(xlUp).Row Application.EnableEvents = False For Each Cell In Worksheets("Feuil7").Range("C1:C" & DernLig) Cell.Value = Cell.Value - Worksheets("Feuil5").[Erreur].Value + X Next Application.EnableEvents = True End If Case "Feuil7" If Not Intersect(Target, [Resultats]) Is Nothing Then Application.EnableEvents = False Target.Value = Target.Value - Worksheets("Feuil5").[Erreur].Value Application.EnableEvents = True End If End Select End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name "Feuil5" And Target.Address Worksheets("Feuil5").[Erreur].Address Then X = [Erreur].Value End If End Sub
Public X As Integer