Philippe Tulliez

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 :

1. à 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
2. référencer les deux premières cellules dans la boîte de dialogue Validation de données (onglet [Options])
3. écrire la formule pour rendre le texte dynamique
4. écrire la ligne de code qui remplace l'ancien texte par le nouveau
5. 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

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

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