bonsoir a tous
je débute en VBA et la je bloque en ligne surligner jaune
le code entre les 2 surlignage bleu
fonctionne bien
le code et sur ma feuil nouvelle ref
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'force la majuscule
If Not Intersect(Target, Range("B8,D8,F8,H8,J8,L8,N8,P8")) Is Nothing Then
If Range("B8,D8,F8,H8,J8,L8,N8,P8") <> "" Then Target = UCase(Target)
End If
Application.EnableEvents = True
Dim cel As Range 'non trouvé en feuille "liste"
If Target.Address = "$B$8" Then
If Target = "" Then Exit Sub
With Sheets("Liste")
Set cel = .Columns("A").Find(what:=Target, LookIn:=xlValues, lookat:=xlWhole)
If Not cel Is Nothing Then
cel.Offset(0, 1).ClearContents
Else
MsgBox Target & " non trouvé"
End If
End With
End If
Sheets("nouvelle ref").Select
ActiveSheet.Unprotect
Range("H1").Select
Selection.Locked = False
Selection.FormulaHidden = False
rep = MsgBox("Connais-tu le mot de passe?", vbYesNo + vbQuestion, "Accès")
If rep = vbYes Then
mdp = "118"
If InputBox("Saisie du mot de passe :", "Accès") = mdp Then
Application.ScreenUpdating = False
Range("B8").Select
Selection.copy
Sheets("LISTE").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("e1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.FormulaR1C1 = "=SUM(RC[-3])"
Range("e2").Select
Sheets("nouvelle ref").Select
Range("B8").Select
Sheets("nouvelle ref").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Else: End If
Application.ScreenUpdating = False
ActiveSheet.Protect Password:="", AllowFiltering:=True
Range("B8").Activate
Application.ScreenUpdating = False
End If
End Sub
je joins un fichier plus explicite
merci par avance
aiglon74