Archives mensuelles : mars 2017

Excel créer un bouton insertion avec une Macro VBA

Voici notre objectif  : nous souhaitons ne pas être obligé d’utiliser l’ascenseur d’Excel pour descendre en bas d’un tableau qui contient déjà beaucoup de lignes pour y taper une donnée supplémentaire. De plus nous souhaitons que cette donnée ne soit pas ajoutée après la dernière ligne du tableau, mais à l’avant dernière ligne, juste avant la ligne Total. Et pour corser le tout, cette donnée est numérique, et doit être pris en compte dans la formule SOMME de la ligne total.

Pour cela nous allons essayer 3 méthodes différentes :
1. Le formulaire généraliste d’Excel (généré automatiquement)
2. L’enregistrement d’une macro à la souris
3. La programmation d’une macro au clavier

Nous allons voir que seule la 3e méthode nous permet de concrétiser tout notre « cahier des charges ».

1. Le formulaire généraliste d’Excel (généré automatiquement)

Pour réaliser la 1ère méthode, vous devez avoir le bouton formulaire accessible. En effet, le bouton formulaire n’existe pas dans les rubans d’Excel par défaut. Vous devez donc afficher le bouton en allant dans les Options Excel. Voir l’article Ajouter le bouton formulaire dans Excel

Dans cet exemple je vais utiliser le bouton Formulaire d’Excel qui génère un Formulaire qui n’est pas personnalisable : il réutilise les libellés de vos en-têtes de colonne que vous avez vous-mêmes taper dans votre tableau et il vous propose toujours les mêmes boutons de validation « Nouvelle » « Précédente » « suivante » « Critères » « Fermer ».

j’ai donc sélectionné mon tableau en prenant en compte mes en-têtes de colonne et la ligne total, puis j’ai cliqué sur le bouton Formulaire. En tapant une nouvelle entrée et en cliquant sur le bouton « Nouvelle », la nouvelle ligne est insérée sous la ligne Total, alors que je voulais qu’elle soit insérée au-dessus de la ligne Total.

insertionparformgeneral3

Dans ce 2e exemple, j’ai sélectionné le tableau sans la ligne total. Quand j’utilise à nouveau le formulaire pour insérer une nouvelle ligne, voici le message d’erreur qu’Excel m’affiche

insertionparformgeneral2

Enfin dans ce dernier exemple, j’ai sélectionné seulement une cellule au-dessus de la ligne Total, mais le résultat est le même que dans le 1er exemple ci-dessus.

insertionparformgeneral

Nous allons donc aborder une autre fonctionnalité d’Excel : les macros.

2. L’enregistrement d’une macro à la souris

Les macros permettent de déclencher une séquence d’opérations qui s’enchaînent automatiquement. Cette séquence peut être créée en enregistrant vos clics de souris.

enregistremacro  enregistremacro2

Mais parfois cette méthode à la souris ne fonctionne pas, elle n’est adaptée qu’au macro les plus simples.

3. La programmation d’une macro au clavier

Je vais vous présenter ici une macro qui ne peux pas être enregistrée à la souris. Il vous faut donc écrire le code VBA au clavier.

Je pourrais écrire cette macro en l’appliquant directement à un formulaire personnalisé, mais pour apprendre le VBA plus en douceur, j’ai décidé de me passer de formulaire dans cet exemple. J’ai donc laissé volontairement une ligne vide au-dessus du tableau et je vais ajouter un bouton « insérer » à côté de cette ligne vide.

Si vous n’avez pas encore le menu Développeur dans le ruban d’Excel, à droite du menu Affichage, vous devez rendre accessible le menu « Développeur » en vous rendant dans les Options Excel : voir l’article Ajouter le menu Développeur dans le ruban Excel

Une fois que vous disposez du menu Développeur, Cliquez dessus, puis cliquez sur « Insérer » dans le bloc « Contrôles » puis sur la première icône, un rectangle gris. Vous pouvez maintenant dessiner à côté de votre tableau un rectangle à la souris dans lequel vous pourrez écrire « Insérer ».

excel macro simple

Maintenant, il s’agit de rendre fonctionnel le bouton « Insérer » quand on clique dessus. Nous allons donc écrire une macro que j’ai appelé AjoutLigne et que nous allons associer au bouton « Insérer ». Pour cela dans le menu Développeur cliquez sur l’icone « Visual Basic » tout à gauche du ruban.

J’ai procédé par étape, et nous pouvons tester si la macro fonctionne à la fin de chacune de ces étapes.

1ere étape : Ajouter une ligne vide au-dessus de la ligne total

Les lignes qui commencent par un guillemet (‘) ne sont pas du code mais des commentaires.


Sub AjoutLigne()

‘Aller en A3 ; c’est la cellule qui contient la première donnée
Range(« A3 »).Select

‘Aller à la dernière ligne remplie : la ligne total. Nous allons donc vérifier à chaque ligne de la colonne A si la cellule contient le texte « total ». Pour cela, nous devons faire une boucle. A chaque itération, la cellule pris en compte (la cellule active) est celle de la ligne en dessus. La boucle s’arrête lorsque le texte « total » est trouvé dans la cellule active.
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = « total »

‘Insérer une ligne vide audessus de la ligne total
Selection.EntireRow.Insert

End Sub

Vous pouvez tester cette macro. Pour cela nous devons d’abord associer le code au bouton « Insérer »

Retounez dans Visual Basic pour compléter la macro.

Pour éviter que le bouton INsertion ne s’allonge à chaque fois que vous insérer une ligne, faites ceci :

  • Clique droit sur le bouton 
  • Format de contrôle 
  • Propriétés 
  • cocher « ne pas déplacer ou dimensionner avec les cellules »

2e étape : Récupérer les valeurs tapées dans la 1ère ligne pour les ajouter dans la ligne insérée vide


Sub AjoutLigne()

‘Aller en A3
Range(« A3 »).Select

‘Aller à la dernière ligne remplie : la ligne total
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = « total »

‘Insérer une ligne vide audessus de la ligne total
Selection.EntireRow.Insert

‘prendre les valeurs de la première ligne pour les mettre dans la nouvelle ligne
ActiveCell.Value = Range(« A1 »).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(« B1 »).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(« C1 »).Value

End Sub

3e étape : AJouter la formule SOMME dans la ligne total


Sub AjoutLigne()

‘Aller en A3
Range(« A3 »).Select

‘Aller à la dernière ligne remplie : la ligne total
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = « total »

‘Insérer une ligne vide audessus de la ligne total
Selection.EntireRow.Insert

‘Récupérer le numéro de cette ligne pour construire la formule Somme du Total plus bas dans le code
LigneFinSomme = ActiveCell.Row

‘prendre les valeurs de la première ligne pour les mettre dans la nouvelle ligne
ActiveCell.Value = Range(« A1 »).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(« B1 »).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(« C1 »).Value

‘Ajouter la formule Somme pour calculer le total
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaLocal = « =SOMME(C3:C » & LigneFinSomme & « ) »

End Sub

4e étape : Supprimer les valeurs de la 1ère ligne pour pouvoir insérer directement à nouveau d’autres valeurs


Sub AjoutLigne()

‘Aller en A3
Range(« A3 »).Select

‘Aller à la dernière ligne remplie : la ligne total
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = « total »

‘Insérer une ligne vide audessus de la ligne total
Selection.EntireRow.Insert

‘Récupérer le numéro de cette ligne pour construire la formule Somme du Total plus bas dans le code
LigneFinSomme = ActiveCell.Row

‘prendre les valeurs de la première ligne pour les mettre dans la nouvelle ligne
ActiveCell.Value = Range(« A1 »).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(« B1 »).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(« C1 »).Value

‘Ajouter la formule Somme pour calculer le total
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaLocal = « =SOMME(C3:C » & LigneFinSomme & « ) »

‘Enlever les valeurs insérer en première ligne pour pouvoir faire une nouvelle insertion sans être obligé de supprimer à la main ces données
Range(« B1 »).Value = «  »
Range(« C1 »).Value = «  »

End Sub