Worksheet_change et clearcontents

[meldja]

Bonjour,
Après recherche sur le moteur de recherche de ce site et sur google, je n'ai toujours pas de solutions.
J'ai une procédure événementielle sur le changement d'une cellule (worksheet_change).
La cellule sensé la déclencher est la cellule A1, qui contient une liste déroulante servant de critère.
Je l'ai déclaré de cette façon :

If Target.address = "$A$1" Then

Cette cellule est dans la feuille active.
La suite doit recopier des données qui se trouvent sur une autre feuille dans la feuille active.
Jusqu'à présent, il n'y a aucun problème.
Sauf que je voudrais, qu'avant de recopier les données dans la feuille active, excel efface les données qui ont été précédemment recopier dans la feuille active.
Je lui demande donc d'effacer ces données de cette façon :

Range("A4:D500").ClearContents

Et cette ligne de code fait tout planter. D'après ce que j'ai pu trouver sur le Net, cela exécute worksheet_change et fait tout planter. Je n'ai malheureusement pas trouvé de solution.
J'ai fait plein de test sans rien solutionner.
Voila le code qui fonctionne :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim x As Integer
x = Sheets("Base").Range("A500").End(xlUp).Row
For i = 2 To x
With Sheets("Base")
If Target.Address = "$A$1" And .Range("G" & i) = Range("A1") Then
Range("A500").End(xlUp).Offset(1, 0) = .Range("A" & i)
Range("A500").End(xlUp).Offset(0, 1) = .Range("B" & i)
Range("A500").End(xlUp).Offset(0, 2) = .Range("C" & i)
Range("A500").End(xlUp).Offset(0, 3) = .Range("E" & i)
Sheets("Feuil1").Select
End If
End With
Next i
End Sub

Si je rajoute :

Range("A4:D500").ClearContents

ça fait planter tout. D'après ce que j'ai pu comprendre sur le Net, c'est incompatible avec Worksheet_Change, mais je n'ai pas trouvé de solution.
Si quelqu'un a une réponse, merci d'avance.

 

[GeoTrouvePas]

Re : Worksheet_change et clearcontents

Bonsoir,

Essaye ceci :

Application.EnableEvents = False
Range("A4:D500").ClearContents
Application.EnableEvents = True

Ça devrait empêcher ton worksheet_change de s'exécuter.

 

[Gorfael]

Re : Worksheet_change et clearcontents

Salut meldja et le forum

Petit rappel : on n'est ni devin, ni télépathe et on n'utilise pas ton fichier.

Ce que donne GeoTrouvePas peut être une solution. Mais ça ne permet pas de trouver le problème

Tu dis que ta macro fonctionne => donc elle est sur le module qui est lié à "Feuil1", sinon elle ne fonctionnerai pas. Ton instruction ne présente, à priori, aucune anomalie susceptible d'entraîner un dysfonctionnement => Mais que signifie "fait tout planter" ? Qu'excel s'arrête et se ferme ? Qu'il y a un message d'erreur et dans ce cas, lequel ? Si tu fais l'effacement manuellement, as-tu le même problème ?
A+

 

[meldja]

Re : Worksheet_change et clearcontents

Bonjour et merci de vos réponses,
La cellule qui doit déclencher la procédure se trouve dans la feuille active (celle qui est affichée).
Dans la feuille affichée, je choisis une donnée dans la cellule A1 (cellule qui déclenche la procédure.
Le code ne se trouve pas dans un module mais directement dans la feuille.
Le plantage dont je parle, c'est la fermeture d'excel. Au début, je pensais que c'était Excel qui plantait, mais je l'ai testé sur une autre partition (j'ai un dualboot : XP et Seven) toujours avec office 2007, et c'est la même chose.
La solution de GeoTrouvePas empêche Excel de planter mais les données ne se recopie pas.
Je pense que le plus simple est de déposer le fichier ici.
Dans la feuille "Feuil1", j'ai rajouté un bouton pour effacer les données recopiées.
J'ai mis des quotes sur les lignes de GeoTroupePas.
Vous pourrez constater que la procédure fonctionne sauf si j'introduis le clearcontents.
Merci

 

[Gorfael]

Re : Worksheet_change et clearcontents

Salut meldja et le forum
C'est tellement simple, et je n'ai même pas trouvé, désolé

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Worksheet_Change
Dim i As Integer, x As Integer

Application.EnableEvents = False
Range("A4:D500").ClearContents
x = Sheets("Base").Range("A500").End(xlUp).Row
For i = 2 To x
    With Sheets("Base")
        If Target.Address = "$A$1" And .Range("G" & i) = Range("A1") Then
            Range("A500").End(xlUp).Offset(1, 0) = .Range("A" & i)
            Range("B500").End(xlUp).Offset(1, 0) = .Range("B" & i)
            Range("C500").End(xlUp).Offset(1, 0) = .Range("C" & i)
            Range("D500").End(xlUp).Offset(1, 0) = .Range("E" & i)
        End If
    End With
Next i

Sortie_Worksheet_Change:
    Application.EnableEvents = True
    Exit Sub
Err_Worksheet_Change:
    MsgBox Err.Description, vbCritical + vbOKOnly, "Erreur Excel n°" & Err.Number
    Resume Sortie_Worksheet_Change
End Sub

Quand je fais une instruction qui dépasse le cadre de la macro, comme EnaleEvents, je gère systématiquement les erreurs, pour évité que celles-ci ne laissent l'instruction à False.

Ton problème : tu utilises un macro à lancement automatique qui se relance à chaque fois que tu écris.
À la première ligne, ta macro se re-déclenche et l'efface. J'ai donc corrigé ta macro à partir de ton code, pour que tu puisses faire fonctionner ton code en pas-à-pas, en mettant un point d'arrêt sur la première ligne de la partie VRAI du test, puis en faisant de même, une fois que tu auras compris ton erreur de fonctionnement et tu tu auras remplacé ton code par celui que j'ai mis.

Une autre manière de traiter le problème :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long, X As Long, y As Long

If Target.Address <> "$A$1" Then Exit Sub
Range("A4:D500").ClearContents
X = Sheets("Base").Range("A500").End(xlUp).Row
For I = 2 To X
    With Sheets("Base")
        If .Range("G" & I) = Range("A1") Then
           y = Range("A500").End(xlUp).Offset(1, 0).Row
           Cells(y, "A") = .Range("A" & I)
           Cells(y, "B") = .Range("B" & I)
           Cells(y, "C") = .Range("C" & I)
           Cells(y, "D") = .Range("E" & I)
        End If
    End With
Next I
End Sub

Les différences essemtielles :
- Je contrôle que c'est bien A1 qui est concerné, avant d'exécuter le code
- Tu utilises une "base de donnée" où "une ligne = un enregistrement". Bien qu'il n'y ait pas de "trou", je préfère m'assurer qu'une fois que j'ai déterminé une ligne, que ce soit pour la source ou la destination, je n'en change pas. Essaie en supprimant un nom ou un prénom dans ta base et regarde ce que te renvoie ta macro. Et compare avec la mienne. C'est plus une histoire de "philosophie" de travail, mais je ne laisse rien au hasard (enfin j'essaie )
A+

 

[meldja]

Re : Worksheet_change et clearcontents

Merci,
Je n'ai pas encore testé tes conseils, mais je te remercie d'avoir consacré ton temps sur mon cas.
Ta philosophie qui consiste à enseigner la pêche plutôt que de donner du poisson tient la route et je l'apprécie.
Merci pour ton aide et bonne fin de journée.