Interdire la modif d'une formule dans un Tableau Excel

[girole]

Bonjour tout le monde!

Voici ma requête, à laquelle je n'ai pas trouvé de réponse dans l'historique du forum.

Je tiens les comptes de mon foyer. Pour cela j'ai créé un petit tableau excel (un "tableau déclaré" crois-je savoir!). La saisie s'effectue dans les cinq colonnes de gauche, puis les autres colonnes contiennent des formules diverses calculant les résultats qui m'intéressent à partir des colonnes de gauche.

Je voudrais interdire la modif des formules, tout en permettant la saisie dans les 5 colonnes de gauche, et l'agrandissement automatique du tableau.

En utilisant le verrouillage de la feuille, le tableau ne s'agrandit pas automatiquement.

En utilisant la validation des données, la seule solution qui me donne le résultat escompté est inélégante:
Dans le champ "Autoriser" je sélectionne "longueur de texte"
Dans le champ "Données" je sélectionne "inférieure à"
Dans le champ "Maximum" je tape 0
Je décoche "Ignorer si vide"

Ainsi, lorsque l'on saisit quoi que ce soit (et même rien du tout) dans une cellule concernée par la validation, un message d'erreur apparaît "La valeur à entrer doit avoir une longueur de texte inférieure à 0." C'est mon compagnon qui va rigoler quand il va tomber sur ce message, et je vais encore me faire moquer! Alors que ça marche, hein!

La validation des données ne concernant pas la recopie automatique, la formule est écrite automatiquement dans la cellule lors de l'agrandissement (automatique ou manuel) du tableau.

Quelqu'un verrait-il une solution plus chic?
NB: sans utiliser visual basic svp! Je n'ai pas installé ce module (on dit comme ça?)

Merci. Je suis curieuse de vos réponses, et j'espère ne pas être la énième a formuler cette question!

 

[sousou]

Re : Interdire la modif d'une formule dans un Tableau Excel

Bonjour Girole.
En principe:
Tu déverouilles toutes les cellules,
Tu verrouilles la colonne F
Tu protèges la feuille en cochant tous sauf sélectionner les cellules protéges
ça devrait fonctionner

 

[girole]

Re : Interdire la modif d'une formule dans un Tableau Excel

Merci de ta réactivité!

En effet, cela empêche de modifier le contenu de la colonne F, mais cela empêche aussi d'agrandir le tableau (manuellement ou automatiquement): lorsque la feuille est verrouillée, on ne voit plus la petite poignée en bas à droite du tableau.
Or cette fonction m'intéresse et je souhaite l'utiliser!

 

[sousou]

Re : Interdire la modif d'une formule dans un Tableau Excel

Désolé
Pas d'idée sans vb

 

[girole]

Re : Interdire la modif d'une formule dans un Tableau Excel

Pas de souci, merci de ton intérêt!

 

[Amilo]

Re : Interdire la modif d'une formule dans un Tableau Excel

Bonsoir à tous,

Faut voir la formule mais à essayer en sélectionnant la plage qui comporte les formules, F2 sur la 1ère cellule et puis Ctrl + Alt + Entrée pour transformer toute la plage en formule matricielle,

Cordialement

 

[girole]

Re : Interdire la modif d'une formule dans un Tableau Excel

Merci Amilo

Je n'ai pas exploré cette piste car, eh bien, euh, j'ignore ce qu'est une formule matricielle. J'ai bien croisé ce vocable dans mes diverses recherches, mais je n'ai pas encore cherché à en savoir plus.

Je viens de lire sur l'aide en ligne:
"Une formule utilisée comme formule matricielle permet d’effectuer des calculs sur des lignes et des colonnes de cellules à l’aide d’une seule formule, là où il en faudrait normalement plusieurs."
Mes connaissances s'arrêtent là pour le moment.

Or il se trouve que je me suis débrouillée (aidée en cela par les références structurées, mais j'ai aussi trouvé moyen de faire sans), je me suis donc débrouillée pour que chaque colonne contienne une seule formule, répétée à chaque ligne. J'ai écrit des formules matricielles sans le savoir?

L'écriture est bien compliquée car j'utilise la fonction INDIRECT au lieu de références relatives. MAIS cela me permet d'utiliser dans ma formule uniquement des références de cellules appartenant à la même ligne. Il s'ensuit deux avantages: d'abord la formule est exactement la même dans chaque case de la colonne, ensuite mon calcul résiste à une suppression, une insertion ou un déplacement de ligne, et même à un déplacement de colonne.

Je mets en pièce jointe le tableau avec des formules que j'ai vraiment utilisées. Elle sont un peu pénibles à lire. Je pourrais en refaire des plus simples pour l'exemple si nécessaire.

Et donc, l'intérêt des formules matricielles pour interdire la saisie dans mes colonnes de droite, tout en permettant l'agrandissement du tableau?

 

[girole]

Re : Interdire la modif d'une formule dans un Tableau Excel

Amilo, j'essaie ta combinaison, mais je ne suis pas sûre de vraiment taper F2. A quelles actions correspondent ces combinaisons?

 

[laetitia90]

Re : Interdire la modif d'une formule dans un Tableau Excel

bonsoir tous

pour faire simple tu pourrais utiliser ScrollArea

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  ScrollArea = ("a1:e20000")
End Sub

 

[girole]

Re : Interdire la modif d'une formule dans un Tableau Excel

merci laetitia 90

Mais je joue sans vba! C'est plus rigolo

 

[Amilo]

Re : Interdire la modif d'une formule dans un Tableau Excel

Bonsoir Girole,

L'autre intérêt des formules matricielles est de protéger les formules contre certaines manipulations involontaires sur les cellules contenant des formules,
Je n'ai malheureusement pas pris le soin de regarder en détail votre fichier et les formules qu'il contient,
Sur ce type de tableau, cela ne fonctionne effectivement pas,
Et non plus pas pour toutes les formules,

Sinon je ne vois pas trop de solution sans VBA

Edit : voilà Laetitia a trouvé une solution en VBA sauf que comme je craignais le tableau n'est plus auto extensible avec la touche Tab

Cordialement

 

[girole]

Re : Interdire la modif d'une formule dans un Tableau Excel

Amilo, mes formules sont assez laides et pénibles à lire. Vous ne perdez pas grand chose! Je me renseignerai au moins un peu sur les formules matricielles avant d'entamer une discussion à ce sujet! Merci de me l'avoir fait entrevoir. Ma curiosité est éveillée.

Sinon, étant donné que la formule est identique dans toute la colonne, j'avais pensé à une validation des données en comparant la saisie au texte de la formule que j'aurais stocké quelque part (j'ai une petite dizaine de formules, c'est raisonnable) mais je ne vois pas bien comment faire, si tant est que ce soit possible.

Si cela donne des idées à quelqu'un ou quelqu'une...

Bonne soirée à tous!

 

[Amilo]

Re : Interdire la modif d'une formule dans un Tableau Excel

Bonsoir le forum, girole,

En passant par une validation de données, sauf erreur de ma part, on peut effectivement protéger les cellules comportant des formules mais pas contre une suppression de celles-ci,
Il est très facile de trouver sur le net et notamment sur ce site, la procédure par validation de données,

Sinon, j'utilise sur certains de mes fichiers, une mise en forme conditionnelle qui signale dès qu'une formule a été supprimée,

Si cela vous intéresse, je peux vous l'indiquer.

Cordialement

 

[Staple1600]

Re : Interdire la modif d'une formule dans un Tableau Excel

Bonsoir à tous

girole [highlight][Bienvenue sur le forum][/code]

leti
Arghhhhhhhhhhh

NB: sans utiliser visual basic svp! Je n'ai pas installé ce module (on dit comme ça?)

bonsoir tous
pour faire simple tu pourrais utiliser ScrollArea

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  ScrollArea = ("a1:e20000")
End Sub

 

[girole]

Re : Interdire la modif d'une formule dans un Tableau Excel

Bonsoir Amilo et staple et tout le monde


Je prendrai donc la peine de chercher la procédure par validation, et de voir si j'arrive à l'appliquer à mon cas. Y a pas de raison...

En attendant je garde ma validation par le nombre de caractères malgré son inélégance et sa faiblesse (on peut modifier le contenu par recopie). J'ai modifié le message d'alerte pour que cette pauvre ruse passe inaperçue! J'ai appliqué la validation aux colonnes du tableau qui contiennent des formules, ainsi qu'à toutes les cellules encore vides de ces colonnes, situées au-dessous du tableau.

J'ai aussi jeté un coup d'œil aux explications sur les formules matricielles. Du peu que j'ai vu, j'ai l'impression que les références structurées, donnent accès à quelques-unes des possibilités offertes par les formules matricielles. Dans le cas de mon petit tableau, j'apprécie beaucoup l'agrandissement automatique qui permet de se préoccuper de la saisie, et uniquement de la saisie, les formules, formats et mises en forme étant recopiés automatiquement, sans risque d'erreur. Avant d'obtenir un résultat semblable avec des formules matricielles, il va me falloir beaucoup d'entraînement.

Je trouve que le tableau déclaré est un assez bon outil pour des utilisateurs de petit niveau, à condition que les calculs et manips de données restent relativement simples...

Merci encore pour ton avis