par , 14/03/2021 à 09h36 (1961 Affichages)
Préambule
Une question sur le forum titrée Personnaliser les messages d'erreur de validation des données, m'a inspiré ce billet.
Le contexte
Nous voulons limiter la saisie de données numériques dans une plage de cellules en utilisant des cellules nommées pour modifier dynamiquement les valeurs minium et maximum ainsi que le message de saisie afin que celui-ci corresponde à nos limites.
Le constat
S'il est très simple de remplacer les constantes des zones de texte Minimum et Maximum par la référence à des cellules nommées, en revanche, il n'est pas prévu dans l'onglet [Message de saisie] de la boîte de dialogue Validation de données de personnaliser le message en référençant une cellule. Cette limite s'applique également d'ailleurs au message d'alerte d'erreur.
Comment faire ?
Partant du principe qu'il est parfaitement inutile de codifier en VBA ce que l'on peut faire manuellement par les fonctionnalités natives d'excel, nous procéderons comme suit :
- à l'aide du "Gestionnaire de noms", nommer trois cellules (pQtMin, pQtMax et pQtText) qui contiendront les valeurs "minimum" et "maximum" et le texte variable. Ces cellules seront dans la feuille contenant l'ensemble des paramètres de notre classeur
- référencer les deux premières cellules dans la boîte de dialogue Validation de données (onglet [Options])
- écrire la formule pour rendre le texte dynamique
- écrire la ligne de code qui remplace l'ancien texte par le nouveau
- intercepter la modification dans l'une des cellules nommées qui représentent la valeur Minimum et Maximum
Le VBA n'est utilisé que pour les étapes 4 et 5 et à l'exception de la procédure événementielle, toutes les procédures doivent se trouver dans un module standard.
Les étapes
Je suppose que les étapes 1 et 2 sont connues par le lecteur, je me concentrerai donc sur les autres points
Etape 3 : écrire la formule pour rendre le texte dynamique
Comme on peut le visualiser dans l'image ci-dessous, c'est une simple concaténation. Pour rappel la cellule contenant le texte se nomme pQtText
Etape 4 : Ecrire la ligne de code qui remplace l'ancien texte par le nouveau
1 2 3
| Sub PutValidationMessage()
Range("t_Stock[Qté]").Validation.InputMessage = Range("pQtText").Value
End Sub |
Etape 5 : Intercepter la modification dans l'une des cellules nommées (pQtMin et pQtMax) qui représentent la valeur Minimum et Maximum
La procédure événementielle Worksheet_Change du module de la feuille où se trouve les paramètres invoquera la procédure PutValidationMessage si la fonction IsTargetCellCorrect renvoie True
Code de la procédure événementielle Worksheet_Change
1 2 3 4 5
| Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If IsTargetCellCorrect(Target) Then PutValidationMessage
End If
End Sub |
Code de la procédure : IsTargetCellCorrect
1 2 3 4 5
| Function IsTargetCellCorrect(oCell As Range) As Boolean
With oCell
IsTargetCellCorrect = .Address = Range("pQtMin").Address Or .Address = Range("pQtMax").Address
End With
End Function |
Conclusion
On agira, s'il le faut, de la même manière pour les messages d'erreurs
Dans ce billet, j'ai simplifié les procédures en utilisant la validation d'une seule zone (colonne Qté du tableau structuré t_Stock mais à partir du moment où l'on choisi de rendre dynamique les textes on le fera sans aucun doute pour d'autres zones présentes dans plusieurs tables structurées et réparties sur plusieurs feuilles.
On utilisera alors plutôt une table de correspondance qui regroupera l'ensemble des paramètres