USERFORM - CRUD
Ou comment créer, consulter, modifier et supprimer des enregistrements à l'aide d'un formulaire.
L’utilisation d’un formulaire pour créer, éditer, supprimer, et ajouter un enregistrement est un sujet régulièrement évoqué sur les forums.
Cette contribution a pour but de montrer une solution parmi d’autres de la manière de construire d’une façon structurée un formulaire CRUD dont l’acronyme signifie (Create, Read, Update, Delete).
Il est possible bien entendu de réaliser un formulaire totalement dynamique mais j’ai voulu faire simple avec un minimum de contrôle et peu de données.
Convention de nommage
Pour nommer mes contrôles, j’utilise les préfixes suivants :
frm pour Frame
cmd pour CommandButton
txt pour TextBox
cbo pour ComboBox
lbl pour Label
opt pour OptionButton
usf pour UserForm
Les données
Colonne A - Ref qui est un nombre avec un format personnalisé R000
Colonne B - Nom
Colonne C - Prénom
Colonne D - Adresse
Colonne E - Sexe
Le formulaire
Les contrôles formulaires
A l’exception des contrôles numérotés en rouge, les quatre étiquettes (label) (Nom, Prénom, Adresse et Sexe) et les Frame désignés par une lettre, le nom de tous les contrôles sont indiqués dans l’illustration ci-dessus
Les contrôles lettrés
Les contrôles désignés pas les lettres A, B et C sont des cadres (type Frame)
A – frmMember (Encadre les contrôles signalétiques des données)
B – frmButton (Encadre les boutons, Nouveau, Modification et Suppression)
C – frmNavigation (Encadre les boutons de navigation Premier, Dernier, Précédent, Suivant)
Les contrôles numérotés (encadré par le frame frmMember)
1 - txtName
2 - txtFirstName
3 - txtAddress
4 - optMale
5 - optFemale
Déclaration des variables et constantes de portées module
1
2
3
4
5
6
7
8
9
10
11
| Option Explicit
' Déclaration des variables et constantes de portées module
Enum Status ' Constantes énumérées Status
Consultation = 0: Modify = 1: NewRec = 2: Remove = 3
End Enum
Const StatusLabel As String = "Consultation;Modification;Création;Suppression"
Const appTitle As String = "Fiche de membre"
Dim UserFormStatus As Byte ' Statut du type de travail (Consultation, Modify, NewRec, Remove)
Dim CurrentRecord As Long ' Enregistrement en cours
Dim rng As Range ' Plage de données traitée par le formulaire
Dim lstStatusText() As String |
Les procédures
Les codes de certaines procédures sont affichés et commentés ici. Je renvoie vers le classeur en téléchargement pour le complément.
A l’ouverture du UserForm
1
2
3
4
5
6
7
8
9
10
11
12
13
| Private Sub UserForm_Activate()
InitVariable ' Initialisation des variables
InitData ' Initialisation adresse liste des données + RowSource
InitComboBox ' Initialisation ComboBox
UserFormStatus = Status.Consultation ' Consultation par défaut
With Me ' Initialisation de certains contrôles
.cmdConfirm.Visible = False: .cmdCancel.Visible = False
.cboMember.Enabled = True: .frmMember.Enabled = False
usfTitle ' Titre du UserForm (Propriété Caption)
End With
' Focus sur le 1er enregistrement ou l'enregistrement sélectionné dans la feuille
With Me: .cboMember.ListIndex = Me.Tag: End With
End Sub |
Lecture des enregistrements
Pour éditer, modifier et supprimer un enregistrement il faut avant tout lire l’enregistrement, nous allons donc prévoir une procédure nommée ReadRecord
1
2
3
4
5
6
7
8
9
10
11
| Private Sub ReadRecord(ByVal RecordNumber As Long)
' Lecture de l'enregistrement
RecordNumber = RecordNumber + 1
With rng
Me.txtName = .Cells(RecordNumber, 2)
Me.txtFirstName = .Cells(RecordNumber, 3)
Me.txtAddress = .Cells(RecordNumber, 4)
If UCase(.Cells(RecordNumber, 5)) = "F" Then Me.optFemale.Value = True Else Me.optMale = True
Me.frmMember.Caption = "Fiche " & Format(RecordNumber, "R000")
End With
End Sub |
Cette procédure sera appelée par la procédure événementielle cboMember_Click qui s’enclenche dès que l’on modifie la valeur de la propriété ListIndex du contrôle cboMember.
Ecriture des enregistrements
Pour ajouter et modifier un enregistrement, il faut bien entendu écrire. Nous prévoirons donc une procédure d’écriture nommée WriteRecord
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| Private Sub WriteRecord(ByVal RecordNumber As Long)
' Ecriture de l'enregistrement
Me.cboMember.ListIndex = -1
RecordNumber = RecordNumber + 1
With rng
With .Cells(RecordNumber, 1)
If Len(.Value) = 0 Then ' ID
.Value = Application.WorksheetFunction.Max(rng.Columns(1)) + 1
End If
.NumberFormat = "\R000" ' Format
End With
.Cells(RecordNumber, 2) = Me.txtName
.Cells(RecordNumber, 3) = Me.txtFirstName
.Cells(RecordNumber, 4) = Me.txtAddress
.Cells(RecordNumber, 5) = IIf(Me.optFemale = True, "F", "M")
End With
Me.cboMember.ListIndex = CurrentRecord
End Sub |
Parcourir les enregistrements
La variable CurrentRecord de portée module) a comme valeur le numéro de l’enregistrement en cours dont le premier égal 0. Ce choix a été fait pour correspondre à la valeur de la propriété ListIndex du contrôle cboMember.
On parcourt les enregistrements soit en sélectionnant un élément dans la liste (contrôle cboMember, soit en cliquant sur un des boutons (Premier, dernier, suivant, précédent) encadrés par le contrôle frmNavigation. Ces contrôles nommés cmdFirst, cmdLast, cmdPrevious, cmdNext.
Procédure événementielle cboMember_Click
1
2
3
4
5
| Private Sub cboMember_Click()
CurrentRecord = Me.cboMember.ListIndex
ReadRecord CurrentRecord ' Lecture de l'enregistrement sélectionné
CheckButton
End Sub |
Les procédures événementielles des boutons de navigations
Les procédures événementielles des boutons de navigations
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| Private Sub cmdNext_Click()
Me.cboMember.ListIndex = CurrentRecord + 1
End Sub
Private Sub cmdPrevious_Click()
Me.cboMember.ListIndex = CurrentRecord - 1
End Sub
Private Sub cmdFirst_Click()
Me.cboMember.ListIndex = 0
End Sub
Private Sub cmdLast_Click()
Me.cboMember.ListIndex = rng.Rows.Count - 1 ': Navigate
End Sub |
La procédure CheckButton
La propriété Enabled des boutons de navigations est à False lorsque la limite inférieure ou supérieure des enregistrements est atteinte.
C’est la procédure CheckButton qui gère cette propriété et celle-ci est invoquée par la procédure événementielle cboMember.
1
2
3
4
5
6
7
8
| Sub CheckButton()
With Me
.cmdFirst.Enabled = CurrentRecord > 0
.cmdPrevious.Enabled = CurrentRecord > 0
.cmdNext.Enabled = CurrentRecord <> rng.Rows.Count - 1
.cmdLast.Enabled = CurrentRecord <> rng.Rows.Count - 1
End With
End Sub |
Les actions (Créer, modifier et supprimer les enregistrements)
Par défaut, l’enregistrement sélectionné est consulté. Pour le modifier, le supprimer ou créer un nouvel enregistrement, il faut sélectionner un des boutons encadrés par le contrôle frmAction.
Voici le code des différentes procédures événementielles
Nouvel enregistrement (cmdNew)
.
1
2
3
4
5
| Private Sub cmdNew_Click()
UserFormStatus = Status.NewRec '
ClearTextBox ' Efface les valeurs des TextBox
OppositeStatus ' Inverse la valeur booléenne des boutons d’action
End Sub |
Modification (cmdModify)
1
2
3
4
| Private Sub cmdModify_Click()
UserFormStatus = Status.Modify
OppositeStatus ' Inverse la valeur booléenne des boutons d’action
End Sub |
Suppression (cmdRemove)
1
2
3
4
5
| Private Sub cmdRemove_Click()
UserFormStatus = Status.Remove: usfTitle
RemoveRecord CurrentRecord ' Supprime l'enregistrement courant
UserFormStatus = Status.Consultation: usfTitle
End Sub |
La procédure de suppression d’enregistrement RemoveRecord
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| Private Sub RemoveRecord(ByVal RecordNumber As Long)
' Suppression de l'enregistrement
' Contrainte il doit rester un enregistrement
Select Case True
Case rng.Rows.Count = 1 ' Reste 1 enregistrement
MsgBox "Vous devez laisser un enregistrement", vbInformation, "Suppression impossible"
Case MsgBox("Voulez-vous supprimer la ligne sélectionnée", _
vbCritical + vbYesNo + vbDefaultButton2, _
"Suppression de la ligne " & CurrentRecord + 1) = vbYes
RecordNumber = RecordNumber + 1
rng.Rows(RecordNumber).Delete Shift:=xlUp ' Supprime la ligne de la plage
InitData
InitRowSource
CurrentRecord = 0: Me.cboMember.ListIndex = CurrentRecord
End Select
UserFormStatus = Status.Consultation
End Sub |
OppositeStatus - Propriétés Visibles, Enabled des contrôles
Lorsque l’on clique sur un des boutons d’action, la propriété Visible ou Enabled de certains contrôles change de valeur. C’est la procédure OppositeStatus qui s’en charge.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| Sub OppositeStatus()
' Inverse la valeur booléenne des boutons d'action
' Modifie la propriété Caption du UserForm
With Me
.cboMember.Enabled = Not .cboMember.Enabled
.frmMember.Enabled = Not .frmMember.Enabled
.frmAction.Visible = Not .frmAction.Visible
.cmdCancel.Visible = Not .cmdCancel.Visible
.cmdConfirm.Visible = Not .cmdConfirm.Visible
.cmdExit.Visible = Not .cmdExit.Visible
.frmNavigation.Visible = Not .frmNavigation.Visible
If .cboMember.Enabled = True Then UserFormStatus = Status.Consultation ' Consultation
usfTitle ' Titre du UserForm
End With
End Sub |
Liens
Je renvoie vers ces tutoriels pour apprendre la manière de gérer les formulaires et les contrôles.
et cette contribution pour générer automatiquement la valeur de la propriété ColumnWidth d'un contrôle ListBox ou ComboBox en fonction de la largeur de la colonne de la feuille.
Classeur
Un classeur est disponible en téléchargement.
Tests
Malgré le soin apporté à la programmation de ces procédures et aux multiples tests réalisés, il est possible qu'il subsiste une erreur qui m'aurait échappé. N'hésitez pas à m'en faire part.
Vos remarques et réactions sont les bienvenues.
USERFORM - Créer, Consulter, Modifier et Supprimer des enregistrements à l'aide d'un formulaire.
et si celle-ci est pertinente pensez à voter 
Répondre avec citation
Partager