Discussion :

[linkcr15]

Bonjour à tous!

J'ai protégé ma feuille et je souhaite faire ajouter une liste déroulante dans une cellule. Voici mon code :

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
Sheets("Compte rendu").Rows(compteur2).Insert
                Sheets("Compte rendu").Unprotect
                Sheets("Compte rendu").Range("A" & compteur2 & ": I" & compteur2).Locked = False
                Sheets("Compte rendu").Cells(compteur2, 1).Interior.Color = RGB(0, 0, 0)
                Sheets("Compte rendu").Cells(compteur2, 2).Interior.Color = RGB(0, 0, 0)
                Sheets("Compte rendu").Cells(compteur2, 3) = Usr_Ajouter.Txt_Nature.Text
                Sheets("Compte rendu").Cells(compteur2, 3).Interior.ColorIndex = xlNone
                Sheets("Compte rendu").Unprotect
                Sheets("Compte rendu").Range("D" & compteur2).Select
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="Fonctionnement interne;Ressources humaines;Suivi client;Système QSE"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With

Cependant, le code plante à cette ligne :

Code :

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

 Sheets("Compte rendu").Range("D" & compteur2).Select

Je ne comprends pas car c'est un simple select... Et en plus la feuille n'est pas protégé...

Est-ce que quelqu'un peut m'aider à mettre cette liste déroulante dans cette cellule svp?

Merci d'avance pour vos réponses!

[jackborogar]

Bonjour,

Je me souviens de ce fichier
Déjà tu as deux fois Unprotected je pense que se n'est pas utile! As tu essayé de compiler pas à pas? Si non, fait le et regarde la valeur de ton compteur2 (en mettant ton curseur sur le nom curseur2").
J'ai bien peur qu'elle aille chercher une valeur "Vide"

[linkcr15]

Je me souviens de ce fichier

Oui ça fait une semaine que je suis dessus et je suis pas prêt d'avoir finit avec les galères que j'ai

Le Unprotect était là pour me rassurer que ma feuille n'était plus protégé, je sais qu'il n'est pas utile ^^

Ensuite, le problème est plus grave qu'un simple Select, J'ai éssayé ce code :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
With Sheets("Compte rendu").Range("D" & compteur2).Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="Fonctionnement interne;Ressources humaines;Suivi client;Système QSE"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With

Et là c'est le drame : Erreur d'exécution -2147417848 (80010108) Erreur Automation
L'objet invoqué s'est déconnecté de ses clients.

Il semblerait que cela provient d'une erreur ActiveX, mais je ne sais pas la résoudre (la ligne plante sur le .Add Type...)

[Philippe Tulliez]

Bonjour,
L'utilisation de SELECT est à proscrire pour deux raisons. Cela ralenti l'exécution du programme et de plus c'est parfaitement inutile. On peut parfaitement modifier la propriété d'une cellule sans la sélectionner.
Exemple à adapter selon tes besoins Ici la cellule qui contient la validation de données se trouve en cellule E2 de la même feuille

Code :

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

1
2
3
4
5
6
7
8
9
 Dim sht As Worksheet
 Set sht = ThisWorkbook.Worksheets("Compte rendu")
 Dim rng As Range: Set rng = sht.Range("E2")
 Dim NewAddress As String
 NewAddress = "$A$2:$A$7"
 With rng.Validation
 .Delete
 .Add Type:=xlValidateList, Formula1:="=" & NewAddress
 End With

Petite question, pourquoi faire un INSERT d'une ligne plutôt que d'ajouter tout simplement une ligne à la fin de la liste ?

[linkcr15]

Je sais pour les Select, ce code vient de l'enregistreur de macro, j'essaie de la faire fonctionner avant de l'optimiser ^^

corona, est-il possible d'ajouter manuellement les données de la liste déroulante?

Je vais essayer ton code en attendant, merci.

EDIT : corona, il y a une erreur dans ton code.
Impossible de réaliser la ligne avec .Add (la méthode Add de l'objet Validation à échoué)

Petite question, pourquoi faire un INSERT d'une ligne plutôt que d'ajouter tout simplement une ligne à la fin de la liste ?

J'ai des For et des If au dessus, je cherche le positionnement et j'insére une ligne dans un tableau que j'essaie ensuite de remplir avec notamment pour une cellule, une liste déroulante, voila le pourquoi du INSERT ^^

[Philippe Tulliez]

Bonjour,

corona, est-il possible d'ajouter manuellement les données de la liste déroulante?

Oui, bien sûr, si tu transformes ta plage en tableau, les ajouts seront pris en compte automatiquement par la validation de données.

EDIT : corona, il y a une erreur dans ton code.
Impossible de réaliser la ligne avec .Add (la méthode Add de l'objet Validation à échoué)

C'est curieux, parce-que je teste toujours les codes avant de les publier sur le forum.

[linkcr15]

J'en doutes pas, mais quelque chose ne fonctionne pas chez moi...
Voici mon code :

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
                Dim sht As Worksheet
                Set sht = ThisWorkbook.Worksheets("Compte rendu")
                Dim rng As Range: Set rng = sht.Range("D" & compteur2)
                Dim NewAddress As String
                NewAddress = "$J$4:$J$7"
                With rng.Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:="=" & NewAddress
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = "Interdit"
                    .InputMessage = ""
                    .ErrorMessage = _
                    "Veuillez sélectionner une valeur dans la liste déroulante de la cellule."
                    .ShowInput = True
                    .ShowError = True
                End With

Et il bloque sur le .Add

[Philippe Tulliez]

Bonjour,
A la relecture de ton premier post, je constate que tu as placé en constante la liste de la Validation de données alors que moi je fais référence à un range.
Mon test a été lancé alors que la feuille active était la même feuille que celle où se trouve la liste.
Qu'elle est la valeur de la variable compteur2 ?

[linkcr15]

compteur2 change justement en fonction des choses déjà ajouté dans un tableau.
J'ai compris ton code et le soucis ne vient pas de là.
Le placement en constante faisait partie de l'enregistreur de macro, j'ai repris ton code entièrement ^^
Par contre ma feuille active n'est pas la même que la feuille où se trouve les données ainsi que la feuille où j'insère les données, est-ce que l'erreur pourrait venir de là?

[Philippe Tulliez]

Bonjour,
Non l'erreur ne vient pas de là.
Je viens de faire un autre test avec une boucle et en ayant une autre feuille active que celle où j'écris et je n'ai pas de soucis. La seule différence c'est que mon test d'écriture se fait sur une feuille nommée "Feuil1"
En résumé, sur Feuille1 en J$$4:$J$7 se trouve la liste et je place une Validation de données dans les cellules $D$2:$D$10 et je lance la procédure alors qu'une autre feuille du classeur est active.
Quelle version d'Excel as-tu ?

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
Sub Autretest()
 Dim sht As Worksheet
 Dim compteur2 As Long
 Set sht = ThisWorkbook.Worksheets("Feuil1")
 Dim rng As Range:
 Dim NewAddress As String
 NewAddress = "$J$4:$J$7"
 
 For compteur2 = 2 To 10
  Set rng = sht.Range("D" & compteur2)
  With rng.Validation
  .Delete
  .Add Type:=xlValidateList, Formula1:="=" & NewAddress
  .IgnoreBlank = True
  .InCellDropdown = True
  .InputTitle = ""
  .ErrorTitle = "Interdit"
  .InputMessage = ""
  .ErrorMessage = _
  "Veuillez sélectionner une valeur dans la liste déroulante de la cellule."
  .ShowInput = True
  .ShowError = True
  End With
 Next
End Sub

[linkcr15]

Je suis sous Excel 2003 (11.5612.5606)

C'est bien ce que j'ai compris du code, et j'ai le même mais cela ne fonctionne toujours pas.
Et du côté de la protection des feuilles, est-ce que cela pourrait influencer la création de la liste déroulante? (Pourtant ma cellule n'est pas Locked et ma feuille est Unprotect, mais bon je demande toujours)

[Philippe Tulliez]

Bonjour,
Mes tests ont été effectués avec la version 2010, le problème vient peut-être de là parce-que la validation de données ne peux pas faire référence à des cellules d'une autre feuille dans les versions antérieures à 2010 sauf en nommant la plage.
Bien que dans le code rien ne le laisse paraître une référence à une autre feuille, je vais lancer ma machine 2003 et faire un test dans cette version.
[EDIT]
Test effectué dans les mêmes conditions avec Excel 2003 avec le même succès.
!!! En relisant ton premier post, je réalise que ta feuille était protégée au départ. Est-elle déprotégée lors du test ?
Je confirme que le problème vient de là évidemment. J'ai protégé la feuille et lancer la procédure et j'ai le même message d'erreur.
Il faut donc enlever la protection de la feuille dans la procédure.

[linkcr15]

Ma feuille est bien déprotégée (j'avais même Unprotect plusieurs fois pour être bien sûr) et mes cellules ne sont pas Locked.

Edit : Après mes test, j'en tire la même conclusion que toi. Je réalise l'enregistreur de macro avec la feuille déverouillée, aucun soucis.
Je relance ma macro avec feuille déverrouillée, là encore pas de problème mais quand je la reprotège, là j'ai la même erreur que dans mon code.

Cependant toute la protection est enlevée dans mon programme alors comment faire plus?

[Philippe Tulliez]

Bonjour,

Ma feuille est bien déprotégée (j'avais même Unprotect plusieurs fois pour être bien sûr) et mes cellules ne sont pas Locked.
Edit : Après mes test, j'en tire la même conclusion que toi. Je réalise l'enregistreur de macro avec la feuille déverouillée, aucun soucis.
Je relance ma macro avec feuille déverrouillée, là encore pas de problème mais quand je la reprotège, là j'ai la même erreur que dans mon code.
Cependant toute la protection est enlevée dans mon programme alors comment faire plus?

As-tu lu jusqu'au bout ce que j'ai écris ?

Il faut donc enlever la protection de la feuille dans la procédure.

Ce qui veut dire qu'il faut placer l'instruction Unprotect en début de procédure et ne pas oublié de protéger à nouveau la feuille en fin de procédure.
Tu peux aussi protéger la feuille par code VBA en autorisant l'écriture par code VBA

Code :

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

 Worksheets("FileName").protect "toto", userinterfaceonly:=True

[linkcr15]

J'ai enfin réussi à trouver un code qui fonctionne! Je ne comprends pas la différence avec avant mais bon....
Voici mon code :

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
Dim NewAddress As String
NewAddress = "J1:J4"
Sheets("Compte rendu").Activate
Sheets("Compte rendu").Unprotect
Range("D" & compteur2).Select
With Selection.Validation
           .Delete
           .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
           xlBetween, Formula1:="=" & NewAddress
           .IgnoreBlank = True
           .InCellDropdown = True
           .InputTitle = ""
           .ErrorTitle = "Interdit"
           .InputMessage = ""
           .ErrorMessage = _
           "Veuillez sélectionner une valeur dans la liste déroulante de la cellule."
           .ShowInput = True
           .ShowError = True
End With

Merci à tous ceux qui m'ont aidé et notamment à corona qui n'a pas lâché l'affaire