VBA - Dialogues et formulaires

Auteur: Michel Berthiaume
Reproduction interdite sans autorisation

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

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:

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:

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

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

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

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:

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:

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:

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:

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

D'abord la documentation:

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

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:

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:

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:

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

Suite: Objets et événements Excel