Eviter une redondance de code pour formulaire sans user form (Mac)

Bonjour à tous,

Toute neuve en VBA, j'ai tenté mes premières macro récemment pour mon travail : je suis malheureusement sous MAC donc il me manque pas mal d'options disponibles via PC habituellement, et j'ai donc été "obligée" de me mettre au VBA (passionnant, mais qui prend du temps). Il me manque notamment l'option "UserForm" et j'ai donc voulu créer ma base de donnée via un pseudo formulaire en Feuille 1, et ma base de donnée en Feuille 2. J'ai réussi à purger le formulaire, à vérifier s'il y avait des doublons, modifier les données du tableau si le nom existait déjà dans la BDD... mais pas enregistrer correctement les infos (c'est un comble n'est ce pas?)

Premièremement, je n'ai déjà pas réussi à faire fonctionner mon code sur deux feuilles distinctes, et j'ai fini par le faire sur la même feuille en copiant les données de ma feuille 1 éparpillées sur plusieurs colonnes avec des cellules vides au milieu sur une seule colonne de la feuille 2 (mais je serai preneuse pour changer ce bidouillage également).

Ensuite, bien que mon code fonctionne il est infâme car mon formulaire a 400 lignes de données, et je souhaiterais les transposer dans mon tableau de Base de données sous forme de ligne. Donc j'ai vaillamment débuté mon copié collé de ligne de codes, mais j'imagine bien qu'il existe quelque chose pour me permettre d'éviter 400 répétitions de la même chose. J'ai longtemps cherché sur différents forums; vidéos youtube and co (notamment avec les loop et les offset), mais j'ai toujours un bug quelconque et j'avoue arriver à court de temps pour finaliser ce projet pro, c'est pourquoi je me tourne vers vous pour toute assistance que vous pourriez m'apporter.

Voici mon code pour enregistrer une nouvelle fiche client:

_______________

Sub Enregistrer()
With Feuil2 'Correspond à ma feuille de travail (données du formulaire en Feuille 1 copiées sur une seule et même colonne E de la feuille 2 qui comporte également mon tableau de base de données à compléter.
Dim ligne As Integer
ligne = 2

Do While Range("E" & ligne) <> "" And Range("E" & ligne) <> [b2]
ligne = ligne + 1
Loop

Range("e" & ligne) = [b2]
Range("f" & ligne) = [b3]
Range("g" & ligne) = [b4]
Range("h" & ligne) = [b5]
Range("i" & ligne) = [b6]
Range("j" & ligne) = [b7]
Range("k" & ligne) = [b8]
Range("l" & ligne) = [b9]
'(et cetera jusqu'à hypothétiquement ma dernière ligne de données)
End With

dd = MsgBox("Les données ont bien été enregistrées", vbInformation, "Données enregistrées")
End Sub

______________

Le problème se répète pour modifier une fiche déjà existante :

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$5" Then
Dim ligne As Range
Set ligne = [aa:aa].Find(Target)

If Not ligne Is Nothing Then
[c6] = ligne.Offset(0, 1)
[c7] = ligne.Offset(0, 2)
[c8] = ligne.Offset(0, 3)
[c9] = ligne.Offset(0, 4)
[c10] = ligne.Offset(0, 5)
[c11] = ligne.Offset(0, 6)
[c12] = ligne.Offset(0, 7)
' etc, etc...
End If
End If

End Sub

Je vous remercie par avance pour votre aide et vos explications.

Edit modo : mis code entre balises. Merci de pensez à utiliser l'icone </> disponible dans le barre de menu lorsque vous postez un code

J'ai corrigé votre post pour que vous voyiez

Bonjour et bienvenue,

j'ai tenté mes premières macro récemment pour mon travail : je suis malheureusement sous MAC

Pourquoi malheureusement ! ce n'est pas parce que vous ne disposez pas des Userform qu'excel est "réducteur". Certes il y a des choses que l'on ne peut pas faire mais cela n'empêche pas de réaliser le plupart de ce que l'on souhaite. Gardez en mémoire -> MAC = "Think different" et plus simple à utiliser (pour ma part je l'utilise depuis plus de 25 ans).
NB : Pour votre info, depuis la version excel 2011 (qui n'est plus supportée par Miscrosoft), les userform ne sont plus disponibles sur MAC.

Votre formulaire est très bien présenté avec soin d'ailleurs et ce sera bien plus facile d'aller compléter votre base de données que d'utiliser une userform

Les Listes sont ok mais vous auriez pu aussi mettre chaque liste de données au format structuré.

je souhaiterais les transposer dans mon tableau de Base de données sous forme de ligne.

1. Il faut modifier le code mais pouvez-vous expliquer pourquoi dans la feuille 2 vous n'avez pas de titre entre E2 et L2 ?
E2 = Nom scp, F2= N° RSSC, etc....

2. Dans la feuille formulaire, à quoi sert le tableau complétement à droite colonne AA et suivante ? Il est dans la feuil2..

Cordialement

(Je remercie déjà le modérateur qui a vérifié ma demande pour sa correction sur le code présent dans mon post initial)

Bonjour Dan, et merci pour votre retour !

Votre formulaire est très bien présenté avec soin d'ailleurs et ce sera bien plus facile d'aller compléter votre base de données que d'utiliser une userform

C'est bon à savoir ! Et rassurant :D C'est seulement que j'ai eu tellement de mal à trouver des réponses sans les outils proposés par Excel PC (comme userform) que j'en suis arrivée à cette conclusion. Mais je me suis donc fourvoyée !

Les Listes sont ok mais vous auriez pu aussi mettre chaque liste de données au format structuré.

Je ne connaissais pas ce terme, mais après rapide recherche - cela signifie-t-il bien que j'aurais dû mettre chaque liste au sein d'un tableau et ne mentionner que lesdits tableaux dans mes listes ? (cela permetrait-il donc de ne pas avoir à modifier la source à chaque modification?)

1. Il faut modifier le code mais pouvez-vous expliquer pourquoi dans la feuille 2 vous n'avez pas de titre entre E2 et L2 ?

E2 = Nom scp, F2= N° RSSC, etc....

Alors, je dois avouer que j'ai fait énormément de tests et "bidouillages" sur ce pauvre classeur, et il est possible que j'ai par inadvertance supprimé les titres de mes colonnes, mes excuses. (voici modifié en PJ)

2. Dans la feuille formulaire, à quoi sert le tableau complétement à droite colonne AA et suivante ? Il est dans la feuil2..

Absolument, cela suit mes multiples essais/erreurs.

En fait chronologiquement j'ai débuté l'expérience avec 3 feuilles :

1. Formulaire
2. BDD
3. Listes

Sauf que je n'arrivais pas à compléter ma base de données automatiquement avec VBA sur la seconde feuille, donc je l'ai ensuite transférée sur ma feuille 1 de formulaire pour faire en sorte que ça fonctionne déjà dans un premier temps.

Puis, je n'ai pas voulu lâcher l'affaire avec mon histoire de 400 lignes à copier, donc j'ai voulu essayer de contourner le problème tant bien que mal.

Je me suis dit que mon formulaire était peut être le problème vu qu'il comporte des cases vides, et les informations sont réparties sur plusieurs colonnes : ce qui complexifierait mon code et m'empêcherait peut être d'automatiser mes 400 copies inutiles (à mon avis, et donc augmenterait grandement mes erreurs en plus du reste). Donc j'ai voulu simplifier la chose en copiant toutes les données de mon formulaire sur ma feuille 2 automatiquement, et ce, sur une seule colonne sans cellule vide ni rien d'autre qui pourrait entraver mon chemin de débutante.

Ainsi, j'aurais pu à termes masquer mes deux premières colonnes de la feuille 2, et avoir ma BDD sur ma feuille 2 comme prévu initialement avec un code simplifié. (pas trop tordu hein?)

J'ai seulement conservé mon tableau sur la feuille 1 comme une sorte "d'archive" au cas où je venais à tout supprimer par inadvertance ou autre joyeuseté. C'était juste une assurance pour me rassurer.

Voilà j'espère que mes explications seront claires et vous seront utiles pour m'assister sur ce projet. Merci encore par avance pour vos lumières.

Bonjour,

Si je comprends bien ce que vous voulez c'est dans la feuille Formulaire :
- Vous complétez à chaque fois les cellules des colonnes C, H, J, M, O, R et T
- vous voulez les enregistrer dans la feuille (feuil2 - BDD) dans les colonnes E à OM

en gros dans le tableau feuil2 ( E à OM) on doit retrouver les valeurs de la colonne B

Bonjour Dan,

Voici mes réponses en rouge

Si je comprends bien ce que vous voulez c'est dans la feuille Formulaire :

- Vous complétez à chaque fois les cellules des colonnes C, H, J, M, O, R et T + E (précisément E5, E20, E32:E43 et E46:E57)

- vous voulez les enregistrer dans la feuille (feuil2 - BDD) dans les colonnes E à OM - c'est ça !

en gros dans le tableau feuil2 ( E à OM) on doit retrouver les valeurs de la colonne B - tout à fait

Sachant que dans le meilleur des cas, ma colonne B de la feuille 2 - BDD, n'est peut être pas utile, je ne l'ai créée que parce que je pensais que ca simplifierait le code.

--> Mon but initial était seulement de compléter mes colonnes C, E, H, J, M, O, R et T et que ca les enregistre automatiquement dans le tableau de la feuille 2 - BDD quand j'appuie sur le bouton enregistrer.

Ok. Merci des explications

Je regarde cela mais je vous propose de renommer la feuil2 en BDD.

Autre question :
La liste déroulante en C5 fait référence à la première colonne du tableau de droite (colonne AA). Si vous devez ajoutez un nouveau NomSCP, il ne sera pas par défaut dans la liste déroulante. Vous allez l'ajouter directement dans la cellule je suppose
Si oui, il faudra aussi prévoir le doublon ?

Oui, Feuil2 en BDD c'est parfait

La liste déroulante en C5 fait référence à la première colonne du tableau de droite (colonne AA). Si vous devez ajoutez un nouveau NomSCP, il ne sera pas par défaut dans la liste déroulante. Vous allez l'ajouter directement dans la cellule je suppose - oui, ce qui doit alors créer la ligne avec les informations dans la BDD

Si oui, il faudra aussi prévoir le doublon ? - c'est ça, l'objectif est le suivant : si le NomSCP existe dans la colonne AA (et donc dans la liste déroulante), alors les cases du formulaire se remplissent automatiquement avec les informations déjà présentes dans la BDD, et cela me permet alors de mettre à jour les informations de la société si je le souhaite, ou alors de supprimer la ligne correspondante.

c'est ça, l'objectif est le suivant : si le NomSCP existe dans la colonne AA (et donc dans la liste déroulante), alors les cases du formulaire se remplissent automatiquement avec les informations déjà présentes dans la BDD, et cela me permet alors de mettre à jour les informations de la société si je le souhaite, ou alors de supprimer la ligne correspondante.

Ok mais là c'est l'inverse de l'enregistrement. A voir plus tard.
Quand je parle de doublon, c'est surtout de contrôler que le nom que vous allez mettre en C5, ne soit pas déjà repris dans la liste déroulante. Vous pourriez en effet ne pas vérifier dans la liste avant ...

1. lorsque je vois le code Enregistrer actuel dans le fichier, il ne reprend pas toutes les cellules de votre feuille Formulaire. Normal ou vous n'aviez pas encore tout fini de ce coté ?
2. Est-ce que dans le code vous voulez que chaque ligne ait un commentaire de la colonne concernée parce qu'avec 400 lignes à enregistrer cela va faire 400 lignes de codes minimum. Si vous comprenez les boucles on peut raccourcir mais on n'aura pas de commentaire dans le code

Quand je parle de doublon, c'est surtout de contrôler que le nom que vous allez mettre en C5, ne soit pas déjà repris dans la liste déroulante. Vous pourriez en effet ne pas vérifier dans la liste avant ...

Oui je comprends, non je n'ai en effet pas penser à cela, étant donné que quand je commence à écrire le nom d'une société que j'aimerai enregistrer, la liste déroulante se réduit d'elle même, et apparaît automatiquement, mais ca ferait une belle assurance en effet.

1. lorsque je vois le code Enregistrer actuel dans le fichier, il ne reprend pas toutes les cellules de votre feuille Formulaire. Normal ou vous n'aviez pas encore tout fini de ce coté ?

C'est normal, je n'ai pas terminé en effet : en voyant que je devais vraiment copier 400 lignes, je me suis penché sur les boucles mais c'était un peu trop avancé pour moi, et surtout 400 lignes, ça ralentit pas mal mon ordi (alors après, je dis peut être une grosse bêtise et ce n'est pas lié...).

2. Est-ce que dans le code vous voulez que chaque ligne ait un commentaire de la colonne concernée parce qu'avec 400 lignes à enregistrer cela va faire 400 lignes de codes minimum. Si vous comprenez les boucles on peut raccourcir mais on n'aura pas de commentaire dans le code

Non, je préfèrerais me pencher sur les boucles si vous êtes OK, et apprendre comment elles fonctionnent, parce qu'à termes, si je dois encore créer d'autres BDD, j'aurais de toute façon tout autant de données à gérer (voire plus), donc autant m'attaquer aux boucles directement.

Si j'arrive à comprendre les boucles avec vous, je pourrais me rajouter mes commentaires par la suite si cela me semble approprié, qu'en dîtes vous ?

Dans le tableau de la feuille BDD, est-ce normal que les données de la colonne E pour le GERANT soient enregistrées après la colonne DATE EXPIRATION CS COGERANT PP (en colonne AZ)

En gros ce que je vois c'est que les données de la colonne E (personne morale) sont enregistrées après les données de la colonne C

SI changement dites moi ce que je dois modifier mais ne postez pas de fichier

J'ai procédé ainsi en me disant que ce serait plus simple à coder de nouveau (toute la colonne C puis toute la colonne E plutôt que un peu de C puis un peu de E et rebelote).

Mais l'ordre m'importe peu dans le tableau BDD de toute façon.

Bonjour,

Bon je n'ai pas modifié la BDD. On laisse tel que vous voulez.

Est-ce que vous voulez que je vous donne ce que vous devez modifier dans le fichier ou vous préférez que je vous envoie le fichier modifié quitte après à adapter

Les choses qui seront à effectuer :
- Modifications des listes déroulantes
- La feuille BDD
- les codes

Bonjour Dan,

J'accepterai bien volontiers le fichier modifié que je pourrai adapter ensuite si cela s'avère nécessaire étant donné la contrainte professionnelle qui pèse.

Je suis cependant à l'écoute de vos pistes et explications avec grand plaisir.

Merci encore pour votre aide.

Re

Quel est le but des deux codes supprimer qui sont dans la module 1

Une fois enregistrées vous voulez avoir un message de confirmation ?

Quel est le but des deux codes supprimer qui sont dans la module 1

C'était deux tests à appliquer au bouton "Supprimer la SCP"

L'objectif : j'enregistre par exemple la société X, et un beau jour celle ci n'est plus cliente chez nous.

Je rentre le nom de la SCP dans la cellule C5 de la feuille Formulaire, et je clique sur le bouton supprimer : là les cellules de ma BDD se vident et la ligne est de nouveau disponible pour la prochaine entrée. J'ai ensuite un message de confirmation comme quoi ma société a bien été supprimée de ma BDD.

J'ai choisi de vider la ligne plutôt que de la supprimer car j'avais placé mon tableau sur le côté, et non en dessous de mon formulaire : je ne pouvais donc pas supprimer les lignes de la BDD sans impacter celles du formulaires.

Une fois enregistrées vous voulez avoir un message de confirmation ?

Oui, c'est d'ailleurs ce que j'avais mis en place avec :

dd = MsgBox("Les données ont bien été enregistrées", vbInformation, "Données enregistrées")

Y a t il mieux à faire ?

C'était deux tests à appliquer au bouton "Supprimer la SCP"
L'objectif : j'enregistre par exemple la société X, et un beau jour celle ci n'est plus cliente chez nous.

Ok pour les explications

Voici votre fichier en retour dans lequel :
1. La feuil2
- est renommée BDD
- Les colonnes A à C sont supprimées dans la feuille BDD
- Le tableau commence à la colonne A
- le tableau est nommé Tab_BDD dans le gestionnaire de noms.
- Les formules pour les listes déroulantes sont revues de sorte que si vous ajoutez une info en supplément dans une des colonnes de la feuille Listes, elle sera automatiquement prise en compte dans la liste déroulante associée en feuille Formaulaire

2. Le code enregistrer est revu et adapté à votre tableau

Pour le test, complétez votre formulaire et une fois fait cliquez sur "Enregistrer"
Merci de reparti de ce fichier pour le futur
Ne me renvoyez pas de fichier, laissez-moi vos commentaires

Rem :
- les codes "supprimer" ne sont pas encore inclus. Je regarde cela une fois que vous confirmerez que l'enregistrement est ok
- Le code Purger fonctionne mais on aurait aussi pu l'exécuter à la fin de l'enregistrement -> A voir
- Juste pour votre info : vous aviez mis votre tableau en feuille BDD au format dit "structuré" (vous l'aviez fait dans votre fichier).
Gardez en mémoire qu'il ne faut jamais de lignes sans données dans un tableau mis au format structuré (ce que vous aviez fait dans votre fichier posté)
Si vous faites un ajout de ligne manuellement, il vous suffit d'ajouter en dessous de la dernière ligne et excel verra automatiquement la nouvelle ligne

Alors, déjà, merci infiniment pour votre aide, qui me sauve vraiment la mise.

Je viens de finaliser le test, il n'y avait qu'une petite coquille :

Sub enregister()
Dim lig As Integer

With Feuil2.ListObjects(1)
    If .ListRows.Count = 0 Then
        .ListRows.Add: lig = 1
    Else: .ListRows.Add: lig = .ListRows.Count
    End If
    With .DataBodyRange
        .Item(lig, 1).Resize(, 14).Value = Application.Transpose(Feuil1.Cells(5, 3).Resize(14).Value) 'donnees societe
        .Item(lig, 15).Resize(, 10).Value = Application.Transpose(Feuil1.Cells(20, 3).Resize(10).Value) 'bien immobilier 

Dernière ligne de ma copie de code : .item(lig, 15) au lieu de 14, et tout est bon pour le reste.

- Le code Purger fonctionne mais on aurait aussi pu l'exécuter à la fin de l'enregistrement -> A voir - je pourrai le rajouter si besoin, mais en attendant je préfère rester sans l'option de purge auto après l'enregistrement.

- Juste pour votre info : vous aviez mis votre tableau en feuille BDD au format dit "structuré" (vous l'aviez fait dans votre fichier). - tout à fait

Gardez en mémoire qu'il ne faut jamais de lignes sans données dans un tableau mis au format structuré (ce que vous aviez fait dans votre fichier posté)

Si vous faites un ajout de ligne manuellement, il vous suffit d'ajouter en dessous de la dernière ligne et excel verra automatiquement la nouvelle ligne

C'est tout à fait exact, je prends note de vos remarques et vous remercie de nouveau pour le temps que vous m'accordez, j'appliquerai tous ces conseils dans mes prochains tableaux :)

Je vais regarder plus en détail votre code et ne manquerai pas de vous poser mes questions si je n'arrivais pas à tout bien décortiquer de mon côté... Merci

Bonjour,

Dernière ligne de ma copie de code : .item(lig, 15) au lieu de 14, et tout est bon pour le reste.

Oui, juste ! désolé.

Reste le code supprimer que vous pouvez placer dans votre fichier, par exemple en dessous du code Purger

Sub Supprimer()'suppression SCP
Dim lig As Integer

If MsgBox("Etes-vous certain de vouloir supprimer la SCP " & UCase(Feuil1.Range("C5")) & " ?", vbYesNo, "Attention Suppression irreversible") = vbYes Then
    On Error Resume Next
    lig = WorksheetFunction.Match(Feuil1.Range("C5").Value, Feuil2.ListObjects(1).ListColumns(1).DataBodyRange, 0)
    If lig = 0 Then MsgBox "Valeur non trouvee dans la base de donnees", vbCritical, "Erreur valeur": Exit Sub

    Feuil2.ListObjects(1).ListRows(lig).Range.Delete
    MsgBox "Les donnees ont bien ete supprimees", vbInformation, "Confirmation Suppression"
    'Call Purger
End If
End Sub

A la fin du code j'ai désactivé l'appel au code Purger. Enlevez l'apostrophe si vous voulez que le code soit exécuté une fois que vous avez supprimer le SCP

Le principe reste le même, vous sélectionnez la société en cellule C5 du formulaire. Le code vous demandera la confirmation.
Si le hasard fait que la valeur C5 n'existe pas dans la feuille BDD, le code vous le signalera et se terminera

Rem : j'ai enlevé les accents dans les lignes de Msgbox car lors du passage de MAC à Windows, les accents sont interprétés différemment. Avec VBA il est préférable de toujours penser Anglais (donc sans accents...)

Dites-moi pour la suite

Cordialement

Bonjour Dan,

Je viens de tester le code supprimer, et ça fonctionne superbement bien, merci ! Je note également la problématique des accents, c'est un comble moi qui cours toujours après mes collègues qui mettent des accents partout

Je me suis penchée hier sur la possibilité de retrouver une fiche déjà existante dans la BDD et la modifier sans créer de nouvelle ligne.

Imaginons, je rentre la SCP X, je l'enregistre.

Je souhaite ensuite la modifier, donc j'aimerais que le formulaire se remplisse automatiquement avec les données de la BDD, et modifier les données - puis enregistrer mes modifications.

Pour se faire j'avais créée dans mon premier document envoyé sur le forum un Sub Worksheet Change et la fonction find que tu devrais pouvoir retrouver dans le code VBA sur la feuille 1. Mais je me retrouvais de nouveau face au problème de recopier 400 lignes. J'ai voulu utiliser ton ".item . resize..." mais sans succès.

c'est un comble moi qui cours toujours après mes collègues qui mettent des accents partout

Bah logique si on veut écrire correctement le Français mais avec VBA cela peut poser des soucis quelques fois

sur la possibilité de retrouver une fiche déjà existante dans la BDD et la modifier sans créer de nouvelle ligne.

Je me doutais que cette question allait arriver...
On peut faire en sorte que sur le choix en C5, le code rapatrie toutes les données depuis la feuille BDD. Après on a le choix de supprimer via le bouton ou de modifier
Ne pas oublier qu'actuellement le code Supprimer se fait aussi sur le choix de C5
Là j'ajouterais un bouton modifier. cela vous laisserait le choix de Supprimer ou modifier la ligne

Si ok, je regarderai pour faire le code