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

Re-bonjour Eric,

Peux-tu faire afficher l'etat de "ProtectContents" après ton opération de protection pour voir s'il est à vrai ou faux lorsque tu n'as pas d'erreur, s'il te plait ?

msgbox "ProtectContents : " & Worksheets("Feuil1").ProtectContents

Cela me dépasse ... je ne peux pas croire qu'une histoire de temps d'activation soit à l'origine du problème !

Merci pour ton aide,

Luigi

 

[eriiic]

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

re,

Peux-tu faire afficher l'etat de "ProtectContents" après ton opération de protection pour voir s'il est à vrai ou faux lorsque tu n'as pas d'erreur, s'il te plait ?

Ben il peut être l'un ou l'autre sans problème, même si je force contents:=false dans le .protect.
Il est remis d'office lors d'une protection manuelle, il reste à cet état tant qu'on n'y touche pas.

Mais peut-être qu'on s'est mal compris. A aucun moment je ne t'ai dit de ne pas le mettre.
Je te faisais juste part d'un constat lors de mes tests : si je désigne la feuille ou si je lance 2 fois je n'arrive plus à avoir l'erreur 1004, même si contents:=true n'est pas présent.
Quant à l'interprétation que j'en fais ce n'est qu'une tentative d'explication, rien de plus.

eric

 

[luigiF]

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

Merci pour cette ultime réponse, Eric.

J'avais bien compris le sens des tests que tu as menés, mais ... je n'obtiens pas les mêmes résultats (ni en appelant deux fois la protection, ni en activant la feuille) : chez moi le seul moyen de ne pas provoquer l'erreur 1004 est d'appeler protect avec Contents:=true ... ce qui ne sert pas à grand chose.

J'ai consulté d'autres forums et trouvé plusieurs témoignages du comportement aléatoire de UserInterfaceOnly dès que l'on touche aux validations : dans la plupart des cas il est recommandé en définitive d'opter pour la stratégie de protection/déprotection qui a de nombreux inconvénients (... puisqu'elle vide le presse-papier) mais qui est la seule solution fiable.

Comme je le signalais dans un message précédent, ce problème a même fait trébucher certains "MVPs" ... n'en étant pas un, je renonce !

Je te remercie pour tes efforts !

Bonne soirée,

Luigi

 

[eriiic]

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

Même si c'est relou, il ne te reste plus qu'à récupérer le PP dans une variable avant...
eric

 

[luigiF]

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

Il est plus simple d'utiliser le presse-papier de windows ... même si ca pose aussi quelques problèmes ...

Private Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "User32" () As Long

Sub ProtectSheet(Optional factice As String)
OpenClipboard 0
    ActiveSheet.Protect UserInterfaceOnly:=True, Password:=Thepass
CloseClipboard
End Sub

Sub UnProtectSheet(Optional factice As String)
OpenClipboard 0
    ActiveSheet.Unprotect Password:=Thepass
CloseClipboard
End Sub

Luigi