Discussion :

[m@tix]

Bonsoir,

J'utilise un évènement Worksheet_Change sur une feuille de mon classeur.
Même si c'est contradictoire, je souhaiterais pouvoir affecter le code associé à cet évènement à un bouton.
Je ne pense pas que ce soit faisable, étant donné qu'il faudrait "mémoriser" l'ensemble des évènements "Worksheet_Change" tant que le bouton n'aura pas été actionné..

Ma question est simple : existe-t-il une astuce pour réussir à faire cela ?

Merci d'avance.

[BlueMonkey]

Bonsoir,

Je souhaiterais pouvoir affecter le code associé à cet évènement [Worksheet_Change] à un bouton.

Si le but est d'appeler le même code lorsqu'on clic sur un bouton que lorsqu'on change d'onglet, le plus simple est de :
- copier le code de Worksheet_Change, dans une fonction personnalisée.
- appeler cette fonction personnalisée dans :
* le gestionnaire d'évènement du bouton CommandButton1_Click().
* le gestionnaire Worksheet_ChangeEn théorie, il est même possible d'appeler directement Worksheet_Change() dans le même onglet.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub CommandButton1_Click()
     Worksheet_Change ([A1])
End Sub

Mais pour une meilleure compréhension, l'utilisation d'une fonction intermédiaire est préférable.

[m@tix]

Bonsoir,

Le but n'est pas d'effectuer une action lorsqu'on change de feuille, mais lorsqu'une modification est effectuée sur une feuille.

Du coup, ta réponse reste-t-elle valable ?

Pour donner une idée plus précise de ce que je cherche à obtenir, voici un exemple : je souhaite qu'à chaque modification d'une cellule par l'utilisateur, une action s'effectue dans un autre classeur (écriture d'un texte dans une cellule dépendant directement de la cellule modifiée dans le premier classeur). Seulement, et là est la difficulté que je rencontre, je voudrais que ces actions s'effectuent uniquement une fois l'ensemble des modifications de cellules effectué, sous l'action de l'utilisateur via un bouton.

[BlueMonkey]

Bonjour,

Le but n'est pas d'effectuer une action lorsqu'on change de feuille, mais lorsqu'une modification est effectuée sur une feuille.

Le gestionnaire Worksheet_Change est bien appelé lorsqu'une cellule du classeur change de valeur.
voir FAQ sur les évènements de feuille de calcul

Je voudrais que ces actions s'effectuent uniquement une fois l'ensemble des modifications de cellules effectué, sous l'action de l'utilisateur via un bouton.

Pour cela suffit d'exécuter le code lorsque l'utilisateur clique sur un bouton <mise à jour>.

Je ne vois pas bien à quoi servirait un code réagissant à chaque changement de cellule, s'il faut attendre que toutes les cellules soient modifiées
pour exécuter l'action.
Est-ce qu'il faut garder une trace des cellules qui ont été modifiée, pour les distinguer de celle qui ne l'ont pas été, de façon exécuter une action spécifiques sur les cellules modifiée ?
Dans ce cas une solution est peut être de lister les modifications dans une nouvelle feuille du classeur à partir de Worksheet_Change:
- Col A : n° ligne de la cellule modifiée ; ColB : n° colonne de la cellule modifiée ; Col C : Nouvelle Valeur.
- sur le clic du bouton, on parcours ces modifications pour les envoyer vers le classeur cible.
- on efface les actions de modifications qui ont été envoyé au classeurs cible, pour le pas les refaire au prochain clic de bouton.

[m@tix]

Bonjour,

Merci pour ta réponse. Avant d'aller plus loin, autant que j'explique clairement ce que je souhaite réaliser afin de comprendre mes intentions et voir si je peux utiliser ce que tu m'as proposé.

J’ai un fichier constituant une base, et un fichier plus léger contenant une partie des infos présentes dans la base. Mon but est le suivant : lorsque l’utilisateur change certains paramètres du fichier léger, je souhaite que ces changements se répercutent également dans la base, une fois toutes les modifications effectuées, au moyen d’un bouton. Mon idée première était de faire des boucles For pour passer en revue l’ensemble des lignes des fichiers afin de repérer sur quelles lignes les changements ont eu lieu, puis effectuer les changements. La méthode fonctionne, mais étant donné que la base comporte plusieurs milliers de lignes, et que la plupart du temps, seules quelques modifications seront effectuées (2 à 3 à chaque fois), le temps d’exécution du code est relativement long. J’ai donc voulu optimiser le code, en ne cherchant pas à parcourir à chaque fois toutes les lignes, mais en allant directement à l’essentiel, là où les modifications ont eu lieu.

Mes intentions sont-elles plus claires ?

[BlueMonkey]

Bonjour,

La méthode présentée précédemment peu fonctionner.

- un onglet contient la vu partielle de la base de donnée.
- un onglet contient la liste des modifications faite par l'utilisateur depuis la dernière mise à jour de la base.
- un onglet (normalement dans un autre classeur contient la base de donnée complète).

Pour copier l'ensemble des changements de valeur faites par l'utilisateur, ajouter ce code dans l'onglet contenant la vue partielle de la base,
en changeant la couleur de fond des lignes modifiées :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target Is Nothing Then Exit Sub
 
    Dim r As Range
    Dim nextline As Integer
    nextline = Worksheets("log des changements").[A65000].End(xlUp).Row + 1
 
    Application.ScreenUpdating = False
    For Each r In Target
        With Worksheets("log des changements")
            .Cells(nextline, 1).Value = r.Row
            .Cells(nextline, 2).Value = r.Column
            .Cells(nextline, 3).Value = r.Value
        End With
 
        r.Interior.ColorIndex = 35
        nextline = nextline + 1
    Next r
    Application.ScreenUpdating = True
End Sub

Lorsque l'utilisateur souhaite envoyer ses modifications vers la base complète, il clique sur un bouton.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub CommandButton1_Click()
    Worksheets("log des changements").SendLog2Base
End Sub

La fonction SendLog2Base qui envoie les données vers l'onglet "base complète" (représentant ici la base complète),
se trouve dans l'onglet contenant la liste des modifications utilisateurs. (onglet "log des changements").
La couleur de fond des cellules mises à jour reprend l'état pas de couleur.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Option Explicit
 
Sub SendLog2Base()    
    Dim wsDB As Worksheet
    Set wsDB = Worksheets("base complète")
    Dim wsSmallDB As Worksheet
    Set wsSmallDB = Worksheets("base partielle")
 
    Application.ScreenUpdating = False
    Dim r As Long
    r = 2
    With Worksheets("log des changements")
        Do While (.Cells(r, 1).Value <> "")
            wsDB.Cells(.Cells(r, 1).Value, .Cells(r, 1).Offset(0, 1).Value).Value = .Cells(r, 1).Offset(0, 2).Value
            wsSmallDB.Cells(.Cells(r, 1).Value, .Cells(r, 1).Offset(0, 1).Value).Interior.ColorIndex = 0
            r = r + 1
        Loop
    End With
 
    Worksheets("log des changements").[A2].CurrentRegion.Offset(1).ClearContents
 
    Application.ScreenUpdating = True
End Sub

Exemple ci-joint.

[Didier Gonard]

Bonsoir,

@m@tix De ce que je comprend, tu pourrais utiliser les worksheets change pour alimenter une variable tableau qui mémoriserait les repères nécessaires aux cellules modifiées et à leurs nouvelles valeurs.
Lors de l'appui sur le bouton voulu, l'écriture dans la base se fera avec le contenu de la variable tableau.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 nextline = Worksheets("log des changements").[A65000].End(xlUp).Row + 1

Depuis la sortie de 2007 avec 1 046 576 lignes, il est tout à fait déconseillé de coder des bornes "en dur". Le danger est très important, vu qu'on aura pas de soucis tant qu'on est en dessous et qu'ensuite, on aura pas de message d'erreur forcément, mais des données non prises en compte assurément

Cordialement,

Ps : si le côté technique de la réponse = OK ou pas => pensez à cliquer sur les pouces et quand question résolue à la taguer résolue, et chaque action vous rapporte des points

Didier

[BlueMonkey]

Bonjour,

Depuis la sortie de 2007 avec 1 046 576 lignes, il est tout à fait déconseillé de coder des bornes "en dur"

J'étais parti dans l'hypothèse qu'il y avait un faible nombre de mise à jour à tracer au vu de la méthode utilisée.
(65000 lignes était de déjà une approximation même pour XLS2003).

Maintenant pour ne pas inclure de limite sur le nombre de ligne, on pourra utiliser le code suivant.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target Is Nothing Then Exit Sub
 
    Dim r As Range
    Dim nextline As Long
    With Worksheets("log des changements")
        nextline = IIf(.Cells(2, 1).Value <> "", .Cells(1, 1).End(xlDown).Row + 1, 2)
    End With
 
    Application.ScreenUpdating = False
    For Each r In Target
        With Worksheets("log des changements")
            .Cells(nextline, 1).Value = r.Row
            .Cells(nextline, 2).Value = r.Column
            .Cells(nextline, 3).Value = r.Value
        End With
 
        r.Interior.ColorIndex = 35
        nextline = nextline + 1
    Next r
    Application.ScreenUpdating = True
End Sub

P.S. :
La FAQ mériterait une mise à jour également : Comment retrouver les numéros des dernières lignes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Dim DerniereLigne As Integer    
DerniereLigne = Range("A65536").End(xlUp).Row

[Pierre Fauconnier]

Salut.

Pour atteindre la dernière cellule utilisée en A de la feuille active, on peut utiliser

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("a" & Rows.Count).End(xlUp)

Si on veut atteindre la dernière ligne utilisée en A d'une autre feuille, il suffit de préfixer l'objet Range et la collection Rows.

C'est valable pour toutes les versions.

[m@tix]

Bonsoir à vous tous,

Désolé pour ce retour tardif, je n'ai pas été disponible ce weekend.
Je viens de tester ton code BlueMonkey, et j'ai surtout essayé de le comprendre. Ca me semble très bien pour ce que je souhaite faire, mais tout n'est pas clair. Notamment, tu parles de couleurs à plusieurs reprises, je ne saisis pas bien l'intérêt, je dois passer à côté de quelque chose.

Ensuite, j'ai essayé d'appliquer ce code (via ton fichier) en mettant quelques lignes de données sur les feuilles souhaitées. Et.. chez moi ça ne fonctionne pas. J'ai fait un pas à pas, les changements effectués apparaissent bien sur la feuille "log des changements", mais en appuyant sur le bouton (qui déclenche SendLog2Base), la mise à jour ne s'effectue pas sur la feuille "base complète".. Je n'ai pourtant pas modifié ton code !

Edit: à présent cela semble fonctionner ! Sûrement une mauvaise manip de ma part...

[BlueMonkey]

Bonsoir,

Content que ça convienne .
Pour le code, il est préférable de partir de la dernière version du code (à complété depuis le post #8 et la proposition de Pierre Fauconnier
pour simplifier le code)

Sinon pour ce qui est de l'intérêt de la couleur, j'espère qu'a l'utilisation de l'exemple c'est devenu plus clair.
Bien entendu, l'utilisation de la couleur est optionnel .

[m@tix]

J'ai passé la discussion en "Résolu", mais j'ai deux questions supplémentaires si vous le permettez..

1) Il se trouve que ma "base complète" (pour reprendre les notations du fichier) ne se trouve pas sur le même fichier, mais dans un fichier séparé. En conséquence, j'ai modifié certaines lignes de code ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Sub SendLog2Base()
 
    Dim wsDB As Worksheet
    Dim wbkBComplete as String
    wbkBComplete = ThisWorkbook.Path & "\baseComplete.xlsx"
    Set wsDB = Workbooks(wbkBComplete).Sheets("base complete")
    Dim wsSmallDB As Worksheet
    Set wsSmallDB = Worksheets("base partielle")

Seulement, en faisant une exécution pas à pas, une erreur est déclenchée au niveau de la ligne (erreur d'exécution 9, l'indice n'appartient pas à la sélection):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set wsDB = Workbooks(wbkBComplete).Sheets("base complete")

Pourtant, j'ai bien vérifié l'emplacement du fichier baseComplete.xlsx, et l'existence de la feuille.. D'où vient le problème ?

[BlueMonkey]

Re.

(1) Pour l'utilisation du WorkBook, il faut l'ouvrir avant de pouvoir l'utiliser comme souhaité.
exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Dim wb As Workbook
Set wb = Workbooks.Open(Filename:=ThisWorkbook.Path & "\baseComplete.xlsx")

et vérifier que l'onglet "base complete" existe bien dans ce classeur.

[m@tix]

Avant de passer au deuxième point, finissons-en avec le premier je pense.
Je viens d'effectuer la modification que tu viens de me suggérer, et j'ai fait suivre la ligne suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set wsDB = Workbooks(wb).Sheets("base complete")

Là encore, j'obtiens une erreur (et j'ai bien vérifié que la feuille base complete existe) : erreur d'exécution 13, incompatibilité de type.



Edit: pour l'histoire de la couleur, c'est parfaitement clair à présent !

[BlueMonkey]

wb ne peut pas être utilisé comme un indice de classeur.
C'est le classeur.

Pour avoir une référence sur l'onglet, essaye :
Set wsDB = Workbooks.Open(Filename:=ThisWorkbook.Path & "\baseComplete.xlsx").Sheets("base complete")

[m@tix]

Ah ok ! Autant pour moi.. Merci, ça m'a l'air bon cette fois-ci.

Venons-en donc à ma seconde question.
Si à partir de la feuille base partielle, l'utilisateur, en plus de faire des modifications sur les lignes existantes, rajoute un élément, dans le sens où sur la première ligne vide, il va rajouter une donnée de la même forme que les précédentes, comment pourrait-on modifier le code pour que cette (ou ces) ligne(s) soi(en)t elle(s) aussi prises en compte lors de la mise à jour ?

[BlueMonkey]

Du moment que l'utilisateur n'insère pas de ligne ou ne supprime pas de ligne dans la feuille, l'ensemble des modifications sont tracées dans l'onglet de log.
Donc l'ensemble de ces modifications sont envoyés sur la base complète.

[m@tix]

Dans ce cas, c'est parfait, étant donné que j'ai annulé la possibilité de supprimer ou d'ajouter des lignes/colonnes sur la feuille.

Merci encore pour ton aide, précieuse, BlueMonkey !
Je testerai tout cela sur mes fichiers en début de semaine pour vérifier si tout est ok. En cas de soucis, je reviendrai sur le fil...

Bonne nuit !

[BlueMonkey]

Ok.
Bonne nuit également.

[m@tix]

Bonjour,

Me revoilà !

Je viens de tester le code sur mes fichiers, et en fait, je constate un problème de taille.
Je m’explique. Pour rappel, d’un côté, j’ai un fichier contenant une feuille « base complete » répertoriant sur chaque ligne un ensemble d’objets ayant plusieurs caractéristiques (sur les colonnes). D’un autre côté, j’ai un autre fichier comportant une feuille « base partielle » qui contient une partie des objets présents dans la « base complete ».

Le problème constaté est le suivant : lorsque je change l’une des caractéristiques de l’objet présent sur la ligne i dans la base partielle, et que clique sur le bouton, la macro va modifier dans la base complète la caractéristique de l’objet présent sur la ligne.. i. Normal au vu du code. Mais l’ennui, c’est que l’objet présent sur la ligne i de la base partielle ne va pas forcément correspondre à l’objet présent sur la ligne i de la base complète étant donné que celle-ci comporte un nombre bien plus important d’objets, et que les utilisateurs sont amenés à manipuler les colonnes via des filtres (ce qui change forcément les numéros de lignes attribués à chaque objet). En définitive, seules les colonnes ne posent pas de problème.

J’espère avoir été clair.. Au besoin je fournirai des fichiers représentatifs.
Est-il possible de palier le problème ?

Merci d’avance.