Sélection de coordonnées

Vous avez un grand écran, mais les tables avec lesquelles vous travaillez sont encore plus grandes. Et, en regardant par-dessus l'écran à la recherche des informations nécessaires, il y a toujours une chance de "glisser" les yeux vers la ligne suivante et de regarder dans la mauvaise direction. Je connais même des gens qui, pour de telles occasions, gardent toujours près d'eux une règle en bois pour l'attacher à la ligne du moniteur. Technologies du futur ! 

Et si la ligne et la colonne actuelles sont mises en surbrillance lorsque la cellule active se déplace sur la feuille ? Une sorte de sélection de coordonnées comme celle-ci :

Mieux qu'une règle, non ?

Il existe plusieurs manières, plus ou moins complexes, de mettre cela en œuvre. Chaque méthode a ses avantages et ses inconvénients. Voyons-les en détail.

Méthode 1. Évident. Macro qui met en surbrillance la ligne et la colonne actuelles

Le moyen le plus évident de résoudre notre problème "sur le front" - nous avons besoin d'une macro qui suivra le changement de sélection sur la feuille et sélectionnera la ligne et la colonne entières pour la cellule actuelle. Il est également souhaitable de pouvoir activer et désactiver cette fonction si nécessaire, afin qu'une telle sélection en forme de croix ne nous empêche pas d'entrer, par exemple, des formules, mais ne fonctionne que lorsque nous parcourons la liste à la recherche du nécessaire informations. Cela nous amène aux trois macros (sélectionner, activer et désactiver) qui devront être ajoutées au module de feuille.

Ouvrez une feuille avec une table dans laquelle vous souhaitez obtenir une telle sélection de coordonnées. Faites un clic droit sur l'onglet de la feuille et sélectionnez la commande dans le menu contextuel Texte source (Code source).La fenêtre de Visual Basic Editor devrait s'ouvrir. Copiez-y le texte de ces trois macros :

Dim Coord_Selection As Boolean 'Variable globale pour la sélection on/off Sub Selection_On() 'Macro sur la sélection Coord_Selection = True End Sub Selection_Off() 'Macro sur la sélection Coord_Selection = False End Sub 'Procédure principale qui effectue la sélection Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange As Range If Target.Cells.Count > 1 Then Exit Sub 'si plus d'une cellule est sélectionnée, quitter If Coord_Selection = False Then Exit Sub 'si la sélection est désactivée, quitter Application.ScreenUpdating = False Set WorkRange = Range (" A1:N6") 'adresse de la plage de travail dans laquelle la sélection est visible

Changez l'adresse de la plage de travail par la vôtre - c'est dans cette plage que notre sélection fonctionnera. Fermez ensuite Visual Basic Editor et revenez dans Excel.

Appuyez sur le raccourci clavier ALT + F8pour ouvrir une fenêtre avec une liste des macros disponibles. Macro Sélection_On, comme vous pouvez le deviner, inclut la sélection de coordonnées sur la feuille actuelle et la macro Sélection_Off – l'éteint. Dans la même fenêtre, en cliquant sur le bouton Paramètres (Options) Vous pouvez attribuer des raccourcis clavier à ces macros pour un lancement facile.

Avantages de cette méthode :

  • relative facilité de mise en oeuvre
  • sélection - l'opération est inoffensive et ne modifie en rien le contenu ou la mise en forme des cellules de la feuille, tout reste tel quel

Inconvénients de cette méthode:

  • une telle sélection ne fonctionne pas correctement s'il y a des cellules fusionnées sur la feuille - toutes les lignes et colonnes incluses dans l'union sont sélectionnées en même temps
  • si vous appuyez accidentellement sur la touche Suppr, non seulement la cellule active sera effacée, mais toute la zone sélectionnée, c'est-à-dire supprimer les données de toute la ligne et de la colonne

Méthode 2. Original. CELLULE + Fonction de mise en forme conditionnelle

Cette méthode, bien qu'elle présente quelques inconvénients, me semble très élégante. Pour implémenter quelque chose en utilisant uniquement les outils Excel intégrés, entrer au minimum dans la programmation en VBA est de la voltige 😉

La méthode est basée sur l'utilisation de la fonction CELLULE, qui peut donner de nombreuses informations différentes sur une cellule donnée - hauteur, largeur, numéro de ligne-colonne, format numérique, etc. Cette fonction a deux arguments :

  • un mot de code pour le paramètre, tel que "colonne" ou "ligne"
  • l'adresse de la cellule pour laquelle on veut déterminer la valeur de ce paramètre

L'astuce est que le deuxième argument est facultatif. S'il n'est pas spécifié, la cellule active actuelle est prise.

Le deuxième composant de cette méthode est la mise en forme conditionnelle. Cette fonctionnalité Excel extrêmement utile vous permet de formater automatiquement les cellules si elles répondent aux conditions spécifiées. Si nous combinons ces deux idées en une seule, nous obtenons l'algorithme suivant pour implémenter notre sélection de coordonnées via un formatage conditionnel :

  1. Nous sélectionnons notre tableau, c'est-à-dire les cellules dans lesquelles la sélection de coordonnées doit être affichée à l'avenir.
  2. Dans Excel 2003 et versions antérieures, ouvrez le menu Format – Mise en forme conditionnelle – Formule (Format — Formatage conditionnel — Formule). Dans Excel 2007 et plus récent - cliquez sur l'onglet Accueil (Accueil)bouton (dans la fenêtre de contrôle qui apparaît maintenant) Formatage conditionnel - Créer une règle (Mise en forme conditionnelle - Créer une règle) et choisissez le type de règle Utilisez une formule pour déterminer les cellules à formater (Utiliser la formule)
  3. Entrez la formule de notre sélection de coordonnées :

    =OU(CELLULE(“ligne”)=LIGNE(A2),CELLULE(“colonne”)=COLONNE(A2))

    =OU(CELLULE(«ligne»)=LIGNE(A1),CELLULE(«colonne»)=COLONNE(A1))

    Cette formule vérifie si le numéro de colonne de chaque cellule du tableau est le même que le numéro de colonne de la cellule actuelle. De même avec les colonnes. Ainsi, seules les cellules qui ont un numéro de colonne ou un numéro de ligne correspondant à la cellule actuelle seront remplies. Et c'est la sélection de coordonnées en forme de croix que nous voulons réaliser.

  4. Cliquez sur le bouton Framework (format) et définissez la couleur de remplissage.

Tout est presque prêt, mais il y a une nuance. Le fait est qu'Excel ne considère pas une modification de la sélection comme une modification des données de la feuille. Et, par conséquent, il ne déclenche pas le recalcul des formules et la recoloration de la mise en forme conditionnelle uniquement lorsque la position de la cellule active change. Par conséquent, ajoutons une simple macro au module de feuille qui fera cela. Faites un clic droit sur l'onglet de la feuille et sélectionnez la commande dans le menu contextuel Texte source (Code source).La fenêtre de Visual Basic Editor devrait s'ouvrir. Copiez-y le texte de cette simple macro :

Private Sub Worksheet_SelectionChange (ByVal Target As Range) ActiveCell.Calculate End Sub

Maintenant, lorsque la sélection change, le processus de recalcul de la formule avec la fonction sera lancé CELLULE dans la mise en forme conditionnelle et inonder la ligne et la colonne actuelles.

Avantages de cette méthode :

  • La mise en forme conditionnelle ne rompt pas la mise en forme de tableau personnalisée
  • Cette option de sélection fonctionne correctement avec les cellules fusionnées.
  • Aucun risque de supprimer une ligne et une colonne entières de données en cas de clic accidentel Supprimer.
  • Les macros sont peu utilisées

Inconvénients de cette méthode:

  • La formule de mise en forme conditionnelle doit être saisie manuellement.
  • Il n'existe aucun moyen rapide d'activer/désactiver un tel formatage - il est toujours activé jusqu'à ce que la règle soit supprimée.

Méthode 3. Optimal. Formatage conditionnel + Macros

Juste milieu. Nous utilisons le mécanisme de suivi de la sélection sur la feuille à l'aide des macros de la méthode 1 et y ajoutons une mise en surbrillance sécurisée à l'aide de la mise en forme conditionnelle de la méthode 2.

Ouvrez une feuille avec une table dans laquelle vous souhaitez obtenir une telle sélection de coordonnées. Faites un clic droit sur l'onglet de la feuille et sélectionnez la commande dans le menu contextuel Texte source (Code source).La fenêtre de Visual Basic Editor devrait s'ouvrir. Copiez-y le texte de ces trois macros :

Dim Coord_Selection As Boolean Sub Selection_On() Coord_Selection = True End Sub Sub Selection_Off() Coord_Selection = False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange As Range, CrossRange As Range Set WorkRange = Range("A7:N300") Si Target.Count > 1 Then Exit Sub If Coord_Selection = False Then WorkRange.FormatConditions.Delete Exit Sub End If Application.ScreenUpdating = False If Not Intersect(Target, WorkRange) Is Nothing Then Set CrossRange = Intersect( WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type :=xlExpression, Formula1 :="=1" CrossRange.FormatConditions(1).Interior.ColorIndex = 33 Target.FormatConditions .Supprimer Fin Si Fin Sous

N'oubliez pas de remplacer l'adresse de la plage de travail par l'adresse de votre table. Fermez Visual Basic Editor et revenez à Excel. Pour utiliser les macros ajoutées, appuyez sur le raccourci clavier ALT + F8  et procédez comme pour la méthode 1. 

Méthode 4. Belle. Module complémentaire FollowCellPointer

Excel MVP Jan Karel Pieterse des Pays-Bas offre un module complémentaire gratuit sur son site Web SuivreCellPointer(36 Ko), qui résout le même problème en dessinant des flèches graphiques à l'aide de macros pour mettre en surbrillance la ligne et la colonne actuelles :

 

Belle solution. Non sans pépins par endroits, mais ça vaut vraiment le coup d'essayer. Téléchargez l'archive, décompressez-la sur le disque et installez le module complémentaire :

  • dans Excel 2003 et versions antérieures – via le menu Service – Modules complémentaires – Aperçu (Outils — Compléments — Parcourir)
  • dans Excel 2007 et versions ultérieures, via Fichier – Options – Modules complémentaires – Aller – Parcourir (Fichier — Options Excel — Compléments — Aller à — Parcourir)

  • Que sont les macros, où insérer le code de macro dans Visual Basic

 

Soyez sympa! Laissez un commentaire