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

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 :

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 :

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 :

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 :

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 :

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 :

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 » :

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

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 :

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) :

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 :

Dans le second menu, nous choisissons Change :

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 :

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 :

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)

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 :

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

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

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 :

Sur lequel nous affectons une nouvelle macro :

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

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

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

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 » :

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))

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é :

   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 :

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… » :

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)="*"

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… » :

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 :