Comment créer un formulaire VBA sans Userform ? (La méthode facile sur Excel)

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Dans ce tutoriel, je vais vous montrer comment créer un formulaire VBA sur Excel pour stocker des informations sur Excel au sein d’une petite base de données.

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

Partie 1 : Comment créer un formulaire VBA sans Userform sur Excel ?

 

Partie 2 : Comment insérer un Placeholder dans un formulaire VBA sans Userform sur Excel ?

 

1. Préparation du formulaire

Étant donné que nous souhaitons créer un formulaire sans Userform, nous allons devoir l’insérer directement sur la feuille de calcul.

Pour cet exemple, nous allons souhaiter créer une petite base de gestion des clients d’une entreprise :

Excel formation - formulaire avec placeholder - 01

Notre objectif sera donc de remplir les champs correspondants, puis de les enregistrer dans la base en cliquant sur un simple bouton.

Bien entendu pour réaliser cet enregistrement, nous allons devoir passer par le développement de quelques petites macros en VBA, mais rassurez-vous, comme vous pourrez vous en rendre compte, celles-ci seront très simples à mettre en place.

D’autant plus que nous allons détailler le fonctionnement de chaque ligne dans le détail.

Mais si après avoir suivi ce tutoriel vous souhaitez en savoir davantage sur le développement en VBA, vous pouvez commander mon livre « Apprendre VBA pour les grands débutants » en suivant ce lien.

 

2. Enregistrer les données dans une base

Pour simplifier au maximum l’enregistrement des données dans la base, nous allons créer celle-ci directement sur la feuille dans laquelle se trouve le formulaire.

Pour cela nous commençons simplement par saisir les intitulés de colonnes dans les cellules F8 à J8 :

Excel formation - formulaire avec placeholder - 02

Maintenant, nous allons créer le bouton qui va permettre d’appeler la macro.

Pour cela, nous nous rendons dans le menu Insertion, afin de cliquer sur Zone de texte :

Excel formation - formulaire avec placeholder - 03

Le curseur de la souris prend alors la forme d’une croix, et nous pouvons ainsi dessiner le bouton aux mesures et aux coordonnées souhaitées :

Excel formation - formulaire avec placeholder - 04

Ensuite, nous pouvons personnaliser ce bouton en saisissant un titre, puis en le mettant en forme depuis le menu Format de forme du menu ruban :

Excel formation - formulaire avec placeholder - 05

Pour créer la macro, nous effectuons un clic droit sur ce bouton afin de cliquer sur Affecter une macro.

Dans la fenêtre qui apparaît nous saisissons le nom que nous souhaitons donner à la macro (par exemple enregistrerFiche), puis nous cliquons sur le bouton « Nouvelle » :

Excel formation - formulaire avec placeholder - 06

VBE, l’éditeur de code VBA va alors se lancer la nouvelle macro sera créée automatiquement :

Excel formation - formulaire avec placeholder - 07

Maintenant, nous allons pouvoir créer la macro d’enregistrement.

La première information dont nous allons avoir besoin est le numéro de la ligne sur laquelle nous allons insérer l’enregistrement.

Pour cela, nous allons créer une nouvelle variable, que nous appelons ligne et que nous typons en tant que Integer, c’est-à-dire en tant que nombre entier.

Pour créer une variable, nous utilisons le mot-clé Dim :

    Dim ligne As Integer

Puis, nous allons souhaiter connaître le numéro de la première cellule vide de la colonne F.

Pour cela, nous donnons à la variable ligne la valeur de la ligne sur la laquelle se trouvent les en-têtes de la base, c’est-à-dire la ligne 8 :

    ligne = 8

Nous pourrons ensuite incrémenter cette valeur tant que la cellule correspondant à la valeur de cette variable au sein de la colonne F est vide.

Pour cela, nous allons utiliser une boucle Do While… Loop :

    Do While Range("f" & ligne) <>  ""
        ligne = ligne + 1
    Loop

Une boucle Do While… Loop permet de répéter une série d’instructions tant qu’une condition est remplie.

Pour en savoir plus sur cette boucle, vous pouvez consulter le tutoriel en cliquant ici.

Maintenant que nous connaissons le numéro de la ligne sur laquelle nous devons enregistrer les données, il suffit d’y insérer les valeurs du formulaire :

    Range("f" & ligne) = [c8]
    Range("g" & ligne) = [c10]
    Range("h" & ligne) = [c12]
    Range("i" & ligne) = [c13]
    Range("j" & ligne) = [c15]

Et voilà, notre macro est maintenant terminée, il suffit de remplir le formulaire, puis de cliquer sur le bouton pour enregistrer les données :

Excel formation - formulaire avec placeholder - 08

La fiche est bien enregistrée !

 

3. Retrouver une fiche

Maintenant, voyons comment retrouver une fiche déjà enregistrée.

Pour cela, nous allons utiliser une macro évènementielle qui va consister à regarder si la valeur de la cellule C8 dans laquelle se trouve le nom de l’entreprise est modifiée.

La particularité des macros évènementielles est de se lancer automatiquement lorsque quelque chose (un « événement ») se passe sur le classeur ou sur la feuille de calcul.

Cliquez-ici pour en savoir plus sur les évènements.

Pour mettre en place cette macro évènementielle, nous nous rendons à nouveau dans VBE, puis dans l’explorateur de projet, nous double cliquons sur le nom de la feuille dans laquelle se trouve le formulaire (ici la feuille « excelformation.fr) :

Excel formation - formulaire avec placeholder - 09

VBA nous affiche alors le module de la feuille correspondante.

Pour appeler l’évènement qui va permettre de lancer une macro lorsqu’une cellule voit son contenu modifié, nous déroulons le premier menu déroulant qui se trouve au-dessus du module afin de choisir Worksheet :

Excel formation - formulaire avec placeholder - 10

Dans le second menu, nous choisissons Change :

Excel formation - formulaire avec placeholder - 11

De cette manière, Excel nous insère l’évènement Worksheet_Change :

Private Sub Worksheet_Change(ByVal Target As  Range)
 
End Sub

Pour commencer, nous allons regarder si la cellule modifiée, obtenue par l’argument Target correspond à la cellule placée aux coordonnées C8, en appelant la propriété Target :

    If Target.Address = "$C$8" Then
    
    End If

Lorsqu’effectivement il s’agit de cette cellule C8, alors nous allons pouvoir regarder si une entrée ayant pour nom d’entreprise a la même valeur que celle saisie par l’utilisateur.

Pour cela, il existe plusieurs méthodes, la plus simple consistant à effectuer une recherche sur la colonne F avec la méthode Find():

        Dim ligne As Range
        Set ligne = [f:f].Find([c8])

Si Excel trouve la ligne correspondante, alors celle-ci sera retournée dans cette variable ligne.

Dans le cas contraire, ligne renverra Nothing.

Nous allons donc regarder si cette valeur est différente de Nothing, afin de pouvoir récupérer les valeurs correspondantes :

Private Sub Worksheet_Change(ByVal Target As  Range)
 
    If Target.Address = "$C$8" Then
        Dim ligne As Range
        Set ligne = [f:f].Find([c8])
        
        If Not ligne Is Nothing Then
            [c10] = ligne.Offset(0, 1)
            [c12] = ligne.Offset(0, 2)
            [c13] = ligne.Offset(0, 3)
            [c15] = ligne.Offset(0, 4)
        End If
    End If
End Sub

Nous pouvons maintenant saisir le nom d’une entreprise dans la cellule C8 pour remplir le formulaire automatiquement :

Excel formation - formulaire avec placeholder - 12

Pour faciliter la saisie d’une entreprise déjà renseignée, nous pouvons insérer un menu déroulant dans la cellule C8 en utilisant la méthode découverte dans le tutoriel dédié que vous pourrez retrouver en cliquant ici.

Pour cela, nous commençons par sélectionner la cellule C8, puis nous nous rendons dans le menu Accueil > Validation de données :

Excel formation - formulaire avec placeholder - 13

Ensuite, dans la fenêtre qui s’affiche à l’écran, nous sélectionnons d’autoriser les valeurs contenues dans une liste, puis dans le champ Source, nous saisissons la formule suivante :

=DECALER(F9;;;NBVAL(F:F)-1)

Excel formation - formulaire avec placeholder - 14

Avant de valider, nous nous rendons dans l’onglet Alerte afin de décocher l’option « Quand des données non valides sont tapées », ce qui nous permettra de saisir des informations qui ne figure pas dans la liste des entreprises enregistrées et donc de créer de nouvelles lignes :

Excel formation - formulaire avec placeholder - 15

Ensuite, nous pouvons valider en appuyant sur le bouton [OK].

Maintenant, un menu déroulant apparaît lorsque nous sélectionnons la cellule

Excel formation - formulaire avec placeholder - 16

 

4. Modifier une fiche

Maintenant, voyons comment modifier une fiche déjà enregistrée.

Cette opération peut paraître compliquée à mettre en place, mais compte tenu de la manière dont nous avons construit les macros précédentes, il suffit simplement de modifier la ligne :

Do While Range("f" & ligne) <>  ""

Par :

    Do While Range("f" & ligne) <>  "" And Range("f" & ligne) <> [c8]

Ici, nous arrêtons en effet de passer en revue les lignes en revue dès que nous arrivons sur la ligne voulue !

Ensuite, nous enregistrons les données de manière classique.

 

5. Effacer le contenu du formulaire

Pour effacer tout le contenu du formulaire, les choses sont également très simples.

Il suffit en effet de supprimer le contenu de la colonne C.

Nous commençons par créer un nouveau bouton :

Excel formation - formulaire avec placeholder - 17

Sur lequel nous affectons une nouvelle macro :

Excel formation - formulaire avec placeholder - 18

Et enfin dans cette macro, nous supprimons tout le contenu de la colonne C avec la ligne suivante :

Sub viderFiche()
    [c:c].ClearContents
End Sub

 

6. Créer un système de Placeholder sur le formulaire

Maintenant que nous avons vu comment créer un formulaire VBA très facilement en l’intégrant directement sur la feuille de calcul, nous pouvons aller un peu plus loin, en mettant en place un système de Placeholder, ou message fantôme.

Il s’agit d’afficher un message directement dans la cellule, qui disparaîtra ensuite lorsque l’utilisateur va saisir des données.

 

   6.1. Enregistrement des messages sur une table de données

 

Pour commencer, nous allons créer une table d’enregistrement des messages que nous allons souhaiter insérer dans les placeholders du formulaire.

Nous créons une nouvelle feuille de calcul que nous nommons « Placeholder » et sur laquelle nous saisissons les titres de colonnes :

  • Cellule en cellule A1
  • Placeholder en cellule B1

Excel formation - formulaire avec placeholder - p2 - 02

Dans la première colonne, nous venons saisir les coordonnées des cellules du formulaire :

Excel formation - formulaire avec placeholder - p2 - 03

Et dans la seconde, nous saisissons les messages à insérer dans les Placeholders en commençant par un astérisque :

Excel formation - formulaire avec placeholder - p2 - 04

Nous verrons un peu plus tard que ces astérisques vont nous permettre de personnaliser l’apparence des cellules concernées.

 

   6.2. Afficher le Placeholder dans le formulaire

 

Maintenant, pour insérer le message dans la cellule lorsque celle-ci n’a pas encore été remplie par l’utilisateur, nous allons créer une petite macro VBA.

Pour cela, nous allons revenir dans l’évènement Worksheet_Change de la feuille « excelformation.fr » :

Excel formation - formulaire avec placeholder - p2 - 05

Nous allons ajouter une seconde partie dans cet évènement dans laquelle nous allons :

  • Commencer par regarder si la cellule modifiée, retournée par la variable Target est vide,
  • Lorsque c’est le cas, nous allons rechercher si une cellule contient les coordonnées de Target dans la colonne A de la feuille « excelformation.fr »
  • Si c’est également le cas, nous pourrons récupérer cette valeur pour l’insérer dans la cellule Target

Pour vérifier si la cellule est vide, nous pouvons utiliser la fonction IsEmpty(), en passant en paramètre la variable Target :

    If IsEmpty(Target) Then
    End If

Nous pouvons maintenant rechercher si les coordonnées de Target apparaissent dans la première colonne de la feuille « Placeholder » que nous avons créée juste avant.

Nous créons donc une variable de type Range que nous appelons cellule.

Comme nous l’avons vu dans la partie précédente, nous allons effectuer la recherche en utilisant la méthode Find().

Attention, pour effectuer une recherche sur les coordonnées relatives de la cellule, nous utilisons les arguments RowAbsolute et ColumnAbsolute auxquels nous donnons la valeur False :

        Dim cellule As Range
        Set cellule = Sheets("Placeholder").[a:a].Find(Target.Address(False,  False))

Excel formation - formulaire avec placeholder - p2 - 06

Et maintenant, si Excel parvient à identifier cette cellule, alors nous récupérons la valeur de la cellule située juste à droite pour l’insérer dans la cellule Target.

Pour cela nous utilisons la méthode Offset :

        If Not cellule Is Nothing Then
            Target = cellule.Offset(0,  1)
        End If

Pour finir, nous allons revenir sur la macro viderFiche() qui permet de purger le formulaire, afin de vider chaque cellule individuellement, ce qui permettra d’exécuter l’évènement que nous venons de modifier sur toutes les cellules du formulaire :

Sub viderFiche()
    [c8].ClearContents
    [c10].ClearContents
    [c12].ClearContents
    [c13].ClearContents
    [c15].ClearContents
End Sub

Maintenant, nous pouvons tester de supprimer le contenu du formulaire en cliquant sur le bouton dédié :

Excel formation - formulaire avec placeholder - p2 - 07

 

   6.3. Modifier la mise en forme des placeholders

 

Maintenant que nous sommes capables d’afficher correctement les placeholders dans les cellules, il ne reste plus qu’à modifier la mise en forme de celles-ci pour les rendre plus perceptibles.

Pour cela, nous allons pouvoir mettre en place une mise en forme conditionnelle que nous pourrons faire reposer sur l’astérisque que nous retrouvons au tout début de chaque texte.

Une mise en forme conditionnelle permet comme son nom l’indique d’adapter automatiquement l’apparence d’une cellule en fonction de son contenu.

Pour mettre en place une mise en forme conditionnelle, nous commençons par sélectionner les cellules concernées :

Excel formation - formulaire avec placeholder - p2 - 08

Ici, nous pouvons sélectionner une plage de cellules contenant des cellules vides, mais si nous souhaitons ne sélectionner que les cellules sur lesquelles se trouve du contenu, il suffit de maintenir la touche [Ctrl] du clavier enfoncée en cliquant sur chacune d’entre elles.

Une fois les cellules sélectionnées, nous nous rendons dans le menu Accueil > Mise en forme conditionnelle et nous cliquons sur « Nouvelle règle… » :

Excel formation - formulaire avec placeholder - p2 - 09

Dans la fenêtre qui s’affiche, nous choisissons d’« Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué », puis nous saisissons la formule suivante :

=GAUCHE(C8)="*"

Excel formation - formulaire avec placeholder - p2 - 10

Cette formule regarde si le tout premier caractère de la cellule C8 (qui est la cellule active) est un astérisque.

Lorsque c’est le cas, alors la formule renverra VRAI et la mise en forme conditionnelle que nous allons mettre en place sera appliquée.

Pour paramétrer cette mise en forme, nous cliquons sur le bouton « Format… » :

Excel formation - formulaire avec placeholder - p2 - 11

Une fois que nous validons, en appuyant sur le bouton [OK], Excel modifie automatiquement la mise en forme des cellules commençant par l’astérisque :

Excel formation - formulaire avec placeholder - p2 - 12

 

 



Articles qui pourraient vous intéresser

Comment protéger efficacement un classeur Excel en utilisant une clé USB comme protection physique ?
Comment lire un fichier externe (txt, csv,…) avec une fonction Excel VBA ?
Comment créer un arbre généalogique en quelques minutes sur Excel ?
Comment lire un fichier externe depuis un classeur sans VBA Excel ?
Comment consolider plusieurs classeurs Excel dans un classeur unique ?
Comment automatiser la mise à jour des formules Excel ?
Comment vérifier qu’une adresse mail est valide sur Excel ?
Comment protéger efficacement le contenu de vos fichiers Excel ?
Comment afficher une image différente en fonction de la valeur d’une cellule Excel (sans VBA) ?
Comment faire clignoter une cellule Excel ? La mise en forme dynamique
COMMENT EFFACER ET PERSONNALISER LA LISTE DES DOCUMENTS RÉCENTS D’EXCEL (la vraie méthode)
Comment compter le nombre de cellules contenant un nombre de caractères sur Excel sans VBA ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2022 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.