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

Sommaire

Fichier exemples

Objets et événements Excel

Conseils de programmation

Liste d'instructions

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:

MsgBox(message[, boutons] [, titre])

Où:

message est le message affiché par la fonction. On peut utiliser la constante VBA vbCrLf pour insérer des changements de lignes dans un message long.
boutons Expression numérique indiquant les boutons disponibles dans la boîte de dialogue. Par défaut, il n'y a que le bouton Ok.
Valeurs possibles:
0 (vbOKOnly) Affiche le bouton OK uniquement.
1 (vbOKCancel) Affiche les boutons OK et Annuler.
2 (vbAbortRetryIgnore) Affiche les boutons Abandonner, Réessayer et Ignorer.
3 (vbYesNoCancel) Affiche les boutons Oui, Non et Annuler.
4 (vbYesNo) Affiche les boutons Oui et Non.
5 (vbRetryCancel) Affiche les boutons Réessayer et Annuler.
16 (vbCritical) Affiche l'icône Message critique.
32 (vbQuestion) Affiche l'icône Requête d'avertissement.
48 (vbExclamation) Affiche l'icône Message d'avertissement.
64 (vbInformation) Affiche l'icône Message d'information.
256 (vbDefaultButton2) Le deuxième bouton est le bouton par défaut.
512 (vbDefaultButton3) Le troisième bouton est le bouton par défaut.
768 (vbDefaultButton4) Le quatrième bouton est le bouton par défaut.
524288 (vbMsgBoxRight) Le teste est aligné à droite.
(certaines valeur exotiques sont omises)

Le premier groupe de valeurs (0 à 5) décrit le nombre et le type de boutons affichés dans la boîte de dialogue. Le deuxième groupe (16, 32, 48 et 64) décrit le style d'icône. Le troisième groupe (256 et 512) définit le bouton par défaut. Au moment d'additionner ces nombres pour obtenir la valeur finale de l'argument buttons, ne sélectionnez qu'un seul nombre dans chaque groupe.
Les constantes entre parenthèses peuvent être utilisées à la place des valeurs correspondantes et sont plus explicites.
titre titre affiché en haut de la boîte de dialogue.

La fonction MsgBox() retourne une valeur différente selon le bouton cliqué pour fermer la boîte de dialogue:

1 (vbOK) OK
2 (vbCancel) Annuler
3 (vbAbort) Abandonner
4 (vbRetry) Réessayer
5 (vbIgnore) Ignorer
6 (vbYes) Oui
7 (vbNo) Non

La boîte de dialogue ouverte par l'instruction MsgBox() est toujours modale, c'est à dire que l'exécution du programme VBA est interrompu et que le classeur Excel n'est pas accessible tante que la boîte de dialogue ne sera pas fermée par l'utilisateur.

MsgBox() permet au programme d'offrir jusqu'à 3 choix (3 boutons) à l'utilisateur.

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

InputBox(message[, titre] [, défaut])

Où:

message est le message affiché par la fonction. On peut utiliser la constante VBA vbCrLf pour insérer des changements de lignes dans un message long.
titre titre affiché en haut de la boîte de dialogue.
defaut valeur qui sera utilisée si l'utilisateur clique sur Ok sans fournir de valeur.

La boîte de dialogue ouverte par l'instruction InputBox() est toujours modale, c'est à dire que l'exécution du programme VBA est interrompu et que le classeur Excel n'est pas accessible tante que la boîte de dialogue ne sera pas fermée par l'utilisateur.

InputBox() offre à l'utilisateur une zone de texte et 2 boutons: Ok et Annuler.

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

Collection Dialogs d'objets Dialog Chaque objet Dialog correspond à une boîte de dialogue Excel. La méthode Show affiche la boîte de dialogue et retourne la réponse fournie par l'utilisateur. On peut utiliser une constante VBA pour identifier la boîte de dialogue voulue:
Dialogs(xlDialogOpen).Show	Ouvrir fichier
Dialogs(xlDialogSaveAs).Show	Enregistrer sous
Il y a plus de 250 autres boîtes de dialogues disponibles dans Excel. Voir l'aide VBA, ou utiliser l'enregistreuse de macro-commande pour identifier le nom de la boîte de dialogue que vous voulez utiliser.

Il y a aussi l'objet FileDialog:

Objet FileDialog Cet objet permet d'utiliser les boîte de dialogues de fichier standard Ouvrir et Enregistrer de Microsoft Office. Utilisez la propriété DialogType pour spécifier quelle boîte de dialogue ouvrir:
msoFileDialogFilePicker	Choisir un ou plusieurs fichiers, dont les chemins d'accès sont dans la collection FileDialogSelectedItems
msoFileDialogFolderPicker	Choisir un dossier, dont le chemins d'accès est dans la collection FileDialogSelectedItems
msoFileDialogOpen	Choisir un ou plusieurs fichiers qui peuvent être ouverts avec la méthode Execute
msoFileDialogSaveAs	Choisir un nom de fichier et un chemin d'accès, et d'enregistrer le classeur Excel sous ce nom avec la méthode Execute
Plusieurs autres propriétés de cet objet permettent de spécifier le titre, des valeurs par défaut, des filtres, etc. L'objet FileDialog a 2 méthodes: Show qui affiche la boîte de dialogue spécifiée par la propriété DialgType Execute qui exécute l'action appropriée pour les dialogues de type msoFileDialogOpen et msoFileDialogSaveAs

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

La case à cocher est liée à une cellule nommée Accueil, qui est elle-même en blanc sur fond blanc pour que le contenu ne soit pas visible à l'écran.
La procédure événementielle Sub Workbook_Open(), créée en
- 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
  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

On Error GoTo Erreur:

    If Sheets("Accueil").Range("Accueil").Value = False Then 'Si l'utilisateur n'a rien 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

    Exit Sub
Erreur:
    MsgBox "Erreur inattendue: " & Err.Description
End Sub

La feuille Accueil sera affichée lors de l'ouverture du classeur.

Technique 2: utilisant un formulaire

Note: le document VBA Excel - Créer un formulaire (User Form) d'accueil décrit les étapes de création du formulaire ci-dessous.

Cet exemple requiert:

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

dont la propriété
- Name: frmAccueil
- Caption: Accueil

Contenant les contrôles (objets) suivants:

Une image (ici une capture de la feuille d'accueil Excel de l'exemple 1)
Une case à cocher (CheckBox):
- Name: cbMessage
- Caption: Ne plus afficher ce message
- ControlSource: =Accueil

La procédure événementielle Sub Workbook_Open():
- Double-cliquez sur ThisWorbook (fenêtre de projet de l'éditeur VBA)
- Choisissez 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
frmAccueil.show

End Sub

La procédure événementielle UserForm_Activate():
- Affichez le fomulaire frmAccueil
- Basculez en mode Code (bouton dans la fenêtre Projet, Menu Affichage/Code ou )
- Choisissez UserForm dans la liste déroulante de gauche de la fenêtre de code
- Choisissez 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

On Error GoTo Erreur:
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

Exit Sub

Erreur:
MsgBox "Erreur inattendue: " & Err.Description

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

On Error GoTo Erreur:

    '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
            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(.Offset(0, 0), .Offset(.CurrentRegion.Rows.Count, _
             .CurrentRegion.Columns.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

    Exit Sub
Erreur:
    MsgBox "Erreur inattendue: " & Err.Description
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 IsNumeric(PourcentageEffectué) And _
       (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 ListeFichiers1()
'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(.Offset(0, 0), .Offset(.CurrentRegion.Rows.Count, _
             .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

    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
Exit 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("NomClient").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("NomClient").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("NomClient").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.

6 autres zones de texte servent à afficher des informations sur la facture en cours de traitement:

Nom	Propriétés importantes	Description
tFacture	Enabled: False ControlSource: NoFacture	Contiendra le numéro de facture
tSousTotal	Enabled: False ControlSource: SousTotal	Sous-total de la facture
tTPS	Enabled: False ControlSource: TPS	TPS
tTVQ	Enabled: False ControlSource: TVQ	TVQ
tTotal	Enabled: False ControlSource: Total	Total
tLigneDébutFacture	Enabled: False Visible: False	Numéro de ligne Excel où commence la facture
tPrix	TabIndex: 6	Prix unitaire
tMontant	Enabled: False	Extension (Nombre * Prix)

Il y a aussi les 4 étiquettes qui décrivent les montants de la facture.

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	TabStop: False Enabled: False Picture: Image d'un triangle	Enregistre la ligne affichée. Permet de reculer d'une ligne dans la facture.
bLigneSuiv	TabStop: False 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("NoFacture"): numéro de la facture en cours (liée à tFacture)
'Range("NoLigne"): numéro de la ligne de facture en cours (liée à tLigne)
'Range("NoLigneVide"): Numéro de la prochaine ligne Excel vide
'tLigneDébutFacture: Numéro de la ligne Excel de la 1ère ligne de la facture

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
    lDate.Caption = Date
    tLigneDébutFacture = [NoLigneVide]
    'Ajouter 1 au numéro de la dernière facture
    ActiveSheet.Unprotect 'Désactiver la protection de la feuille
    If tLigneDébutFacture = 2 Then 'Première facture!
        tFacture.Value = 1
    Else
        tFacture.Value = Cells(Val(tLigneDébutFacture.Value) - 1, 1).Value + 1
    End If
    tLigne.Value = 1 'Ligne 1 de nouvelle facture
    ActiveSheet.Protect 'Activer la protection de la feuille
    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 de la facture sont initialisés
La procédure qui affiche les totaux de facture est exécutée.

Le traitement des lignes de facture:

Private Sub tNombre_Change()
    tMontant.Value = Val(tNombre.Value) * Val(tPrix.Value) 'Déclenche Sub tMontant_Change()
End Sub

Private Sub tPrix_Change()
    tMontant.Value = Val(tNombre.Value) * Val(tPrix.Value) 'Déclenche Sub tMontant_Change()
End Sub

Private Sub tMontant_Change()
'Activer les boutons de changement de ligne
    If tMontant.Value <> 0 Then
       bLigneSuiv.Visible = True
    Else
        bLigneSuiv.Visible = False 'On ne peut pas enregistrer une ligne sans montant
    End If
End Sub

Private Sub bLignePréc_Click()
'Le formulaire n'affiche qu'une ligne de facture à la fois
'L'utilisateur peut se déplacer d'une ligne à l'autre avec des flèches
'Mais ne peut pas changer de facture.

    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
        Call EnregistrerLigne
    Else
        'Pas de montant, pas d'enregistrement
    End If

    'Passer à la ligne précédente
    tLigne.Value = tLigne.Value - 1

    'Édition de nouvelle ligne active
    Call LireLigne
    If tLigne.Value = 1 Then
        bLignePréc.Visible = False 'On ne peut pas reculer plus loin
    End If

    tItem.SetFocus 'Activer le 1er champ
End Sub

Private Sub bLigneSuiv_Click()
    'Sert à enregistrer une ligne de facture
    'Traiter la ligne en cours
    If Val(tMontant.Value) = 0 Then 'Ne pas enregistrer ligne nulle
        Beep
        Exit Sub
    ElseIf (Val(tLigne.Value) + 1) > 899 Then 'Pas plus de 899 lignes dans une facture
        Beep
        Exit Sub
    Else 'enregistrer la ligne active avant de passer à la suivante
        Call EnregistrerLigne
    End If
    If tClient.Enabled Then 'C'est la première ligne de la facture
        tClient.Enabled = False 'Il faut désactiver ces champs
        tAdresse1.Enabled = False 'Jusqu'à enregistrement ou annulation de la facture
        tAdresse2.Enabled = False
    End If

    'Initialiser les champs
    If Cells(Val(tLigneDébutFacture) + Val(tLigne), 1) <> "" Then
        'Édition de ligne existante
        tLigne.Value = tLigne.Value + 1
        Call LireLigne
    Else
        Call NouvelleLigne
    End If
    bLignePréc.Visible = True
    tItem.SetFocus 'Activer le 1er champ
End Sub

Sub EnregistrerLigne()
'Enregistrer une ligne de la facture
Dim lligne As Long
lligne = Val(tLigneDébutFacture.Value) + Val(tLigne.Value) - 1

     ActiveSheet.Unprotect
     Cells(lligne, 1) = tFacture.Value
     Cells(lligne, 2) = lDate.Caption
     Cells(lligne, 3) = tLigne.Value
     Cells(lligne, 4) = tClient.Value
     Cells(lligne, 5) = tAdresse1.Value
     Cells(lligne, 6) = tAdresse2.Value
     Cells(lligne, 7) = tItem.Value
     Cells(lligne, 8) = tPrix.Value
     Cells(lligne, 9) = tNombre.Value
     Cells(lligne, 10) = tMontant.Value
     ActiveSheet.Protect
End Sub

Sub LireLigne()
'Lire une ligne de la facture
Dim lligne As Long
     lligne = Val(tLigneDébutFacture.Value) + Val(tLigne.Value) - 1

     lRéférence.Caption = Cells(lligne, 1)
     lDate.Caption = Cells(lligne, 2)
     tClient.Value = Cells(lligne, 4)
     tAdresse1.Value = Cells(lligne, 5)
     tAdresse2.Value = Cells(lligne, 6)
     tItem.Value = Cells(lligne, 7)
     tPrix.Value = Cells(lligne, 8)
     tNombre.Value = Cells(lligne, 9)
     Call AfficherTot
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.
bLignePréc_Click() et bLigneSuiv_Click() 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.
Les 2 dernières Sub regroupent les instructions d'écriture et de lecture d'une ligne de facture.

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 = [NoLigneVide]

    ActiveSheet.Unprotect

    'Sous-total
    lLigne = lLigne + 1
    Cells(lligne, 1).Value = tFacture.Value
    Cells(lligne, 2).Value = lDate.Caption
    Cells(lligne, 3).Value = 900
    Cells(lligne, 4).Value = tClient.Value
    Cells(lligne, 5).Value = tAdresse1.Value
    Cells(lligne, 6).Value = tAdresse2.Value
    Cells(lligne, 7).Value = "Sous-total"
    Cells(lligne, 10).Value = Val(tSousTotal.Value)

    'TPS
    lligne = lligne + 1
    Cells(lligne, 1).Value = tFacture.Value
    Cells(lligne, 2).Value = lDate.Caption
    Cells(lligne, 3).Value = 901
    Cells(lligne, 4).Value = tClient.Value
    Cells(lligne, 5).Value = tAdresse1.Value
    Cells(lligne, 6).Value = tAdresse2.Value
    Cells(lligne, 7).Value = "TPS"
    Cells(lligne, 10).Value = Val(tTPS.Value)

    'TVQ
    lligne = lligne + 1
    Cells(lligne, 1).Value = tFacture.Value
    Cells(lligne, 2).Value = lDate.Caption
    Cells(lligne, 3).Value = 902
    Cells(lligne, 4).Value = tClient.Value
    Cells(lligne, 5).Value = tAdresse1.Value
    Cells(lligne, 6).Value = tAdresse2.Value
    Cells(lligne, 7).Value = "TVQ"
    Cells(lligne, 10).Value = Val(tTVQ.Value)

    'Total
    lligne = lligne + 1
    Cells(lligne, 1).Value = tFacture.Value
    Cells(lligne, 2).Value = lDate.Caption
    Cells(lligne, 3).Value = 999
    Cells(lligne, 4).Value = tClient.Value
    Cells(lligne, 5).Value = tAdresse1.Value
    Cells(lligne, 6).Value = tAdresse2.Value
    Cells(lligne, 7).Value = "Total"
    Cells(lligne, 10).Value = Val(tTotal.Value)

    ActiveSheet.Protect

    Call NouvelleFacture

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 Val(tTotal.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 4 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()
'On ne peut annuler que la facture en cours.

Dim lligne As Long

    'Annuler les lignes de la facture annulée
    ActiveSheet.Unprotect
    For lligne = ([NoLigneVide] - 1) To 1 Step -1
        If Cells(lligne, 1).Value = Val(lRéférence.Caption) Then
            Range(Cells(lligne, 1), Cells(lligne, 10)).ClearContents
        Else
            Exit For
        End If
    Next
    ActiveSheet.Protect

    'Annuler ligne en cours
    Call NouvelleFacture
End Sub

Sub NouvelleFacture()
      ActiveSheet.Unprotect
      tFacture.Value = Val(tFacture.Value) + 1
      lRéférence.Caption = tFacture.Value
      tClient.Enabled = True
      tAdresse1.Enabled = True
      tAdresse2.Enabled = True
      tLigne.Value = 0 'Pour démarrer la prochaine ligne à 1
      ActiveSheet.Protect
      Call NouvelleLigne
End Sub

Sub NouvelleLigne()
      ActiveSheet.Unprotect
      tLigne.Value = tLigne.Value + 1
      ActiveSheet.Protect
      tItem.Value = ""
      tPrix.Value = ""
      tNombre.Value = ""
      Call AfficherTot
End Sub

Sub AfficherTot()
      'Afficher les totaux de facture
      'et activer/désactiver les boutons appropriés.

      Calculate 'S'assurer que Excel a les bonnes valeurs
      tSousTotal = [SousTotal]
      tTPS.Value = [TPS]
      tTVQ.Value = [TVQ]
      tTotal.Value = [Total]

      If Val(tTotal.Value) <> 0 Then
            bAnnuler.Enabled = True
            bEnregistrer.Enabled = True
            bLigneSuiv.Visible = True
      Else
            bAnnuler.Enabled = False
            bEnregistrer.Enabled = False
            bLigneSuiv.Visible = False 'Les autres boutons sont gérés par AfficherTot
      End If
      If tLigne = 1 Then
            bLignePréc.Visible = False
      Else
            bLignePréc.Visible = True
      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()
NouvelleFacture est utilisée par Enregistrer_Click() et Annuler()
NouvelleLigne() est utilisée par Annuler() et bLigneSuiv_Click()
AfficherTot() est utilisée par UserForm_Initialize(), bLignePréc_Click(), bLigneSuiv_Click() et NouvelleLigne().

Sommaire

Les programmes VBA Excel peuvent communiquer avec l'utilisateur en utilisant:

Le classeur Excel qui contient le programme (cellules, boutons, Function...)
Les fonctions VBA MsgBox, InputBox
Les dialogues Excel (objets Dialog et FileDialog)
Un formulaire VBA

Fichier Exemples

Le fichier Excel Tutoriel Exemples Dialogues.xlsm contient les exemples présentés dans la présente section.

Suite: Objets et événements Excel