protection, validation via VBA et presse-papier -- DrawingObjects UserInterfaceOnly

[luigiF]

Salut à vous,

Je consulte souvent le forum mais c'est la première fois que je me permets de poser une question.

je souhaite modifier les attributs de validation d'une plage via VBA sur une feuille protégée.
Ayant consulté de nombreuses discussions consacrées à ce sujet sur plusieurs sites, je sais que la protection doit être faite en VBA et inclure les paramètres UserInterfaceOnly et DrawingObjects (la nécessité de ce dernier est liée au fait que je souhaite intervenir sur la validation des cellules de ma plage).

Malgré cela, la fatidique "erreur d’exécution '1004'" survient dès que je touche à la validation de mes cellules.

La solution "brutale" serait de déprotéger/reprotéger la feuille mais elle comporte un inconvénient rédhibitoire pour ce que je veux faire : déprotéger/reprotéger vide le presse-papier et interdit donc les copier/coller à l’intérieur de la plage concernée. Il y a bien sûr des solutions pour contourner ce nouveau problème mais elles comportent également des inconvénients que je souhaite éviter.

Quelqu'un pourrait-il m'expliquer pourquoi

ActiveSheet.Protect DrawingObjects:=True, UserInterfaceOnly:=True

n'est pas suffisant pour ce que je souhaite faire ?

Le code ci-dessous fonctionne parfaitement avec les lignes de protection/de-protection (marquées par '<commenter>) mais provoque l'erreur 1004 dès que je les passe en commentaire.

Merci pour vos réponses car la question m'a occupé tout le week-end (pluvieux, il est vrai !).
Je joins un fichier simplifié pour illustrer le problème.

Luigi

Private Sub Worksheet_Activate()
ActiveSheet.Protect DrawingObjects:=True, UserInterfaceOnly:=True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Set av = Evaluate("Avalider")
Set isect = Application.Intersect(av, Target)
If Target.Count = 1 And Not isect Is Nothing Then
    Application.EnableEvents = False
    Target.Value = Target.Value & "-OK"
    Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set av = Evaluate("Avalider")
Set isect = Application.Intersect(av, Target)
If Target.Count = 1 And Not isect Is Nothing Then
    ActiveSheet.Unprotect '<commenter>
    Target.Validation.Delete  
    Target.Validation.Add Type:=xlValidateList, Formula1:="=" & Range("D1").Value
    ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True '<commenter>
End If
End Sub

 

[luigiF]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Petit Ajout ... si cela peut aider quelqu'un à m'aider ...
le lien suivant décrit exactement le même problème ...
http://www.excelforum.com/excel-programming-vba-macros/723313-problem-with-userinterfaceonly.html
et en fin de compte le MVP s'avoue "vaincu" :

I have by chance been experimenting with this method today and it does not appear to be particularly reliable, so I would personally suggest that unprotecting the sheet, making the changes and then reprotecting is probably safer in general. I'll update if I figure out why it sometimes works and sometimes doesn't.

Est-ce un "bug" connu d'Excel ?

Merci pour vos réponses,

Luigi

 

[luigiF]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Salut à vous,

Un petit "up" sur cette question qui me taraude : déprotéger/reprotéger la feuille pour éviter l'erreur 1004 a aussi pour conséquence de vider le presse-papier et me contraint de ce fait à des contorsions vraiment pénibles pour la suite de mon programme.

Vraiment personne qui ait déjà rencontré ce problème avec "UserInterfaceOnly" ?

Luigi

 

[luigiF]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Salut à vous,

Une ultime tentative de "Up" sur ce sujet qui me pose énormément de problèmes.
Je joins un fichier simplifié illustrant une situation qui semble mettre en échec le système de protection de feuille avec le paramètre "UserInterfaceOnly".

Je suis étonné que personne n'ait la moindre suggestion/piste ...

Merci,

Luigi


Private Sub Worksheet_Activate()
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set av = Evaluate("Avalider")
Set isect = Application.Intersect(av, Target)
If Not isect Is Nothing Then
ActiveSheet.Unprotect 'ce que je voudrais pouvoir enlever
Target.Validation.Delete
Target.Validation.Add Type:=xlValidateList, Formula1:="=" & Range("D1").Value
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True 'ce que je voudrais pouvoir enlever
End If
End Sub

 

[eriiic]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Bonjour,

et si tu donnais les manip effectuées qui plantent ?
On suppose qu'il faut modifier des cellules de la plage Avalider. Manifestement ce n'est pas ça ton pb puisqu'elles sont non protégées et je n'ai pas eu de soucis... (sur 2010)
Sinon une idée à tester :
- mémoriser l'état de verrouillage de la cellule
- l’ôter, modifier, le remettre.

eric

 

[luigiF]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Bonjour Eric et merci pour ta réponse.

les cellules de la plage à valider ne sont pas verrouillées (mais d'autres cellules de la feuille doivent l'être, c'est pourquoi la feuille doit être protégée). Mémoriser l'état de verrouillage de la cellule pour le restaurer ensuite ne répond donc pas à mon problème.

Rien ne plante si on exécute le code tel que je vous l'ai envoyé (avec protection/dé-protection).
On obtient par contre une erreur (1004) lorsque l'on sélectionne une cellule de la plage à valider dès que l'on supprime les étapes de protection/dé-protection.

L'inconvénient de l'option Protection/dé-protection est qu'elle vide le presse-papier et interdit donc les copier/coller à l’intérieur de la plage concernée. Il y a bien sûr des solutions pour contourner ce nouveau problème mais elles comportent également des inconvénients que je souhaite éviter.

C'est pourquoi j'aimerais utiliser l'option "UserInterfaceOnly" qui est justement faite pour cela ... mais qui est apparemment limitée (ou qui comporte un "bug").

J'espère avoir été clair dans mes explications ... je peux les détailler si nécessaire.

Merci encore,

Luigi

 

[Pierrot93]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Bonjour,

essaye peut être en placant la protection dans le module "thisworkbook" :

Private Sub Workbook_Open()
Feuil1.Protect UserInterfaceOnly:=True, DrawingObjects:=True
End Sub

semble fonctionner chez moi sous 2003... enlève toutes les autres instructions de protection / déprotection de tes modules...

bon après midi
@+

 

[luigiF]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Bonjour Pierrot93,

Merci beaucoup pour cette réponse qui semble effectivement résoudre le problème (Excel 2010) sur le fichier simplifié.
J'avais initialement mis la protection dans "thisworkbook" comme tu le proposes,mais je l'avais ensuite placée dans "Worksheet_Activate" pour ne pas avoir à fermer/ouvrir le fichier à chaque fois pendant le debuggage.

Ceci dit ... je ne comprends pourquoi le comportement de protection est différent dans les deux cas ... Si quelqu'un a une explication, je suis preneur car le fichier original comporte d'autres procédures durant lesquelles une déprotection/reprotection est nécessaire.
Je sais que l'état de UserInterfaceOnly n'est pas sauvegardé lorsque l'on ferme le fichier et qu'il faut donc le restaurer après l'ouverture, mais pourquoi cela n'est-il efficace que dans "Workbook_Open" ?

Merci encore pour l'attention que tu as portée à ma question !

Cordialement,

Luigi

 

[eriiic]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

On obtient par contre une erreur (1004) lorsque l'on sélectionne une cellule de la plage à valider dès que l'on supprime les étapes de protection/dé-protection.

C'est bien ce que j'avais compris et fait, sans avoir d'erreur.
D'où ma demande de précision...
eric

 

[Pierrot93]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Re,

Je sais que l'état de UserInterfaceOnly n'est pas sauvegardé lorsque l'on ferme le fichier et qu'il faut donc le restaurer après l'ouverture, mais pourquoi cela n'est-il efficace que dans "Workbook_Open" ?

bah... l'événement "activate" de la feuille ne se déclenche pas à l'ouverture du classeur, mais si celle ci est la feuille active....

 

[luigiF]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Bonsoir Pierrot93,

Je ne pense que ce soit l'explication car pendant que je faisais mes tests je changeais volontairement de feuille pour provoquer l'évènement "activate" avant de tester la validation sur la feuille 1.

Après quelques tests complémentaires en mettant la protection dans Workbook_Open ... ou ailleurs, il semble que le point clef soit l'état de la propriété "protectContent" au moment ou l'on manipule la validation des cellules :

Si l'on protège (dans Workbook_Open ou ailleurs) avec :

Worksheets("Feuil1").Protect UserInterfaceOnly:=True, DrawingObjects:=True, Password:="toto", contents:=True

le contenu des cellules verrouillées est protégé, mais la manipulation de la validation provoquera une erreur, même sur une cellule non verrouillée.

Si l'on protège avec : Worksheets("Feuil1").Protect UserInterfaceOnly:=True, DrawingObjects:=True, Password:="toto", contents:=False

la manipulation de la validation ne provoque plus d'erreur ... mais le contenu des cellules verrouillées n'est plus protégé !

Je joins un nouveau fichier pour illustrer cela : il permet de vérifier l'état de la protection et de modifier le paramètre "Contents".

Si quelqu'un comprends l'intérêt d'un tel fonctionnement de UserInterfaceOnly ...

Cordialement,

Luigi

 

[eriiic]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Bonsoir,

J'ai enfin eu l'erreur.
De ce que j'ai constaté : Contents n'a pas d'influence.
Relancer un coup ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True suffit à ne plus l'avoir. Ce que tu devais faire en testant avec ou sans.
Et si tu le lances 2 fois d'office (?) avec ces 2 seuls paramètre plus d'erreur, ce quel que soit le module d'où tu le lances..
Même à l'ouverture après enregistrement.

Et comme on n'est pas à une bizarrerie près : lorsqu'on a l'erreur, la liste en D1 fonctionne impeccablement elle.

eric

 

[luigiF]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Bonjour Eric et merci de t'interesser à mon problème.

Je pense que "Contents" a une influence, comme le montre mon fichier de test (V3) : la manipulation par VBA de la validation des cellules sur la feuille protégée échoue si "ProtectContents" est à True".

Si tu protèges avec seulement les paramètres
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True, tu ne contrôles pas la valeur de "contents" qui a pu être modifiée par l'utilisateur.

Je ne comprends pas ce que tu veux dire par "si tu le lances 2 fois d'office (?)".

Concernant la liste de validation en D1, il est normal qu'elle fonctionne dans tous les cas car elle n'est manipulée par aucune macro.

Cordialement,

Luigi

 

[eriiic]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Re,

Je ne comprends pas ce que tu veux dire par "si tu le lances 2 fois d'office (?)".

Si je lance ça dans un module quelconque (pour éviter de fermer et rouvrir) :

Sub test()
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True
end sub

Je n'ai plus d'erreurs 1004 ensuite, et le bon fonctionnement est conservé lors de l'enregistrement (ce qui n'empêche pas de le laisser dans workbookOpen ensuite qu'il s'active systématiquement).
Je pense que c'est ce que j'avais dû faire lors du premier test puisque je n'avais pas d'erreur. J'avais dû naviguer 2 fois entre les 2 feuilles avant de tester.
Enfin chez moi ça fait ça, reste à voir si ça se confirme.
Ce qui n'empêche pas d'ajouter Contents si tu crains qu'il soit modifié, je pense juste qu'il n'a pas d'influence sur le bug.

eric

eric

 

[eriiic]

Re : protection, validation via VBA et presse-papier -- DrawingObjects UserInterface

Suite...
Vu que j'avais déjà eu cette erreur 1004 et que c'était dû à une feuille non activée j'ai testé une solution qui semble fonctionner :

Sub test()
    Worksheets("Feuil1").Protect DrawingObjects:=True, UserInterfaceOnly:=True
End Sub

ou

Private Sub Worksheet_Activate()
    Worksheets("Feuil1").Protect DrawingObjects:=True, UserInterfaceOnly:=True
End Sub

On pourrait supposer que l'activation ne soit pas encore totale lorsque s'exécute la fonction (?)

A confirmer car actuellement ta première version de classeur avec Worksheet_Activate() activesheet... suffit pour ne plus avoir l'erreur, comme à mon premier test. Si cette piste est bonne, ça doit se jouer à un pouième de seconde.

eric