.Protect ET .Validation.ShowInput

[David C.]

j'ai une feuille protégée (à chaque ré-ouverture) avec l'option:
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True

donc normalement le code peut modifier la feuille sans problème.

Sauf que quand j'essaye de modifier sur certaines cellules la propriété
suivante:
.Validation.ShowInput = True

eh bien... ça plante.
Désactiver totalement la protection (ActiveSheet.Unprotect) corrige le
problème et la modification se passe parfaitement bien.
Est-ce normal ? Qu'est-ce que j'oublie ?
Y'a-t-il une option de plus à ajouter à Protect pour obtenir que le code
puisse modifier les validations ?

 

[David C.]

I have a sheet re-protected (at each activation) with the options:
ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True,
Contents:=True, Scenarios:=True

So, the code is supposed to be abble to change the cells...

But, when I try to change this properties on cells:
.Validation.ShowInput = True
then... error !

Unprotecting the sheet is a solution (ActiveSheet.Unprotect)
and then I can change the Validation propertie

Is this supposed to happen ? Am i forgetting something ?
Is there an other option missing in the protect method ?

 

[Norman Jones]

Hi David,

Try:

ActiveSheet.UnProtect

'Your validation code

ActiveSheet.Protect UserInterfaceOnly:=True, _
DrawingObjects:=True, _
Contents:=True, Scenarios:=True

 

[Norman Jones]

Hi David,

Is this supposed to happen ? Am i forgetting something ?

I forgot to add that using the UseInterfaceOnly argument permits various
code manipulation of a protected sheet. However, this argument does not
provide access to everything that would be possible on an unprotected sheet

Is there an other option missing in the protect method ?

Not to my knowledge in xl2k and earlier. I cannot speak for later versions.

 

[David C.]

Well; sure...
but because of a password (in later use) I'd prefer not to use that way...
Well...
thank you.