Création d'un menu déroulant

**juberrebi** · 31/05/2019, 14h30

Bonjour à tous,

Je souhaiterais pouvoir créer un menu déroulant via vba.
A l’aide de l’enregistreur de macro sur un autre fichier, j’ai pu arriver au code suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Oui,Non"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Seulement lorsque je le reproduis sur mon fichier, il m’affiche un message d’erreur au niveau du .Add Type : 1004: définie par l’application ou par l’objet

Pourriez vous m’aider ?

De façon plus générale, je suis bien sûr preneur d'une toute autre solution

merci beaucoup

**BENNASR** · 31/05/2019, 14h48

bonjour
essayez avec :

Code :

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

1
2
3
4
5
6
Sub testtt()
With Range("A1").Validation
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Oui,Non"
End With
End Sub

**Menhir** · 31/05/2019, 14h49

Chez moi ça fonctionne.

Le problème doit donc venir d'un élément qui n'est pas formulé dans ta demande.

**BENNASR** · 31/05/2019, 14h55

désolé j'ai oublié .delete
pour étaler la liste sur une plage :

Code :

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

1
2
3
4
5
6
7
Sub testtt()
With Range("A2:A10").Validation
.Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Oui,Non"
End With
End Sub

**juberrebi** · 31/05/2019, 15h37

Envoyé par BENNASR

désolé j'ai oublié .delete
pour étaler la liste sur une plage :

Code :

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

1
2
3
4
5
6
7
Sub testtt()
With Range("A2:A10").Validation
.Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Oui,Non"
End With
End Sub

Bonjour Bennasr,

malheureusement aucune différence ... Je pense que l'analyse de Menhir est juste et que le problème vient d'ailleurs.. Navré de mes imprécisions

**juberrebi** · 31/05/2019, 15h34

Envoyé par Menhir

Chez moi ça fonctionne.

Le problème doit donc venir d'un élément qui n'est pas formulé dans ta demande.

Bonjour Menhir

Pensant que le problème venait de là et ne voulant pas compliquer ma requête, j'avais un peu simplifié mon code, mais le voici :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
With feuille.Cells(L, num_colonne).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Oui,Non"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With

avec feuille la feuille ouverte
L : le numéro de la ligne
num_colonne : numéro de ma colonne.

feuille.Cells(L, num_colonne) semble bien être un Range puisque je parviens à lui appliquer .Value
Mais pourtant toujours ce message d'erreur sur l'objet ou l'application.

**Pierre Fauconnier** · 31/05/2019, 17h24

Salut.

Je ne comprends déjà pas trop pourquoi il faut créer une validation par macro. Normalement, tu joues dans un décor que tu as planté, et la validation fait partie de ce décor qui préexiste à l'utilisation du classeur.

Peux-tu nous dire pourquoi tu dois créer cela par code? A part un exo d'école (qui serait dès lors débile et inutile car mal pensé), je ne vois pas l'intérêt.

**juberrebi** · 31/05/2019, 17h41

Envoyé par Pierre Fauconnier

Salut.

Je ne comprends déjà pas trop pourquoi il faut créer une validation par macro. Normalement, tu joues dans un décor que tu as planté, et la validation fait partie de ce décor qui préexiste à l'utilisation du classeur.

Peux-tu nous dire pourquoi tu dois créer cela par code? A part un exo d'école, je ne vois pas l'intérêt.

Bonjour Pierre et merci de ta réponse

En fait je génère quotidiennement un fichier excel à partir d'une base Access. Je souhaite donc paramètrer mon fichier excel depuis le vba de mon Access. C'est la raison pour laquelle je veux pouvoir créer mon menu en vba. Pour qu'à l'ouverture du fichier excel exporté, il soit déjà présent sur le doc sans aucune manipulation.

**Pierre Fauconnier** · 31/05/2019, 17h43

C'est bien ce que je pensais

Pourquoi n'utilises-tu pas un modèle Excel (fichier xltx ou xltm) dans lequel tu as déjà tous les outils et où il te suffit d'injecter tes données? Ce serait mille fois plus simple que de créer un fichier Excel en partant de rien.

**juberrebi** · 31/05/2019, 17h50

Envoyé par Pierre Fauconnier

C'est bien ce que je pensais

Pourquoi n'utilises-tu pas un modèle Excel (fichier xltx ou xltm) dans lequel tu as déjà tous les outils et où il te suffit d'injecter tes données? Ce serait mille fois plus simple que de créer un fichier Excel en partant de rien.

Tu pourrais détailler ?

Parce que je pars du fichier que me fournit Access et j'effectue les modifs en vba à partir de là. Je ne vois pas trop comment partir d'une modèle ?
Par ailleurs je suis à une centaine de paramétrages sur ce fichier quotidien et il ne manque que ce menu pour boucler l'affaire, alors je me vois mal tout recommencer

**Pierre Fauconnier** · 31/05/2019, 17h54

Je ne vois pas quoi te dire de plus que ce que j'ai dit. Normalement, tu connais la structure de ton fichier Excel avant de le créer en VBA (sinon je ne vois pas bien comment tu peux le créer en VBA...). Donc, tu crées un fichier Excel avec des données bidon et tu y places ta validation, tes graphiques, tes tables de données, tes tcd... Bref, tout comme s'il était indépendant des données d'Access. Puis tu le vides des données de test, tu le sauves comme modèle (attention au changement de dossier lorsque tu choisis le type modèle).

Partant de là, dans Access, lorsque tu génères ton fichier Excel, tu le crées sur base du modèle et tu injectes tes données dedans. Dès lors, les outils que tu as mis dedans fonctionnent et le tour est joué. Ta manipulation VBA du fichier Excel consiste simplement à le créer et à y injecter les données Access. Code VBA réduit au minimum, et tu ne réinventes pas la roue par programmation.

Par exemple, ta liste de validation s'appuie sur la colonne d'une table de données (tableau structuré). Donc il te suffit de remplir cette table en Access et ta liste de validation fonctionne.

Où coinces-tu dans ce genre de démarche?

**juberrebi** · 31/05/2019, 18h04

Envoyé par Pierre Fauconnier

Je ne vois pas te dire de plus que ce que j'ai dit. Normalement, tu connais la structure de ton fichier Excel avant de le créer en VBA (sinon je ne vois pas bien comment tu peux le créer en VBA...). Donc, tu crées un fichier Excel avec des données bidon et tu y places ta validation, tes graphiques, tes tables de données, tes tcd... Bref, tout comme s'il était indépendant des données d'Access. Puis tu le vides des données de test, tu le sauves comme modèle (attention au changement de dossier lorsque tu choisis le type modèle).

Partant de là, dans Access, lorsque tu génères ton fichier Excel, tu le crées sur base du modèle et tu injectes tes données dedans. Dès lors, les outils que tu as mis dedans fonctionnent et le tour est joué. Ta manipulation VBA du fichier Excel consiste simplement à le créer et à y injecter les données Access. Code VBA réduit au minimum, et tu ne réinventes pas la roue par programmation.

Par exemple, ta liste de validation s'appuie sur la colonne d'une table de données (tableau structuré). Donc il te suffit de remplir cette table en Access et ta liste de validation fonctionne.

Où coinces-tu dans ce genre de démarche?

Je coince sur le fait que la structure de mon fichier excel est basé sur les tables que j'extrais. Il n'y a pas de fichier initial, juste un fichier, crée de rien, regroupant mes tables dans différents onglets. J'ai fait cela avec le code suivant :

Code :

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

1
2
3
4
5
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Comparaison racines J J_1", strNomFichierResultat, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Comparaison liens_personnes J J_1", strNomFichierResultat, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Comparaison L-DOCs J J_1", strNomFichierResultat, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Comparaison Res CRS J J_1", strNomFichierResultat, True

Cela me génère donc un tableur excel avec 4 onglets contenant mes données à l'adresse strNomFichierResultat et au format .XLSB

Par la suite, j'ouvre ce fichier en vba sur acces et y effectue toutes mes modifications.

De telle sorte que lorsque j'appuie sur mon bouton "Générer le tableau", Access sorte mes données dans un tableau que j'ai designé.

Mais si tu me dis qu'il est possible de faire ça hors de Access, ce qui me faciliterait la vie, je suis tout à fait preneur. J'aurais juste besoin d'explications plus fournies car je suis vraiment novice en la matière

Merci beaucoup pour ton aide en tout cas

EDIT : mon tableau de validation est une colonne supplémentaire que j'ajoute. Elle n'est donc pas liée aux données contenues dans mon Access

**Pierre Fauconnier** · 31/05/2019, 18h05

Je vais essayer de te ficeler un exemple rapide

**Pierre Fauconnier** · 31/05/2019, 18h21

Avant de te montrer le jeu Access/Modèle Excel...

Pour que tu puisses avancer si c'est le seul truc qui te pose problème. J'ai essayé le code que tu as donné dans ton message de 15h34.

N'ayant ni Feuille, ni L, ni num_Colonne à ma disposition, je l'ai essayé avec Range("a1:a10") et ça fonctionne. Tu as donc un problème sur une de ces trois variables. Il te reste à placer un point d'arrêt sur la ligne With feuille.Cells(L, num_colonne).Validation, puis à vérifier tes variables (par exemple dans la fenêtre des variables locales). Le problème ne peut être rencontré que parce qu'au moins une de ces variables pose problème... Feuille pointe-t-il bien vers une feuille de calcul valide? Quelles sont les valeurs de L et de num_colonne? Sont-elles conformes à tes attentes?

Question au passage: As-tu Option Explicit en toute première ligne de ton module VBA?

**juberrebi** · 31/05/2019, 18h25

Envoyé par Pierre Fauconnier

Avant de te montrer le jeu Access/Modèle Excel...

Pour que tu puisses avancer si c'est le seul truc qui te pose problème. J'ai essayé le code que tu as donné dans ton message de 15h34.

N'ayant ni Feuille, ni L, ni num_Colonne à ma disposition, je l'ai essayé avec Range("a1:a10") et ça fonctionne. Tu as donc un problème sur une de ces trois variables. Il te reste à placer un point d'arrêt sur la ligne With feuille.Cells(L, num_colonne).Validation, puis à vérifier tes variables (par exemple dans la fenêtre des variables locales). Le problème ne peut être rencontré que parce qu'au moins une de ces variables pose problème... Feuille pointe-t-il bien vers une feuille de calcul valide? Quelles sont les valeurs de L et de num_colonne? Sont-elles conformes à tes attentes?

Question au passage: As-tu Option Explicit en toute première ligne de ton module VBA?

On je sais que ce n'est pas le code en lui même qui pose problème malheureusement ... J'ai bien le Option Explicit en première ligne, et j'utilise à foison Feuille, L et num_colonne pour d'autres modifications ...
feuille est en fait par d'un For Each
L une variable que je fais croître de 1 en 1 dans une boucle While pour parcourir mes lignes
et num_colonne un numéro de colonne défini.
J'ai msgbox L, num_colonne et feuille.Cells(L, num_colonne).Value et tous les résultats affichés sont cohérents ...

**juberrebi** · 31/05/2019, 18h59

Envoyé par juberrebi

On je sais que ce n'est pas le code en lui même qui pose problème malheureusement ... J'ai bien le Option Explicit en première ligne, et j'utilise à foison Feuille, L et num_colonne pour d'autres modifications ...
feuille est en fait par d'un For Each
L une variable que je fais croître de 1 en 1 dans une boucle While pour parcourir mes lignes
et num_colonne un numéro de colonne défini.
J'ai msgbox L, num_colonne et feuille.Cells(L, num_colonne).Value et tous les résultats affichés sont cohérents ...

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
 
'Le fichier que j'ouvre depuis mon Access, créé lors de l'exportation de mes données est le suivant : 
Set wb = CreateObject("Excel.Application").Workbooks.Open(strNomFichierResultat & ".XLSB")
 
'...
 
'Je parcours ensuite mes onglets un par un : 
 
For Each feuille In wb.worksheets
'...
'Puis chaque colonne de ma feuille, en m'appuyant sur le nom des colonnes : 
For Each c In feuille.range("A1:AN1")
'...
'Lorsque j'attends la fin de mes colonnes, je crée une colonne "Vraie anomalie"? C'est ici que je veux une colonne avec pour réponses Oui ou Non
 
Case ""
        feuille.Cells(c.Row, c.Column + 1) = "Vraie anomalie ?"
        num_colonne_ano = c.Column + 1
        Exit For
 
'...
 
'par la suite, je parcours les lignes de mon onglet une à une avec un indice L : 
 
L = 2
    While feuille.Cells(L, 1).Value <> 0
'...
 With feuille.Cells(L, num_colonne_ano).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Oui"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
'...
L= L+1
Wend

Bon la démarche peut sembler étrange mais c'est parce que j'ai coupé tout le reste du code, ne regroupant que les éléments du problème

**Pierre Fauconnier** · 31/05/2019, 18h43

Intuitivement, je pencherais pour un problème de séparateur dans la liste... Tu pourrais essayer avec simplement "Oui" dans Formula1?

Au passage, version Excel? 32 bits, 64 bits? Version linguistique d'Excel et de Windows? Pas de paramètres régionaux ésotériques?