VBA versus Formules

[JeFiltre]

Bonjour


SITUTATION:
Un classeur contenant 12 feuilles (12 mois)
et dans chaque feuille une ActiveSheet.usedRange de 502 lignes
avec ces formules répétées dans 500 lignes dans les 12 feuilles
=SI($H3="";"";RECHERCHEV($H3;Bdclient;2;FAUX))
=SI($H3="";"";RECHERCHEV($H3;Bdclient;3;FAUX))
=SI($H3="";"";RECHERCHEV($H3;Bdclient;4;FAUX))

PROBLEME:
L'utilisateur du classeur est un novice
qui parfois efface des formules par inadvertance
(protection classeur inefficace car l'utilisateur sait l'oter)
Est-il préférable de remplacer ces formules par une macro VBA
qui en fin de saisie en Hx-H500 fasse ce que font actuellement les formules?
J'ai pensé mettre la macro ici
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Je ne vois encore la syntaxe à utiliser
J'attends vos conseils.

Merci

 

[G.David]

Salut
et juste un bouton qui reecrit toutes la ligne de formules? ou autrement
supprimes toutes les barres doutils sur le classeur et protège le par mot de passe si il peut y déplomber c'est qu'il n'esyt pas si novice et il te restera la pédagogie DCPAC
Cordialement
G.David

 

[JeFiltre]

bonjour

protéger la feuille génère d'autres problèmes

Je pensais aussi qu'en utilisant VBA cela allègerai le classeur et le temps de
calcul

et par la même occasion si plus de formules dans les cellules
pas de souci d'effacement par inadvertance.

Peut-être me trompe-je?

En plus en cas de soucis la personne m'envoie le classeur
et je dois revérifier si toutes les formules sont intactes

ce qui devient vite lourd à gérer.

Voila pourquoi je voudrais remplacer les formules par du VBA

 

[JeFiltre]

Bonsoir

Voila ou j'en suis de ma réflexion
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cel In ActiveSheet.UsedRange.Columns(8).Cells
cel.FormulaR1C1 = "=VLOOKUP(RC[-1],RC[2]:R[14]C[4],2,FALSE)"
Next cel
End Sub

Résultat si on n'efface la formule elle se réinscrit
C'est déjà ça

Mais ceque je voudrais arriverà faire
c'est qu'il n'y ai pas de formule qui apparaisse dans les cellules
juste le résultat de la recherche
ainsi les 500 cellules des douzes feuilles seraient vides au départ
Dés qu'on saisit un nom, la recherche se fait en vba, affiche le résultat.

Tant que la cellule de saisie est vide, la cellule contenant la formule est vide elle aussi.

Est-ce une bonne façon de voir les choses
ou dois-je absolument passer par des formules dans chaque cellule
et me résoudre à portéger le feuilles.

Merci de m'aiguiller sur les pistes les plus élégantes possibles
(en terme de fonctionnement optimum du classeur)

 

[Celeda]

Bonjour,


Pour ma part, ayant affaire souvent à des novices, mais surtout à des gens préssés de finir un travail , il arrivait parfois qu'ils effacaient les formules.

J'ai complètement évincé les solutions de protections de quelque nature qu'elles soient pour simplement leur faire prendre conscience que l'outil que je mettais à leur disposition était Notre outil. Sur chaque feuille, j'avais prévenu par une petite boite que "détruire une cellule ou une ligne pouvait générer un mauvais fonctionnement du classeur et une macro avec : Votre contact (le email de bibi of course!!)

Donc en régle générale _ hormis sur des prix que je protégeais par l'installation du macro de protection sur filtre _ toutes les cellules pouvaient être effacées et de ce fait, tous les résultats étaient faussés de feuille en feuille.

Le temps m'a donné raison. Mais avec le temps aussi, j'ai préparé une feuille Home dans laquelle, je collais quelques instructions en cas d'effacement fortuit de certaines cellules en y collant tout bonnement,
les formules à replacer. Et mieux, je faisais un lexique des formules (format conditionnel et tout le tralala) avec une succincte explication et des liens hypertextes pour renvoi direct dans la bonne cellule. Et bizarrement, je n'ai eu aucune trouble par la suite. Plus de formules effacées et des fichiers retournés impec.
Mais j'ai continué mes lexiques de formules!!

Si cela peut t'aider,

Celeda

 

[Michel_M]

Bonsoir à Tous,

Difficille de te proposer une piste sans que tu nous envoies un extrait de ta feuille (on a compris que les 12 étaient identiques?)

En utilisant VBA, l'objectif sera de se passer de formules peut-etre en utilisant cells(x,y).offset(p,q) ou autre chose mais surtout pas formula R1C1

Je n'ai pas bien compris le coup de ta cellule de saisie...

Donc, on t'attend: explique bien ce que tu veux faire car nous on ne connait pas ton pourquoi du comment.

Michel

 

[G.David]

Il reste que si tu veut récuperer la valeur de ta cellule il faudra d'abords la calculer ensuit prendre sa valeur et enfin reinscrire la valeur dans la cellule
ça risque d'etre tres lourds ou alors
calculer tout tes valeur par VBA et inscrire les résultats dans les cellules concerné
Quoi qu'il en soit le VBA risque bien d'etre plus lent que les formule de feuille
Cordialement
G.David

 

[Sylvain]

bonjour,

il y a aussi la possibilité de scrollarea, à mettre dans worksheet.activate. On peut ainsi éviter la protection et protéger les formules en les mettant à l'extérieur de la zone de travail.
Au lieu de mettre la formule en A2 on la met en Z2
en A2 on met =Z2
et comme ça en autorisant scrollarea="a" ce qu'on risque au maximum c'est d'effacer une formule simple.

A+

 

[JeFiltre]

Bonjour
voici en piece jointe le fichier

FONCTIONNEMENT
en colonne A:A, on lit un code barre avec une douchette
de ce code barre, on obtient le prix (par formule)
ensuite on saisit la date
puis le nom du client ,on obtient les coordonnées (par formule)
puis on indique le mode de paiement.


Le classeur final aura 12 feuilles mensuelles
avec des zones nommées contenant 500 lignes

Ce que j'essaye d'obtenir
c'est qu'il n'y ait plus de formules dans chaque cellule
mais dès que les cellules utilisées actuellement dans les formules ne sont pas vide, alors VBA se subsitue aux formules et affiche le resultat

Ainsi peut importe si on efface le contenu
car à chaque recalcul du classeur
chaque cellule non vide serait "recalulée" en VBA
(colonnes concernées:
prix € adresse ville teleph che esp cb)

J'espère avoir été clair dans mes explications

J'ai précédemment essayé de protéger le classeur
mais l'utilisateur ote la protection
et on me demande ne pas mettre de mot de passe

Merci en tout cas à tous pour vos conseils

 

[Michel_M]

Bonsoir à tous,

Ci joint proposition en VBA qui enlève tes pb de protection: chaque changement renvoie au nom client et réinscrit les données de "bdclient" et refait l'affectationbn suivant le type de paiement.
(si je n'ai rien oublié, comme d'hab)

Il faudra recopier dans les 11 mois la macro événementielle du module-feuille de janvier; la macro "situer" étant commune à l'usine à gaz

A+

Michel

 

[G.David]

Salut
une question de groupe au matin
mais dans son cas lever la protection est d'une facilité déconcertante si il n'y a pas de mot de passe ne peut t on pas envisager de sucrer la commande protection dans thisworkbook à l'ouverture du fichier ?
juste une interrogation
G.David

 

[Michel_M]

Bonjour à tous,

Pendant que XLD marche:

Evidemment j'avais oublié le cas ou la cellule "nom" (Hx) est validée avec une valeur nulle. ci joint la rustine.

La limite de ta protection devient maintenant la modification de code du programme: cela s'appele de la malveillance et est une faute professionnelle grave.

A+
Michel

 

[Je Filtre]

Bonjour

La solution de Michel_M est limpide
Ci-joint le module auquel j'au ajouté des modifications

Le but final est limiter au strict minimum l'intervention de l'utilisateur

J'essaie maintenant (avec votre aide) de:
Lorsque qu'on saisit le code barre, selection de la prochaine cellule devant être renseignée (dans l'ordre date, nom, paiement)
si un code barre est saisi, la date du client suivant est préremplie
(=la date du précédent client)
a l'ouverture du classeur selection de la feuille du mois en cours
(tous les autres mois sont cachés)
et selection de la cellule vide au dessous du dernier client saisi.

Les personnes qui utilisent ce classeur sont sur un site extérieur à celui ou
je travaille.
Dans la permière version, il y avait des protections, une barre d'outils personnalisée mais en cas de plantage, on me téléphone
et donc outils/protection/oter la protection

D'ou mon souhait de demander à VBA de prendre en charge un maximum d'opérations, d'abandonner les formules.

(La première version pèse 3 Mo)
Est-ce que de cette façon (celle de Michel_M), la taille du classeur
diminuera de façon spectaculaire.

En tous cas merci à tous.

(Prochainement j'enverrai en pièces jointes les modules du premier classeur)

 

[Je Filtre]

Rebonjour
Ci-ici vraiment joint le module en précedent message
(Suis sur un ordinateur sans utilitaire de compression)
ai du en installer un.

Milles excuses.

 

[Michel_M]

Bonsoir,

Difficille de répondre sur la taille future de ton fichier...

Essaies de tester avec aucune opération en Janvier et pas mal de clients, regardes l'encombrement
puis même punition avec tes 500 opérations du mois: tu auras une idée de l'encombrement (diifférence * 12).

Tes utilisateurs sont sur site extérieur: bonjour la galère pour ouvrir plusieurs Mo ADSL ou pas (sans compter les pb de partage...)

A mon avis il faudrait revoir le pb avec 12 classeurs- mois, un classeur recap des 12 mois et une base de données clients soit access soit excel fermé. C'est faisable bien que pas trop facile et long à faire... De ttes façons si tu risque d'avoir plus de 20 utilisateurs simultanés, Access ramera.

Pour Excel fermé il faudra utiliser la technologie A.D.O

A toi de voir, je ne te garantis rien ce week-end et je serais indisponible jusqu'à mercredi