[VIDEO]Comment ajuster automatique la taille cellule en fonction de son contenu
Aujourd’hui, nous allons voir comment procéder pour ajuster automatiquement la taille des cellules en fonction du contenu saisi dans celles-ci. Pour commencer, nous allons voir comment procéder de manière manuelle, puis dans un second temps nous verrons comment faire pour que la taille des cellules s'ajuste automatiquement lorsque le contenu de celle-ci va être modifié.
Partie 1 :
Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier dans le lien disponible en fin d’article (cliquez ici pour y accéder).
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
1. Présentation
Pour illustrer cet exemple nous allons utiliser le fichier que vous avez sous les yeux, constitué d'une base de données qui se trouve dans l’onglet « Base ». Dans celle-ci, nous allons retrouver des articles avec leur prix respectifs, ainsi que leurs caractéristiques.
Dans un premier temps, le but va être de récupérer le prix et les caractéristiques d’un article dont nous allons insérer la référence dans la première cellule ($B$7).
Pour éviter d'avoir à ressaisir les codes de nos articles nous allons utiliser la « Validation de données ». Pour cela nous allons dans l'onglet « Données », puis cliquer sur « Validation de données ».
Nous allons faire le choix d’autoriser une « Liste » :
Puis, nous allons simplement venir choisir la première colonne de la base de données : « Modèle ». Notre base de données était présentée sous la forme d’un tableau de données, il nous suffit simplement de venir cliquer sur l'en-tête de la colonne, (lorsque la flèche pointe vers le bas) pour sélectionner automatiquement toute la colonne.
Par la suite, lorsque nous ajouterons de nouvelles lignes dans notre base de données, Excel adaptera automatiquement la liste pour y intégrer ces nouvelles informations.
Pour en savoir plus sur l’utilisation des tableaux de données, je vous invite à consulter cet article.
Nous pouvons à présent valider la sélection en appuyant sur la touche [Entrée] du clavier.
De cette manière, nous avons bien à disposition un menu déroulant qui va nous permettre de venir saisir la référence de l’article qui nous intéresse.
À présent, pour obtenir les informations sur l’article choisi, nous allons utiliser les formules imbriquées INDEX() et EQUIV().
Nous allons simplement saisir les deux formules suivantes dans les cellules B8 et B9 :
Formule à insérer dans la cellule B8 :
INDEX(_baseArticles[Prix];EQUIV(B7;_baseArticles[Modèle];))
Formule à insérer dans la cellule B9 :
=INDEX(_baseArticles[Caractéristiques];EQUIV(B7;_baseArticles[Modèle];))
Nous passons rapidement sur l’utilisation de ces formules, mais si vous souhaitez en savoir davantage, vous trouverez toutes les informations dans cet article.
Enfin, lorsque nous consultons la base de données d'origine, nous pouvons observer l’existence d’un retour à la ligne à la fin de chaque caractéristique. Pour en faire de même dans la cellule contenant la formule, nous allons nous rendre dans le menu « Accueil » du ruban, puis sélectionner « Retour à la ligne automatique ».
Nous allons à présent en venir au sujet qui nous intéresse, à savoir comment modifier la hauteur et la largeur d’une cellule.
2. La méthode manuelle
Si l’on regarde la cellule qui nous retourne les caractéristiques de notre machine, nous pouvons constater que celle-ci est plus grande que son contenu.
Pour réduire l’espace ainsi perdu, nous pouvons simplement modifier manuellement la largeur de la colonne, en glissant le curseur qui apparaît lorsque l’on place la souris entre deux en-têtes de colonne, vers la gauche pour réduire la largeur de colonne, ou vers la droite pour l’augmenter.
Lorsque nous sélectionnons un autre modèle, nous constatons que les caractéristiques changent de manière automatique. Ainsi la taille de la cellule que nous venons juste d’ajuster est à présent trop étroite.
Nous avons ici une ligne qui se retrouvent sur deux lignes. Il va donc falloir agrandir la largeur de cette cellule pour que les informations se trouvent sur une seule ligne.
Sachez également qu’il existe un raccourci pour adapter automatiquement la hauteur d’une ligne ou la largeur d’une colonne à son contenu : il suffit de double cliquer sur le curseur qui apparaît lorsque l'on se place entre deux numéros de lignes (ou deux lettres de colonnes).
Malheureusement ce raccourci souffre d’un « bug » un peu gênant : lorsque que la cellule possède beaucoup de caractères sur une seule ligne, celle-ci ne va pas s’élargir suffisamment pour afficher toutes les informations sur une seule ligne de texte.
Il est alors nécessaire de donner une grande largeur à la colonne, puis de double cliquer sur l’entre-deux colonnes pour ajuster automatiquement le contenu. En fait, l’agrandissement automatique d’une colonne est limité à une certaine taille.
Cette méthode, dite « manuelle » permet de rapidement adapter la taille des cellules à leur contenu. Cela dit, elle nécessite malgré tout une manipulation. Nous allons maintenant voire comment procéder pour arriver au même résultat, avec l’aide d’une une macro-commande qui se lancera de manière automatique et totalement transparente.
3. La méthode automatisée
Pour modifier automatiquement la taille des cellules, que cela soit en hauteur, ou encore en largeur, nous allons utiliser une macro-commande développée en VBA.
Pour cela nous allons nous rendre dans le code source de la page, en effectuant un clic droit sur l'onglet de la feuille, puis en sélectionnant « Visualiser le code ».
Ainsi nous arrivons dans l'outil développement d'Excel, qui comme nous venons de le voir porte le nom de « Microsoft Visual Basic pour Applications », ou encore « VBA ».
Dans cette feuille blanche nous allons insérer une procédure événementielle, c’est-à-dire un ensemble d’instructions qui vont être exécutées à chaque fois qu’un évènement bien particulier va se produire. L’événement qui nous intéresse ici est « une cellule de la feuille de calcul a été modifiée », soit en langage VBA : « Worksheet_Change ».
Pour insérer cette procédure évènementielle, nous allons sélectionner dans le menu déroulant se trouvant à gauche au-dessus de la feuille de code « Worksheet ».
Puis l’événement « Change » dans le second menu situé juste à droite du premier :
De cette manière, Excel ajoute automatiquement pour nous l’événement qui nous intéresse.
À présent, à chaque fois qu’une cellule de la feuille de calcul va être modifiée, l’ensemble des instructions que nous allons saisir entre ces deux lignes vont être appelées automatiquement.
De plus, les cellules concernées (dont la valeur est modifiée) vont être retourné dans la variable instanciée et qui porte le nom de « Target ».
Pour commencer, nous allons ajuster la largeur des colonnes dans lesquelles se trouve une cellule modifiée. Pour cela nous allons utiliser l’instruction « Autofit » appliquée sur toute la colonne dans laquelle se trouve la cellule « Target » (Target.EntireColumn) de la manière suivante :
Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireColumn.AutoFit
End Sub
L’ « Autofit » correspond au double-clic qui ajuste automatiquement la largeur d’une colonne ou la hauteur d’une ligne, et que nous avons dans la partie précédente de cet article.
Faisons de même pour la hauteur de ligne :
Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireColumn.AutoFit
Target.EntireRow.AutoFit
End Sub
Rappelez-vous, dans la partie précédente, nous avions vu que l’ « Autofit » ne pouvait pas agrandir indéfiniment la largeur d’une colonne. Pour contourner cette limitation, nous allons dans un premier temps donner une grande largeur (d’une taille de 255, soit la taille maximale qu’une colonne puisse avoir), afin que l’ « Autofit » se contente de la diminuer.
Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireColumn.ColumnWidth = 255
Target.EntireColumn.AutoFit
Target.EntireRow.AutoFit
End Sub
Enfin, au lieu d’appliquer ces modifications uniquement sur la cellule modifiée, nous allons utiliser l’ensemble des cellules de la région concernée (c’est-à-dire les cellules du tableau).
Private Sub Worksheet_Change(ByVal Target As Range)
With Target.CurrentRegion
.EntireColumn.ColumnWidth = 255
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With
End Sub
Pour simplifier la lecture du code VBA, nous avons également utilisé l’instruction « with » sur « Target.CurrentRegion ». Cela signifie que chaque instruction qui commence par un point (« . ») sera appliquer sur celle-ci. Le code ci-dessus est ainsi équivalent à :
Private Sub Worksheet_Change(ByVal Target As Range)
Target.CurrentRegion.EntireColumn.ColumnWidth = 255
Target.CurrentRegion.EntireColumn.AutoFit
Target.CurrentRegion.EntireRow.AutoFit
End Sub
Si nous testons notre procédure, nous pouvons constater qu'à présent la hauteur va s'adapter pour l'ensemble de nos cellules du tableau.
4. Ajouter l’autofit sur une plage de cellules (ajustement automatique de la taille) sur Excel
Supposons maintenant que nous souhaitions ajouter l’ajustement automatique de la taille des cellules, non plus sur une cellule, mais sur une plage contenant un ensemble de cellules que nous allons prédéfinir.
Pour illustrer cet exemple, nous allons souhaiter que la taille des cellules de la base des articles s’ajuste à chaque modification.
Pour commencer, nous allons copier la macro-commande que nous venons de saisir dans le code de la feuille Taille de cellule :
Pour la coller dans la feuille Base :
De cette manière, tous les changements que nous allons effectuer dans la feuille de calculs Base vont avoir pour conséquence de recalculer la taille des cellules :
Y compris, lorsque les modifications sont effectuées en dehors de la base des articles :
Pour éviter ce problème, nous allons souhaiter limiter l’autofit des cellules à la plage de la base des données.
Si l’on regarde attentivement les coordonnées de la base des articles, cette dernière commence à partir de la cellule A6, jusqu’à la cellule C11.
Nous allons donc modifier la macro-commande pour vérifier que la cellule modifiée fait bien partie de cette plage de cellules. Pour cela, nous allons utiliser l’instruction INTERSECT de VBA, qui permet de récupérer les cellules communes aux plages de cellules saisies en paramètres. Lorsqu’il n’existe aucune cellule de commune, alors l’instruction INTERSECT renvoie comme valeur nothing. Il suffit alors de quitter l’instruction :
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [a6:c11]) Is Nothing Then Exit Sub
With Target.CurrentRegion
.EntireColumn.ColumnWidth = 255
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With
End Sub
De cette manière, nous sommes certains que le code VBA passe en dessous de la ligne que nous venons d’ajouter, alors cela signifie que la cellule modifiée (Target) fait bien partie de la plage des cellules A6 :C11.
En revanche, si d’aventure nous souhaitons ajouter une nouvelle ligne à la des articles, le résultat sera le même, et les cellules ne vont pas voir leur taille évoluer :
Nous pourrions définir une plage de cellules plus importante (par exemple A6:C100), mais dans ce cas nous perdrons le dynamisme de la macro.
Nous allons plutôt utiliser une plage nommée, qui aura comme avantage de s’adapter automatiquement aux coordonnées de la plage des cellules.
De plus, étant donné que nous utilisons un tableau de données, cette plage existe déjà, nous n’avons qu’à la récupérer (en cliquant sur une des cellules du tableau, puis menu Création du ruban > Récupérer le nom que nous avions défini précédemment : « _baseArticles ») :
Il suffit ensuite d’ajouter simplement le nom de la directement dans la macro, au lieu des coordonnées a6:c11 :
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [_baseArticles]) Is Nothing Then Exit Sub
With Target.CurrentRegion
.EntireColumn.ColumnWidth = 255
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With
End Sub
Et voilà le résultat :
Enfin, nous allons voir une dernière solution, qui va consister à réajuster les tailles des cellules contenues dans une plage de cellules, même lorsque la cellule mise-à-jour ne fait pas partie de la plage.
Pour commencer, nous supprimons la ligne que nous venons d’ajouter. En effet, à présent la macro doit pouvoir s’exécuter, peu importe de savoir si la cellule appartient ou non à la plage :
Private Sub Worksheet_Change(ByVal Target As Range)
With Target.CurrentRegion
.EntireColumn.ColumnWidth = 255
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With
End Sub
Ensuite, il va simplement suffit de remplacer le Target.CurrentRegion présent juste après le With par le nom de la plage des cellules :
Private Sub Worksheet_Change(ByVal Target As Range)
With [_baseArticles]
.EntireColumn.ColumnWidth = 255
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With
End Sub
Cette dernière version de la macro-commande aura un intérêt si la valeur des cellules de la plage se recalcul automatiquement en fonction du résultat d’une autre cellule, qui n’en fait pas partie (je vous invite à consulter la seconde vidéo de cet article pour un exemple).
Article initialement publié le 7 février 2019, puis mis à jour le 16 mai 2019 (ajout de la seconde vidéo).