Private Sub Worksheet_SelectionChange(ByVal Target As Range) Résolu

Question

Bonjour à tous 
Je fais appel à votre aide pour simplifier et/ou modifier ce code qui fait énormément clignoter la feuille lors de son exécution.

Merci à vous

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = False

If EDITION_AGENT_PM.Visible = True Then
 
    If Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "A" Then
        
        Target.Value = Sheets("Donnees").Range("AF1").Value
        Target.Font.Color = Sheets("Donnees").Range("AF1").Font.Color
        
     ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "Nuit" Then
        
        Target.Value = Sheets("Donnees").Range("AF1").Value
        Target.Font.Color = Sheets("Donnees").Range("AF1").Font.Color
        
     ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "M" Then
        
        Target.Value = Sheets("Donnees").Range("AF1").Value
        Target.Font.Color = Sheets("Donnees").Range("AF1").Font.Color
        
      ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "J" Then
        
        Target.Value = Sheets("Donnees").Range("AF1").Value
        Target.Font.Color = Sheets("Donnees").Range("AF1").Font.Color

ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "Ca" Then
        
        Target.Value = Sheets("Donnees").Range("AF1").Value
        Target.Font.Color = -16776961
        
        ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "Fr" Then
        
        Target.Value = Sheets("Donnees").Range("AF1").Value
        Target.Font.Color = -11489280

ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "H" Then
        
        Target.Value = Sheets("Donnees").Range("AF1").Value
        Target.Font.Color = Sheets("Donnees").Range("AF1").Font.Color

ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "Abs" Then
        
        Target.Value = Sheets("Donnees").Range("AF1").Value
        Target.Font.Color = -16776961
       
         ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "Jss" Then
        
        Target.Value = Sheets("Donnees").Range("AF1").Value
        Target.Font.Color = -16776961

ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "Sup" Then
        
        Target.Value = ""
        Target.ClearComments
        Target.Font.Color = Sheets("Donnees").Range("AF1").Font.Color
        
        ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "" Then
        
         Sheets("Donnees").Range("AF1").Copy
        
       Target.Select
        Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
         Target.Font.Color = Sheets("Donnees").Range("AF1").Font.Color

ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "Sup2" Then
        
        Target.ClearComments
        Target.Font.Color = Sheets("Donnees").Range("AF1").Font.Color
     ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "Hsup" Then
        
      Target.Comment.Text Text:=Sheets("AGENT_PM").Range("A10").Value

Target.Font.Color = Sheets("Donnees").Range("AF1").Font.Color
        Target.Comment.Text Text:=Sheets("AGENT_PM").Range("A10").Value
        
Target.Font.Color = Sheets("Donnees").Range("AF1").Font.Color

ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "0" Then
        Target.Interior.Color = Sheets("Donnees").Range("Af1").Interior.Color
        
      ElseIf Not Intersect(Target, Range("$I$11:$EXV$40")) Is Nothing And Sheets("Donnees").Range("AF1").Value = "Supp3" Then
        Target.Interior.Color = Sheets("Donnees").Range("Af1").Interior.Color
        
     End If
        
    End If

Application.ScreenUpdating = True
End Sub

EDIT : Ajout du LANGAGE dans les balises de code (la coloration syntaxique).
Explications disponibles ici : ICI 

Merci d'y penser dans tes prochains messages.


Configuration: Windows 7 / Safari 5.1.7

Patrice33740 · Answer

Bonjour,

Tu devrais commencer par interrompre les évènements pendant ta macro

julia Namor · Answer

Merci Patrice33740

Est ce bien avec "application.enableevents = false" que tu me suggéres d'interrompre les événement?

ou devrais je les placer?

julia Namor · Answer

dont acte 
méa culpa

julia Namor · Answer

Merci Patrice33740 

Est ce bien avec "application.enableevents = false" que tu me suggéres d'interrompre les événement? 

ou devrais je les placer?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

4 réponses

Discussions similaires

Newsletters