VBA pour Excel - Dialogues et formulaires Dialogues, formulaires et alternatives L'éditeur Les contrôles Ouvrir et fermer un formulaire Exemple 1: Écran d'accueil Exemple 2: Sélectionner un dossier Exemple 3: Barre de progression Exemple 4: Saisir et valider des données Dialogues, formulaires et alternatives Les programmes utilisent les dialogues et les formulaires pour obtenir des données ou des instructions de l'utilisateur, pendant l'exécution du programme. Certains formulaires offrent des menus, permettant à l'utilisateur de choisir entre différentes actions possibles. En VBA Excel, vous pouvez utiliser une feuille Excel (de préférence la première) à la place de formulaires VBA pour construire des menus. Cela se fait simplement en insérant des objets (boutons de formulaire par exemple) sur la feuille Excel et en y assignant une macro VBA ou un hyperlien. Par ailleurs, il se peut que votre programme VBA doive obtenir des instructions ou des données de l'utilisateur. Si les choix sont simples (continuer ou arrêter, par exemple), il est recommandé d'utiliser la fonction:
Accueil Concepts de programmation VBE l'éditeur VBA Déclarations, types, références, expressions et assignations Tests et branchements Les boucles Gestion d'erreur Collections et tableaux Dialogues et formulaires Objets et événements Excel Conseils de programmation Liste d'instructions
Word 2003 Word 2007 Excel Excel PowerPoint 2003 PowerPoint 2007 Access Access 2010 Internet Général Intelligence financière Vidéos
Précédent Suivant Page Principale FAQ Nouveautés Plan du site Références Motivations Manuels de formation Fichiers de démonstration
Par courriel Abonnez-vous à l'infolettre LCI

Si la boîte de dialogue doit permettre à l'utilisateur de fournir une valeur (et une seule), il est possible d'utiliser l'instruction:

Si Excel dispose d'une boîte de dialogue qui convient au besoin, il est possible de les utiliser à partir d'un programme VBA:

Il y a aussi l'objet FileDialog:

Pour les besoins plus complexes, comme la saisie de plusieurs valeurs avec une validation importante, vous pouvez utiliser un formulaire VBA.

Finalement, pour une saisie vraiment efficace de données vous devriez utiliser Access, dont les formulaires sont beaucoup plus puissants que ceux que vous pouvez programmer en VBA Excel.

L'éditeur de formulaires VBA (MS Forms)

L'éditeur VBA comporte une fenêtre spécifique pour créer ou modifier un formulaire.

Créez un formulaire:

	Du menu Insertion, sélectionnez UserForm.
	L'explorateur de projet ouvre une branche Feuilles qui ne contient pas des feuilles Excel, mais des formulaires VBA.
	La fenêtre principale affiche le cadre d'un formulaire.
	C'est l'entête de l'explorateur de projet qui contrôle l'affichage de la fenêtre principale: Affichage du code VBA associé au formulaire (une sorte de module incorporé). Alternatives: Menu Affichage/Code ou F7. Affichage du formulaire. Alternatives: Menu Affichage/Objet ou Maj+F7 . Modifie le mode d'affichage de l'explorateur de projets. Inutile.
	La boîte à outils devrait aussi apparaître. Sinon, du menu Affichage, sélectionnez .
	Finalement, remarquez sous l'explorateur de projet, la feuille de propriétés du formulaire. C'est là que seront affichées les propriétés de l'objet sélectionné dans le formulaire (ou celles du formulaire lui-même). Empressez-vous de changer le nom du formulaire que VBA a nommé UserForm1 (ou n). Il est important de choisir le nom définitif du formulaire AVANT de commencer à lui ajouter du code VBA.

Événements

On attache des procédures sub aux événements que subit le formulaire.

Par exemple, une procédure sub nommée Sub UserForm_Initialize() est exécutée à chaque fois que le formulaire est ouvert (initialisé).

Pour écrire une procédure événementielle, afficher la fenêtre code du formulaire (F7), sélectionner l'objet qui subit l'événement (le formulaire ou un contrôle du formulaire), dans la liste déroulante de gauche, et l'événement voulu dans la liste de droite. L'éditeur VBA crée alors une procédure Sub vide correspondant à l'événement.

Il est aussi possible de créer une procédure Sub événementielle d'un contrôle en double-cliquant dessus.
C'est alors l'événement par défaut du contrôle qui est choisi.

Contrôles disponibles

On appelle contrôle les classes d'objet qu'on peut insérer dans un formulaire. La description détaillée de chacun
de ceux qui sont disponibles en VBA dépasse le cadre du présent texte, mais voici une présentation sommaire de chacun d'entre eux:

	outil de sélection	n'est pas un contrôle, mais l'outil à utiliser pour sélectionner un ou plusieurs contrôles.
	libellé (intitulé ou label)	sert à ajouter du texte explicatif dans le formulaire, comme des instructions à l'utilisateur ou des étiquettes pour les zones de texte.	Propriétés intéressantes: Caption Value TextAlign
	zone de texte (textbox)	sert à inclure dans le formulaire une zone dans laquelle l'utilisateur sera invité à entrer des valeurs. La valeur entrée est TOUJOURS enregistrées en format texte, et le programme VBA devra identifier et faire les conversions appropriées.	Propriétés intéressantes: Name Enabled Value Text ControlSource TabIndex TextAlign Événements intéressants: Change Enter Exit BeforeUpdate AfterUpdate
	Zone de liste modifiable (déroulante ou ComboBox)	Sert à inclure dans le formulaire une zone de texte offrant aussi une liste de choix possibles.	Propriétés intéressantes: Name Enabled Visible Value TabIndex Text TextAlign BoundColumn ColumnCount ControlSource RowSource Événements intéressants: Change Enter Exit BeforeUpdate AfterUpdate
	Zone de liste (ListBox)	Sert à inclure dans le formulaire une zone de liste de choix possibles.	Propriétés intéressantes: Name Enabled Visible Value TabIndex Text TextAlign BoundColumn ColumnCount ControlSource RowSource Événements intéressants: Change Enter Exit BeforeUpdate AfterUpdate
	Case à cocher (CheckBox)	Sert à inclure dans le formulaire une (souvent plusieurs) case(s) à cocher	Propriétés intéressantes: Name Caption Enabled Visible Value TabIndex TextAlign ControlSource Événements intéressants: Change Enter Exit BeforeUpdate AfterUpdate
	Bouton d'option (OptionButton)	Sert à inclure dans le formulaire un (souvent plusieurs) boutons d'option	Propriétés intéressantes: Name Caption Enabled Visible Value TextAlign ControlSource Événements intéressants: Change Enter Exit BeforeUpdate AfterUpdate
	Bouton Bascule (ToggleButton)	Désuet: utiliser une case à cocher
	Cadre (Frame)	Sert à regrouper plusieurs contrôles d'un formulaire	Propriétés intéressantes: Name Caption Enabled Visible Événements intéressants: Enter Exit
	Bouton de commande (CommandButton)	Sert à inclure dans le formulaire un (souvent plusieurs) bouton(s)	Propriétés intéressantes: Name Caption Default Enabled TabIndex Visible Événements intéressants: Click
	Contrôle d'onglet (TabStrip)	Sert à inclure des onglets dans le formulaire	Propriétés intéressantes: Name Enabled Value Événements intéressants: Change
	Multi Page (Page)	Sert à inclure des pages dans le formulaire	Propriétés intéressantes: Name Enabled Value Événements intéressants: Change
	Barre de défilement (ScrollBar)	Sert à inclure une barre de défilement dans le formulaire.	Propriétés intéressantes: Name Enabled Min Max Value ControlSource Orientation Événements intéressants: Change Enter Exit BeforeUpdate AfterUpdate
	Toupie (SpinButton)	Sert à inclure des flèches de défilement	Propriétés intéressantes: Name Enabled Min Max Value ControlSource Orientation Événements intéressants: Change Enter Exit BeforeUpdate AfterUpdate
	Image	Sert à inclure une image dans le formulaire	Propriétés intéressantes: Name Enabled Picture Événements intéressants: Click
	RefEdit	Sert à inclure une zone de sélection de plage Excel dans le formulaire Remarque : si il y n'a aucun bouton RefEdit dans la boîte à outils, procédez comme suit : Dans le menu Outils , cliquez sur Autres contrôles . Dans la boîte de dialogue Contrôles supplémentaires , activez la case à cocher RefEdit.ctrl et puis cliquez sur OK.	Propriétés intéressantes: Name Enabled Value Text Événements intéressants: Change Enter Exit BeforeDragOver BeforeUpdate AfterUpdate

Ouvrir et fermer un formulaire

Pour ouvrir un formulaire, il n'y a pas d'instruction VBA. On utiliser plutôt la méthode Show (voir exemples).

Pour réafficher un formulaire déjà ouvert, parce qu'un contrôle a pu changer, ou utilise la méthode RePaint.

Pour fermer un formulaire, on peut utiliser la méthode Close (ou même Hide), mais celle-ci ne libère pas l'espace mémoire occupée par le formulaire.

On utilise plus souvent l'instruction VBA:

Unload objet Où: objet est le nom du formulaire à supprimer.

Lorsque l'instruction est utilisée dans le module VBA appartenant au formulaire, on préfère  utiliser:
Unload Me
L'objet Me étant le formulaire en cours.

Exemples

Il est fortement déconseillé d'utiliser quelque forme de dialogue que ce soit dans une fonction (Function) VBA.
Une fonction ne devrait communiquer avec l'environnement et l'utilisateur que par ses paramètres (en entrée) et sa valeur (en sortie).

Les exemples ci-dessous ne sont donc que des procédures Sub.

Exemple 1: Écran d'accueil

Technique 1: utilisant une feuille Excel

Cet exemple requiert:

Une feuille Excel nommée Accueil contenant le texte à afficher:


 

• Double-cliquant sur ThisWorbook (fenêtre de projet de l'éditeur VBA)
• Choisissant WorkBook dans la liste déroulante de gauche de la fenêtre de code
• La procédure
  
  

  Option Explicit Private Sub Workbook_Open() End Sub

  apparaît alors.

  Complétez la ainsi:

  Option Explicit Private Sub Workbook_Open() 'Auteur Michel Berthiaume 'Afficher une feuille Excel de présentation de l'application Dim dtDate As Date     If Sheets("Accueil").Range("B1").Value = False Then 'Si l'utilisateur n'a pas coché         Sheets("Accueil").Visible = True         Sheets("Accueil").Select         dtDate = Now()         Do Until Now() - dtDate > (1 / 24 / 60 / 60 * 3) 'Boucle d'attente de 3 secondes             DoEvents 'Permet à l'utilisateur de cocher la case         Loop     End If     Sheets("Accueil").Visible = False End Sub

  Technique 2: utilisant un formulaire

  Cet exemple requiert:

  Un formulaire VBA Excel (Microsoft Forms) nommé frmDocumentation contenant le texte à afficher:
  

  dont la propriété

  • Caption : VBA - Introduction

  Contenant les contrôles (objets) suivants:

• Une image
• Une case à cocher (CheckBox):
  • Name: cbMessage
  • Caption: Ne plus afficher ce message
  • ControlSource: Accueil!B1
  • La procédure événementielle Sub Workbook_Open():
    • Double-cliquez sur ThisWorbook (fenêtre de projet de l'éditeur VBA)
    • Sélectionnez WorkBook dans la liste déroulante de gauche de la fenêtre de code
    • La procédure

      Option Explicit Private Sub Workbook_Open() End Sub

      apparaît alors.

    • Complétez la ainsi:

  Option Explicit Private Sub Workbook_Open() 'Auteur Michel Berthiaume 'Afficher un formulaire VBA Excel (Microsoft Forms) de présentation de l'application     frmDocumentation.show End Sub

  La procédure événementielle UserForm_Activate():
  • Affichez le fomulaire frmDocumentation
  • Basculez en mode Code (bouton dans la fenêtre Projet, Menu Affichage/Code ou F7)
  • Sélectionnez UserForm dans la liste déroulante de gauche de la fenêtre de code
  • Sélectionnez Activate dans la liste déroulante de droite de la fenêtre de code
  • La procédure

    Option Explicit Private Sub UserForm_Activate() End Sub

            apparaît alors.

    • Complétez la ainsi:

  Option Explicit Private Sub UserForm_Activate() 'Auteur: Michel Berthiaume 'Accueil dans un classeur Excel Dim dtDate As Date     If Me.cbMessage.Value = False Then 'Seulement si la case n'est pas cochée         Me.Repaint 'Afficher le formulaire         dtDate = Now()         Do Until (Now() - dtDate) > (1 / 24 / 60 / 60 * 3) 'Boucle d'attente de 3 secondes             DoEvents 'Permet à l'utilisateur de cocher la case         Loop     End If     Unload Me 'Effacer le formulaire End Sub

  Exemple 2: Sélectionner un dossier

  L'exemple ci-dessous crée une liste de fichiers d'un dossier choisi par l'utilisateur.

  Il requiert une feuille Excel contenant des plages nommées:

  • NomDossier, qui contiendra le chemin du dossier choisi
  • ListeFichiers qui est la cellule de gauche du titre de la plage qui contiendra la liste.

  Sub ListeFichiers() 'Auteur: Michel Berthiaume 'Créer une feuille Excel contenant la liste des fichiers d'un dossier choisi par l'utilisateur Dim FSO As New Scripting.FileSystemObject 'Requiert une référence à Microsoft Scripting Runtime Dim fsFichier As File Dim lLigne As Long     'Obtenir le nom du dossier     With Application.FileDialog(msoFileDialogFolderPicker)         .show 'Afficher la boîte de dialogue         If .SelectedItems.Count <> 1 Then 'Opération annulée             Range("NomDossier").Value = ""             Exit Sub          End If         Range("NomDossier").Value = .SelectedItems(1) 'Inscrire dans la feuille     End With     'Inscrire les fichiers     With Range("ListeFichiers")         'Effacer les données présentes         .Range(Cells(2, 1), Cells(.CurrentRegion.Rows.Count- 1, _             .CurrentRegion.Rows.Count)).Clear         lLigne = 1 'Offset initial         For Each fsFichier In FSO.GetFolder(Range("NomDossier").Value).Files             .Offset(lLigne, 0).Value = fsFichier.Name             .Offset(lLigne, 1).Value = fsFichier.Size             .Offset(lLigne, 2).Value = fsFichier.DateLastModified             lLigne = lLigne + 1         Next     End With End Sub

  Commentaires:

  • Notez l'utilisation de l'objet FileSystemObject de la librairie Scripting pour parcourir le dossier choisi.

  Exemple 3: Barre de progression

  L'exemple précédent peut être long à exécuter, si le dossier choisi contient beaucoup de fichiers.
  Ajoutons une barre de progression.

  Technique 1: En utilisant la ligne d'état

  Excel permet d'afficher un message dans la ligne d'état en bas le l'écran, en utilisant la propriété StatusBar.

  Un programme peut appeler la procédure suivante pour afficher un % de progression. Il suffit qu'il rappelle la procédure à chaque fois que le % est modifié.

  Sub AfficheProgression(PourcentageEffectué) 'Affiche un pourcentage et une barre de progression dans la barre d'état Excel 'PourcentageEffectué doit être entre 0 et 1 (inclus) 'Si 0, négatif ou >1, efface barre d'état 'Par Michel Berthiaume 'Inspiré du programme de Dick Kusleika     If PourcentageEffectué > 0 And PourcentageEffectué <= 1 Then         Application.StatusBar = Format(PourcentageEffectué, "0%") & " " & String(PourcentageEffectué * 50, Chr(31))     Else         Application.StatusBar = False     End If End Sub

  Commentaires:

  • Cette procédure requiert un paramètre qui a 2 utilisations:
    • entre 0 et 1, il indique le % de progression à afficher
    • négatif, nul ou >1, il efface le contenu de StatusBar.
  • La fonction VBA String() construit la barre de progression
  • La fonction Chr(31) affiche le caractère , qui sert à construire la barre de progression.

  On modifie donc le programme de l'exemple précédent ainsi:

  Sub ListeFichiers() 'Auteur: Michel Berthiaume 'Créer une feuille Excel contenant la liste des fichiers d'un dossier choisi par l'utilisateur DimFSO As New Scripting.FileSystemObject 'Requiert une référence à Microsoft Scripting Runtime Dim fsFichier As File Dim lLigne As Long Dim lNombre As Long 'Nombre de fichiers     'Obtenir le nom du dossier     With Application.FileDialog(msoFileDialogFolderPicker)         .show 'Afficher la boîte de dialogue         If .SelectedItems.Count <> 1 Then 'Opération annulée             Range("NomDossier").Value = ""             Exit Sub          End If         Range("NomDossier").Value = .SelectedItems(1) 'Inscrire dans la feuille     End With     lNombre = FSO.GetFolder(Range("NomDossier").Value).Files.Count     'Inscrire les fichiers     With Range("ListeFichiers")         'Effacer les données présentes         .Range(Cells(2, 1), Cells(.CurrentRegion.Rows.Count- 1, _             .CurrentRegion.Rows.Count)).Clear         lLigne = 1 'Offset initial         For Each fsFichier In FSO.GetFolder(Range("NomDossier").Value).Files             Call AfficheProgression(lLigne / lNombre) 'Afficher progression             .Offset(lLigne, 0).Value = fsFichier.Name             .Offset(lLigne, 1).Value = fsFichier.Size             .Offset(lLigne, 2).Value = fsFichier.DateLastModified             lLigne = lLigne + 1         Next     End With       Call AfficheProgression(0) 'Effacer la barre de progression End Sub Sub AfficheProgression(PourcentageEffectué) 'Affiche un pourcentage et une barre de progression dans la barre d'état Excel 'PourcentageEffectué doit être entre 0 et 1 (inclus) 'Si 0, négatif ou >1, efface barre d'état 'Par Michel Berthiaume 'Inspiré du programme de Dick Kusleika     If PourcentageEffectué > 0 And PourcentageEffectué <= 1 Then         Application.StatusBar = Format(PourcentageEffectué, "0%") & " " & String(PourcentageEffectué * 50, Chr(31))     Else         Application.StatusBar = False     End If End Sub

  Technique 2: En utilisant un formulaire

  Cet technique requiert:

  • Un formulaire VBA Excel (Microsoft Forms) nommé frmListeFichiers:
    

    dont la propriété

    • Caption : Progression

    Contenant les contrôles (objets) suivants:

  • Une étiquette (Label):
    • Caption: Produire une liste des fichiers du dossier:
  • Une zone de texte (TextBox):
    • Name: tDossier
  • Un cadre (Frame):
    • Name: FrameProgress
    • Caption: 0%

      contenant:

      • Une étiquette (Label):
        • Name: LabelProgress
        • BackColor: &H00C0C000&
  • Trois boutons (CommandButton):
    • Name: bChoisirDossier, Caption: ...
    • Name: bGo, Caption: Go
    • Name: bQuitter, Caption: Quitter

  Il faut associer au formulaire (et à ses contrôles) le code VBA suivant:

  Option Explicit 'Auteur: Michel Berthiaume 'Inspiré du travail de John Walkenbach 'Formulaire de production de liste de fichiers Private Sub UserForm_Initialize() 'Initialiser le formulaire     If tDossier.Value = "" Then         tDossier.Value = ActiveWorkbook.Path 'Dossier par défaut     End If End Sub Private Sub bChoisirDossier_Click() 'Obtenir le nom du dossier     With Application.FileDialog(msoFileDialogFolderPicker)         .Show 'Afficher la boîte de dialogue         If .SelectedItems.Count <> 1 Then 'Opération annulée             Range("NomDossier").Value = ""             Exit Sub         End If         tDossier.Value = .SelectedItems(1) 'Inscrire dans le formulaire et la feuille     End With End Sub Private Sub bGo_Click() 'Produire la liste des fichiers 'Inscrire les fichiers Dim FSO As New Scripting.FileSystemObject 'Requiert une référence à Microsoft Scripting Runtime Dim fsFichier As File Dim lLigne As Long Dim lNombre As Long 'Nombre de fichiers     On Error GoTo DossierInexistant     lNombre = FSO.GetFolder(Range("NomDossier").Value).Files.Count     On Error GoTo 0     If lNombre = 0 Then         Exit Sub     End If With Range("ListeFichiers")     'Effacer les données présentes     .Range(Cells(2, 1), Cells(.CurrentRegion.Rows.Count - 1, _         .CurrentRegion.Columns.Count)).Clear     lLigne = 1 'Offset initial     For Each fsFichier In FSO.GetFolder(Range("NomDossier").Value).Files         Call AfficheProgression(lLigne / lNombre) 'Afficher progression         .Offset(lLigne, 0).Value = fsFichier.Name         .Offset(lLigne, 1).Value = fsFichier.Size         .Offset(lLigne, 2).Value = fsFichier.DateLastModified         lLigne = lLigne + 1     Next End With End Sub DossierInexistant:     MsgBox "Dossier inexistant", , "Liste de fichiers d'un dossier" End Sub Private Sub bQuitter_Click()     Unload Me End Sub Sub AfficheProgression(PourcentageEffectue)     FrameProgress.Caption = Format(PourcentageEffectue, "0%")     LabelProgress.Width = PourcentageEffectue * (FrameProgress.Width - 10)     Me.Repaint End Sub

  Finalement, ajouter la procédure:

  Sub OuvrirfrmListeFichiers()
      FrmListeFichiers.Show
  End Sub

  dans un module.

  Lorsque exécutée, cette dernière procédure exécutera le formulaire.

  Exemple 4: Saisir et valider des données

  Voyons comment on peut utiliser Excel pour saisir les informations nécessaires à une facturation de vente.

  Technique 1: utilisant une feuille Excel

  Créez la feuille Excel suivante:

  

  Ayant les caractéristiques suivantes:

  • B3 à D5 déverrouillées (saisie du client)
  • B7 à D48 déverrouillées (saisie des ventes)
  • G2 à G6 les calculs de montant de facture
  • Plage nommée NomClient: B3
  • Plage nommée Référence: E4
  • Protection de la feuille activée

  Chacun des trois boutons est associé au procédures correspondantes:

  Sub Initial() 'Auteur: Michel Berthiaume 'Initialiser ou réinitialiser la feuille facture 'l'action est déterminée par le contenu du bouton: Démarrer la saisie ou Terminer la saisie 'Configuré pour Excel 2007 Dim sBouton As Shape     'Trouver le bouton     For Each sBouton In ActiveSheet.Shapes 'Examiner chaque bouton         If sBouton.AlternativeText = "Démarrer la saisie" Then             'Initialiser             With ActiveWindow 'Déactive certains affichages                 .DisplayHeadings = False                 .DisplayHorizontalScrollBar = False                 .DisplayVerticalScrollBar = False                 .DisplayWorkbookTabs = False             End With             With Application                 .DisplayFormulaBar = False                 .DisplayStatusBar = False             End With                 'Modifier le bouton             Worksheets("Facture").Unprotect             sBouton.AlternativeText = "Terminer la saisie"             sBouton.Select                           'Impossible de modifier l'étiquette directement             Selection.Characters.Text = "Terminer la saisie"             Range("B3").Select             Worksheets("Facture").Protect             Exit For 'Pas besoin de regarder les autres boutons         ElseIf sBouton.AlternativeText = "Terminer la saisie" Then                 'Rétablir l'affichage             With ActiveWindow                 .DisplayHeadings = True                 .DisplayHorizontalScrollBar = True                 .DisplayVerticalScrollBar = True                 .DisplayWorkbookTabs = True             End With             With Application                 .DisplayFormulaBar = True                 .DisplayStatusBar = True             End With             'Modifier le bouton             Worksheets("Facture").Unprotect             sBouton.AlternativeText = "Démarrer la saisie"             sBouton.Select                         'Impossible de modifier l'étiquette directement             Selection.Characters.Text = "Démarrer la saisie"             Range("B3").Select             Worksheets("Facture").Protect             Exit For 'Pas besoin de regarder les autres boutons         End If     Next End Sub Sub Nouvelle() 'Auteur: Michel Berthiaume 'Prépare la feuille pour une nouvelle facture     Range("B3:D5").ClearContents     Range("B7:D16").ClearContents     Worksheets("Facture").Unprotect     Range("Référence") = Range("Référence") + 1     Worksheets("Facture").Protect     Range("B3").Select End Sub Sub Enregistrer() 'Auteur: Michel Berthiaume 'Exécuté par bouton Ok de boîte de dialogue     With Application.FileDialog(msoFileDialogSaveAs)         .InitialFileName = "Facture de " & Range("NomClient").Value & " du " & Format(Date, "Long Date") & ".xls"         .Show 'Afficher la boîte de dialogue         If .SelectedItems.Count <> 1 Then 'Opération annulée             Exit Sub         End If         ActiveWorkbook.SaveAs Filename:=.SelectedItems(1)     End With End Sub

  Technique 2: utilisant un formulaire

  Ici, les factures seront enregistrées dans une liste Excel.
  Chaque facture est identifiée par un numéro de référence (séquentiel).
  Chaque facture est décrite sur plusieurs lignes:

  • la ligne 0 identifie le client
  • Les lignes 1 à 89 correspondent aux lignes de détail de la facture (items).
  • Les lignes 91 à 99 contiennent le sous-total, les taxes et le total de la facture.

  Cette liste de factures est facilement utilisable avec les filtres, le tris et le tableau croisé dynamique d'Excel.

  Créez la feuille Excel selon le modèle ci dessous:

  

  La partie gauche contiendra la liste de facture.

  Au milieu (colonne K), le bouton qui appelle le formulaire.

  À droite, les colonnes L, M et N contiennent des valeurs qui seront utilisées par le formulaire.

  Pour que le bouton Saisir factures ouvre le formulaire:

  • Créez une procédure Sub:
     

    Sub OuvrirfrmFacture()     frmFacture.Show End Sub

  • Associez cette procédure au bouton (clic droit sur le bouton, affecter une macro...)

  Le formulaire frmFacture est conçu de la façon suivante:

  

  Le formulaire compte les zones de texte suivantes:

  Nom	Propriétés importantes	Description
  tClient	TabIndex: 0	Contiendra le nom du client
  tAdresse1	TabIndex: 1	Ligne 1 de l'adresse du client
  tAdresse2	TabIndex: 2	Ligne 2 de l'adresse du client
  tLigne	Enabled: False	Numéro de la ligne dans la facture
  tItem	TabIndex: 4	Description de l'item acheté
  tNombre	TabIndex: 5	Nombre d'items achetés
  tPrix	TabIndex: 6	Prix unitaire
  tMontant	Enabled: False	Extension (Nombre * Prix)

  Notez que les zones tLignes et tMontant, qui sont gérées par le programme, sont désactivées (Enabled: False).

  Chacune de ces zones de texte est accompagnée d'une étiquette (Lablel) qui la décrit.

  Il y a aussi les 8 étiquettes qui décrivent les montants de la facture. 4 d'entre elles sont descriptives et ont les noms assignés lors de leur création (Label15, Label16, etc.).
  Les 4 autres seront utilisées par le programme pour afficher les valeurs et se nomment: lSousTotal, lTPS, lTVQ et lTotal.

  Deux autres étiquettes, tRéférence et tDate afficheront le numéro de la facture affichée et sa date.

  Finalement, le formulaire compte 5 boutons:

  Nom	Propriétés importantes	Description
  bLignePréc	TabIndex: 8 Enabled: False Picture: Image d'un triangle	Enregistre la ligne affichée. Permet de reculer d'une ligne dans la facture.
  bLigneSuiv	TabIndex: 9 Enabled: False Picture: Image d'un triangle	Enregistre la ligne affichée. Permet de passer à la ligne suivante dans la facture
  bEnregistrer	TabIndex: 10 Caption: Enregistrer Enabled: False	Enregistre la facture.
  bAnnuler	TabIndex: 11 Caption: Annuler facture Enabled: False	Annule la facture (et les lignes enregistrées)
  bQuitter	TabIndex: 12 Caption: Quitter Enabled: True	Ferme le formulaire

  Le code VBA associé à ce formulaire peut être groupé en quelques blocs:

  D'abord la documentation:

  Option Explicit 'Auteur: Michel Berthiaume 'Formulaire d'enregistrement de factures 'Noter que le programme n'utilise aucune variable globale. 'Les données externes aux procédures sont stockées 'Soit dans le formulaire, soit dans la feuille Excel 'Ce qui les rend plus accessibles. ' 'Données critiques: 'Range("ProchaineRéférence"): numéro de la prochaine facture 'Range("RéférenceÉditée"): numéro de la facture en cours (synchrone avec la suivante) 'frmFacture.tRéférence: numéro de la facture en cours (synchrone avec la précédente) 'Range("LigneÉditée"): numéro de la ligne en cours de la feuille Excel 'frmFacture.tLigne: numéro de ligne en cours de la facture en cours

  • Ce programme ne contient aucune variable globale. Toutes les données partagées entre les différentes procédures sont stockées soit dans la feuille Excel, soit dans le formulaire. Cette façon de faire expose les données à l'utilisateur et au programmeur, ce qui rend plus aisé le suivi du déroulement du programme.
    Il faut aussi noter que c'est la fonction Excel Somme.Si() qui est utilisée (dans la feuille Excel) pour calculer le sous-total de la facture en cours. Ceci permet d'éviter du code VBA et facilite encore une fois une vérification visuelle des résultats.

  L'initialisation:

  Private Sub UserForm_Initialize() 'Initialiser le formulaire     tDate = Date     Range("RéférenceÉditée").Value = Range("ProchaineRéférence").Value     tLigne.Value = 1 'Ligne 1 de nouvelle facture     Range("LigneÉditée").Value = Range("A1").CurrentRegion.Rows.Count + 1 'Première ligne vide     Call AfficherTot End Sub

  Commentaires:

  • Cette procédure est exécutée au chargement (load) du formulaire.
  • Les champs Date, Référence et numéro de ligne sont initialisées
  • Le pointeur de ligne dans Excel est intitialisé
  • La procédure qui affiche les totaux de facture est exécutée.

  Le traitement des lignes de facture:

  Private Sub tNombre_Change()     'Recalculer montant     tMontant.Value = Val(tNombre.Value) * Val(tPrix.Value) End Sub Private Sub tPrix_Change()     'Recalculer montant     tMontant.Value = Val(tNombre.Value) * Val(tPrix.Value) End Sub Private Sub tMontant_Change()     'Activer les boutons de changement de ligne     If tMontant.Value <> 0 Then         bLigneSuiv.Enabled = True     Else         bLigneSuiv.Enabled = False     End If     If tLigne.Value = 1 Then         bLignePréc.Enabled = False     Else         bLignePréc.Enabled = True     End If End Sub Private Sub bLignePréc_Click()     If tLigne.Value = 1 Then         Beep 'On ne peut pas reculer avant la ligne 1         Exit Sub     End If     'Traiter la ligne en cours     If tMontant.Value <> 0 Then 'Enregistrer la ligne s'il y a un montant         Cells(Range("LigneÉditée").Value, 1) = tRéférence.Value         Cells(Range("LigneÉditée").Value, 2) = tDate.Value         Cells(Range("LigneÉditée").Value, 3) = tLigne.Value         Cells(Range("LigneÉditée").Value, 7) = tItem.Value         Cells(Range("LigneÉditée").Value, 8) = tPrix.Value         Cells(Range("LigneÉditée").Value, 9) = tNombre.Value         Cells(Range("LigneÉditée").Value, 10) = tMontant.Value     End If     'Passer à la ligne précédente     Range("LigneÉditée").Value = Range("LigneÉditée").Value - 1     'Édition de ligne existante     tRéférence.Value = Cells(Range("LigneÉditée").Value, 1)     tDate.Value = Cells(Range("LigneÉditée").Value, 2)     tLigne.Value = Cells(Range("LigneÉditée").Value, 3)     tItem.Value = Cells(Range("LigneÉditée").Value, 7)     tPrix.Value = Cells(Range("LigneÉditée").Value, 8)     tNombre.Value = Cells(Range("LigneÉditée").Value, 9)     Call AfficherTot     tItem.SetFocus 'Activer le 1er champ End Sub Private Sub bLigneSuiv_Click()     'Traiter la ligne en cours     If Val(tMontant.Value) = 0 Then 'Ne pas enregistrer ligne nulle         Beep         Exit Sub     Else 'enregistrer la ligne         Cells(Range("LigneÉditée").Value, 1) = tRéférence.Value         Cells(Range("LigneÉditée").Value, 2) = tDate.Value         Cells(Range("LigneÉditée").Value, 3) = tLigne.Value         Cells(Range("LigneÉditée").Value, 7) = tItem.Value         Cells(Range("LigneÉditée").Value, 8) = tPrix.Value         Cells(Range("LigneÉditée").Value, 9) = tNombre.Value         Cells(Range("LigneÉditée").Value, 10) = tMontant.Value     End If     'Passer à la ligne suivante     Range("LigneÉditée").Value = Range("LigneÉditée").Value + 1     If Range("LigneÉditée").Value > 899 Then 'Pas plus de 899 lignes par facture         Range("LigneÉditée").Value = 899     End If     'Initialiser les champs     If Cells(Range("LigneÉditée").Value, 1) <> "" Then         'Édition de ligne existante         tRéférence.Value = Cells(Range("LigneÉditée").Value, 1)         tDate.Value = Cells(Range("LigneÉditée").Value, 2)         tLigne.Value = Cells(Range("LigneÉditée").Value, 3)         tItem.Value = Cells(Range("LigneÉditée").Value, 7)         tPrix.Value = Cells(Range("LigneÉditée").Value, 8)         tNombre.Value = Cells(Range("LigneÉditée").Value, 9)         Call AfficherTot     Else         Call NouvelleLigne     End If     tItem.SetFocus 'Activer le 1er champ End Sub

  Commentaires:

  • Les 2 premières procédures sont déclenchées par des changement au contenu des textbox tNombre ou tPrix et assurent le recalcul des montants lorsque le nombre ou le prix de l'item sont modifiés.
  • La suivante (tMontant_Change()) est déclenchée par une ou l'autre des deux précédentes, et active ou désactive les boutons appropriés. Les deux dernières sont déclenchées par les boutons de déplacement entre les lignes de la facture et assurent:
    • l'enregistrement de la ligne affichée
    • le passage à la ligne précédente ou suivante, selon le cas.
  • Notez l'utilisation de SetFocus pour modifier l'ordre de tabulation des contrôles.

  Le traitement de facture:

  Private Sub bEnregistrer_Click() 'Enregistrer la facture Dim lLigne As Long     If Val(tMontant.Value) <> 0 Then 'Enregistrer ligne en cours au besoin         bLigneSuiv_Click     End If     lLigne = Range("LigneÉditée").Value = Range("LigneÉditée").Value     'Information client     Cells(lLigne, 1).Value = tRéférence.Value     Cells(lLigne, 2).Value = tDate.Value     Cells(lLigne, 3).Value = 0     Cells(lLigne, 4).Value = tClient.Value     Cells(lLigne, 5).Value = tAdresse1.Value     Cells(lLigne, 6).Value = tAdresse2.Value     'Sous-total     lLigne = lLigne + 1     Cells(lLigne, 1).Value = tRéférence.Value     Cells(lLigne, 2).Value = tDate.Value     Cells(lLigne, 3).Value = 900     Cells(lLigne, 7).Value = "Sous-total"     Cells(lLigne, 10).Value = Range("SousTotal").Value     'TPS     lLigne = lLigne + 1     Cells(lLigne, 1).Value = tRéférence.Value     Cells(lLigne, 2).Value = tDate.Value     Cells(lLigne, 3).Value = 901     Cells(lLigne, 7).Value = "TPS"     Cells(lLigne, 10).Value = Round(Range("TPS").Value, 2)     'TVQ     lLigne = lLigne + 1     Cells(lLigne, 1).Value = tRéférence.Value     Cells(lLigne, 2).Value = tDate.Value     Cells(lLigne, 3).Value = 902     Cells(lLigne, 7).Value = "TVQ"     Cells(lLigne, 10).Value = Round(Range("TVQ").Value, 2)     'Total     lLigne = lLigne + 1     Cells(lLigne, 1).Value = tRéférence.Value     Cells(lLigne, 2).Value = tDate.Value     Cells(lLigne, 3).Value = 999     Cells(lLigne, 7).Value = "Total"     Cells(lLigne, 10).Value = Round(Range("Total").Value, 2)     'Prochaine facture     Range("RéférenceÉditée").Value = Range("ProchaineRéférence").Value     'Prochaine ligne     tLigne.Value = 0     Call NouvelleLigne 'Sera ligne 1     bAnnuler.Enabled = False     bEnregistrer.Enabled = False End Sub Private Sub bAnnuler_Click()     If MsgBox("Abandonner la facture en cours?", vbOKCancel, "Annuler facture") = vbOK Then         Call annuler     End If End Sub Private Sub bQuitter_Click()     If Range("Total").Value <> 0 Then         If MsgBox("Abandonner la facture en cours?", vbOKCancel, "Quitter sans enregistrer?") = vbOK Then             Call annuler             Unload Me         End If     Else         Unload Me     End If End Sub

  Commentaires:

  • Ces procédures correspondent aux trois boutons de traitement de facture, Enregistrer, Annuler et Quitter.
  • Ces boutons ne sont actifs que si le contexte le permet (géré par la procédure tMontant_Change()
  • Le bouton Enregistrer déclenche:
    • l'enregistrement de la ligne en cours (en utilisant la procédure bLigneSuiv_Click)
    • l'ajout de 5 lignes à la liste de données pour stocker l'information de la facture
    • l'initialisation de différents contrôles et boutons.
  • Le bouton Annuler efface les lignes qui ont été enregistrées pour la facture en cours
  • Le bouton Quitter
    • s'assure d'enregistrer (ou non) la facture en cours
    • ferme le formulaire.

  Les procédures utilitaires:

  Sub annuler() Dim lLigne As Long     If tLigne.Value = 1 Then 'Pas de facture         'Annuler ligne en cours         tLigne.Value = 0         Call NouvelleLigne         Exit Sub     End If     'Annuler les lignes de la facture annulée     For lLigne = Val(Range("LigneÉditée").Value) - 1 To 1 Step -1         If Cells(lLigne, 1).Value <> Val(tRéférence.Value) Then             Exit For 'Terminé         Else             Range(Cells(lLigne, 1), Cells(lLigne, 10)).ClearContents         End If     Next     'Annuler ligne en cours     tLigne.Value = 0     Call NouvelleLigne     'Annuler entête     tClient.Value = ""     tAdresse1.Value = ""     tAdresse2.Value = "" End Sub Sub NouvelleLigne()     tLigne.Value = tLigne.Value + 1     tItem.Value = ""     tPrix.Value = ""     tNombre.Value = ""     Call AfficherTot End Sub Sub AfficherTot() 'Afficher les totaux de facture 'et activer les boutons appropriés.     Calculate 'S'assurer d'avoir les bonnes valeurs     lSousTotal.Caption = Range("SousTotal").Text     lTPS.Caption = Range("TPS").Text     lTVQ.Caption = Range("TVQ").Text     lTotal.Caption = Range("Total").Text     If Range("Total").Value > 0 Then         bAnnuler.Enabled = True         bEnregistrer.Enabled = True     Else         bAnnuler.Enabled = False         bEnregistrer.Enabled = False     End If End Sub

  Commentaires: Ces procédures contiennent des opérations qui sont utilisées par plusieurs autres procédures du formulaire.
  On préfère regrouper ces opérations plutôt que les copier dans les différentes procédures qui les utilisent.

  • Annuler() est utilisée par bAnnuler_Click() et bQuitter_Click()
  • NouvelleLigne() est utilisée par Annuler() et bLigneSuiv_Click()
  • AfficherTot() est utilisée par UserForm_Initialize(), bLignePréc_Click(), bLigneSuiv_Click() et NouvelleLigne().

  
  

  
  
  

   

  Abonnez-vous à l'infolettre Abonnez-vous à l'infolettre du site pour recevoir les dernières nouvelles et aussi des formations à votre courriel. Vous pouvez voir la liste des dernières infolettres ici.

  Cliquez ici pour vous abonner à l'infolettre

  
  
  
  
  Ce site est hébergé par 1&1.com