Discussion :

[jportemer]

Bonjour à tous !

Je suis en train de développer une macro VBA qui doit fonctionner de concert avec des validations de données de type liste.
Pour expliquer simplement, je paramètre une validation de données sur une cellule que l'utilisateur doit remplir et la liste déroulante associée doit adapter ses propositions en fonction de ce que l'utilisateur saisit (un peu à la manière d'une auto-complétion).



Je passe donc par un appel à l'écouteur d'évènement Worksheet_change afin de modifier dynamiquement la liste, stockée dans une autre feuille, qui est ensuite référencée par la validation de données.

Ce mécanisme fonction bien sauf dans un cas, à savoir si l'utilisateur commence à saisir dans la cellule en question et, sans clôturer sa saisie par appui sur Entrée, clique sur la flèche de la liste déroulante. Si l'utilisateur valide sa saisie puis déroule la liste dans un second temps, pas de problème. Mais dans le premier cas, la macro plante sur le premier appel à une fonction Range et indique l'erreur suivante :



J'ai trouvé de nombreuses références sur le net concernant ce problème pour des possibles résolutions mais rien de satisfaisant en lien spécifiquement avec la validation des données. Pour le moment, je n'ai réussi qu'à stopper l'exécution par une gestion d'exception On Error afin d'éviter la fenêtre de bug mais c'est un peu près tout...
Auriez-vous une solution pour prendre en charge ce cas ?

Merci par avance pour votre aide !

Jean

[joe.levrai]

Bonjour,

pour déformer une expression consacrée : pas de code, pas de chocolat

[jportemer]

pour déformer une expression consacrée : pas de code, pas de chocolat

Fair enough
Je vais essayer de compiler un code minimal reproduisant le problème.

[MarcelG]

Salut,

Sans VBA.
Pour ma part, je base ma liste de validation sur une colonne de tableau structuré en lui affectant la méthode Indirect.

=INDIRECT("Tableau1[Id]")

Ce processus permet une grande souplesse.
En effet, il suffit de modifier les données de la colonne du tableau structuré pour obtenir une liste de validation source modifiée automatiquement.

[Pierre Fauconnier]

Salut.


Avec la situation suivante:



J'ai aussi cette erreur dès que je cherche à modifier la liste de validation "en cours de saisie". Ca me semble normal car tu tentes de court-circuiter la validation d'Excel... Je pense qu'il n'est pas possible de réaliser ce que tu souhaites avec une liste de validation. Il faut obligatoirement valider la cellule PUIS ouvrir la liste.

Voici le code que j'ai écrit pour le test:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub PrepareList()
  Dim i As Long
  Dim j As Long
 
  On Error GoTo Catch
  If Not Range("t_Liste").ListObject.DataBodyRange Is Nothing Then Range("t_Liste").ListObject.DataBodyRange.Delete
  For i = 1 To Range("tableau2").Rows.Count
    If UCase(Range("tableau2[Prénom]")(i).Value) Like "*" & UCase(Range("e4").Value) & "*" Then
      j = Range("t_Liste").ListObject.ListRows.Add.Index
      Range("t_Liste[Liste]")(j).Value = Range("tableau2[Prénom]")(i).Value
    End If
  Next
 
Catch:
End Sub

Dans mon code, j'ai capturé l'erreur, et j'ai bien évidemment dû désactiver l'alerte pour donnée invalide. Je note aussi que ce code génère une erreur sur Application.ScreenUpdating = False lorsqu'on le met en début de la macro PrepareList, ce qui est une indication d'un télescopage entre notre macro et le moteur d'Excel.

[Pierre Fauconnier]

Dommage que tu n'es pas en XL365, car avec cette version, ça devient possible, et sans VBA

1. Utiliser FILTRE pour récupérer les données qui contiennent la saisie dans la cellule sur laquelle on a posé la liste de validation;
2. Appuyer la liste de validation sur la plage # renvoyée par FILTRE, c'est-à-dire l'adresse de la première cellule suivie de #.

On peut alors obtenir ce que tu souhaites sans plantage, même en cliquant de suite sur la flèche de la liste durant la saisie

[jportemer]

Merci pour vos réponses !
De mon côté je gère les listes de validation un peu différemment, car j'aurai besoin en production de mettre des listes de validation sur chaque ligne.
Voilà un exemple minimal qui plante, effectivement sans doute à cause d'un conflit entre le système de validation et du code permettant de remplir la liste.
Ça a l'air mal barré d'après vos retours mais je vais du coup détailler pour voir s'il y a moyen de faire autrement.

Structure du fichier Excel

• Une feuille Input contenant la validation en A2 (qui pourra ensuite être dupliquée en A3, A4, etc.)
• Une feuille Aux contenant la liste utilisée par la validation en ligne 2 (qui pourra ensuite accueillir les listes suivantes en lignes 3, 4, etc.)

Paramétrage de la validation des données
Pour le paramétrage de la liste déroulante en A2, je définis dans le Gestionnaire de nom trois formules.

Code :

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

1
2
3
p_v = Aux!$A2 'Position de la première valeur de la liste
l_v = Aux!2:2 'Ligne comportant les valeurs de la liste
f_v = DECALER(p_v;0;0;1;NBVAL(l_v)) 'La liste des valeurs sans les blancs à la fin

f_v est la formule que je définis dans la validation des données de type liste.



Macro VBA
Le but de la macro est de mettre à jour dynamiquement la liste de validation (valeurs dans Aux!2:2 donc) afin de correspondre aux premières lettres de la saisie de l'utilisateur.
Cela passe par l'écouteur d'évènement Workspace_Change sur la feuille Input.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
'Dans Feuil1 (Input)
Private Sub Worksheet_Change(ByVal target As Range)
    'Action only if the cell containing the data validation is triggered
    If target.Row = 2 And target.Column = 1 Then
        Update_based_on_input target
    End If
End Sub 'Worksheet_Change
 
Private Sub Update_based_on_input(ByVal cell As Range)
    'City list
    Const nb_cities = 5
    Dim cities(0 To nb_cities) As String
    cities(0) = "Marseille"
    cities(1) = "Meaux"
    cities(2) = "Miramas"
    cities(3) = "Paris"
    cities(4) = "Pau"
    cities(5) = "Troyes"
 
    'Search cities in list which match the start of the input
    Dim filtered_list() As String
    current_input = cell.Value
    nb_matches = 0
    For i = 0 To nb_cities
        element = cities(i)
        search_match = InStr(1, element, current_input, vbTextCompare)
        If search_match = 1 Then
            nb_matches = nb_matches + 1
            ReDim Preserve filtered_list(0 To nb_matches - 1)
            filtered_list(nb_matches - 1) = element
        End If
    Next i
 
    'Write the city list on the range for validation
    For i = 0 To nb_matches - 1
        Worksheets("Aux").Cells(2, i + 1).Value = filtered_list(i)
    Next i
 
    'Erase the other cells
    For i = nb_matches To nb_cities
        Worksheets("Aux").Cells(2, i + 1).Value = ""
    Next i
End Sub 'Update_based_on_input

L'erreur
L'erreur '50290' se produit lors de la tentative d'écriture sur la plage asservie à la liste de validation si l'utilisateur ne valide pas la saisie.


J'ai joint à ce message le fichier Excel complet présentant ce test.

Je crois avoir compris la problématique... Mais y aurait-il une solution selon vous ?
Grand merci pour votre aide !

[jportemer]

Dommage que tu n'es pas en XL365, car avec cette version, ça devient possible, et sans VBA

Je suis obligé de faire avec Excel 2016
En effet, l'idée est de partager le fichier avec des collègues qui auront la charge de le remplir et tout le monde utilise cette version...