Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Select Case Target.Value
Case ""
Case Else
For i = 1 To ThisWorkbook.Sheets.Count
Select Case Application.WorksheetFunction.CountIf(Sheets(i).[C:C], Target)
Case 0
Case 1
If Sheets(i).Name <> ActiveSheet.Name Then
GoTo a_modifier
End If
Case Else
GoTo a_modifier
End Select
Next
End Select
Exit Sub
a_modifier:
myPrompt = " !!! Cette donnée existe déjà dans la feuille " & Sheets(i).Name & " Ligne " & Sheets(i).Cells.Find(What:=Target.Value, After:=Sheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Row & " colonne " & Sheets(i).Cells.Find(What:=Target.Value, After:=Sheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Column
myDefault = Target
myInput = InputBox(Prompt:=myPrompt, Default:=myDefault, Title:="Attention")
Target = myInput
End If
If Target.Address = "$H$7" Then
Rows(Target.Row).Insert
Application.EnableEvents = False
Sh.Unprotect
Sh.Range("B7").Value = "--"
Sh.Range("J7").FormulaLocal = "=F7*Tarif"
Sh.Range("I7").FormulaLocal = "=SI(J7< 55;55;F7*Tarif)"
Sh.Range("G7").FormulaLocal = "=SI(J7=0;0;I7)"
ActiveSheet.Protect , AllowInsertingRows:=True
Application.EnableEvents = True
Range("$a$7").Select
End If
End Sub
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect userinterfaceonly:=True
Next ws
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Select Case Target.Value
Case ""
Case Else
For i = 1 To ThisWorkbook.Sheets.Count
Select Case Application.WorksheetFunction.CountIf(Sheets(i).[C:C], Target)
Case 0
Case 1
If Sheets(i).Name <> ActiveSheet.Name Then
GoTo a_modifier
End If
Case Else
GoTo a_modifier
End Select
Next
End Select
Exit Sub
a_modifier:
myPrompt = " !!! Cette donnée existe déjà dans la feuille " & Sheets(i).Name & " Ligne " & Sheets(i).Cells.Find(What:=Target.Value, After:=Sheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Row & " colonne " & Sheets(i).Cells.Find(What:=Target.Value, After:=Sheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Column
myDefault = Target
myInput = InputBox(Prompt:=myPrompt, Default:=myDefault, Title:="Attention")
Target = myInput
End If
If Target.Address = "$H$7" Then
Rows(Target.Row).Insert
Sh.Range("B7").Value = "--"
Sh.Range("J7").FormulaLocal = "=F7*Tarif"
Sh.Range("I7").FormulaLocal = "=SI(J7< 55;55;F7*Tarif)"
Sh.Range("G7").FormulaLocal = "=SI(J7=0;0;I7)"
Range("$a$7").Select
End If
Application.EnableEvents = True
End Sub
quelle ligne et après quelle action ?C'est mieux, mais maintenant ça coince sur une autre ligne de la Vba.
Application.EnableEvents = True
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect userinterfaceonly:=True
Next ws
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Select Case Target.Value
Case ""
Case Else
For i = 1 To ThisWorkbook.Sheets.Count
Select Case Application.WorksheetFunction.CountIf(Sheets(i).[C:C], Target)
Case 0
Case 1
If Sheets(i).Name <> ActiveSheet.Name Then
GoTo a_modifier
End If
Case Else
GoTo a_modifier
End Select
Next
End Select
GoTo fin
a_modifier:
myPrompt = " !!! Cette donnée existe déjà dans la feuille " & Sheets(i).Name & " Ligne " & Sheets(i).Cells.Find(What:=Target.Value, After:=Sheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Row & " colonne " & Sheets(i).Cells.Find(What:=Target.Value, After:=Sheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Column
myDefault = Target
myInput = InputBox(Prompt:=myPrompt, Default:=myDefault, Title:="Attention")
Target = myInput
End If
If Target.Address = "$H$7" Then
Rows(Target.Row).Insert
Sh.Range("B7").Value = "--"
Sh.Range("J7").FormulaLocal = "=F7*Tarif"
Sh.Range("I7").FormulaLocal = "=SI(J7< 55;55;F7*Tarif)"
Sh.Range("G7").FormulaLocal = "=SI(J7=0;0;I7)"
Range("$a$7").Select
End If
fin:
Application.EnableEvents = True
End Sub