VBA: création ligne incrémentée sur 4 onglets selon critère alphabétique

Bonjour amis helpers Excel,

Nouveau dans ce forum et dans ce genre d'exercice, je compte sur votre bienveillance pour m'aider. J'ai une question qui peut paraître facile à résoudre pour vous, mais que je n'arrive pas à comprendre et trouver une solution...

7projetvba-v1.xlsx (22.28 Ko)

Explication de la problématique.

1/ Onglet Data : Derrière le bouton Macro "Saisie nouvelle ligne" pourrait (mais encore faut-il le créer...) afficherait un UserForm développant 6 champs définissant eux-mêmes les 6 entêtes des 6 colonnes à renseigner.

2/ Par la validation, une création d'une ligne s'incrémente sur chacun des 4 onglets et s'imbriquant au bon endroit : ordre alphabétique par le 1er champ "Pays" A, B, C, etc...

3/ Les valeurs pré-introduites précédemment dans les cellules existantes seront déplacées par une recopie sur les lignes du dessous (glissement par le bas d'une ligne)

Exemple avant et après création d'une ligne :

capture d ecran 2021 04 06 223932 capture d ecran 2021 04 06 224155

J'ai trouvé des éléments de réponse dans ce forum Copier / Inserer ligne par macro (excel-pratique.com), qui pourrait (avec un peu de modification) servir à mon besoin.

Qu'en pensez-vous ? Prêt à vous lancer ? Par avance Merci.

Bonjour,

Ci-joint un code rapidement réalisé mais fonctionnel avec table structurée. Je suis juste passé par un ajout puis un tri au lieu d'insérer une ligne qui va être, je pense, plus long sur une grosse basse de donnée. Je n'ai pas ajouté de contrôle sur le remplissage de toutes les textbox mais c'est possible selon demande. Une contrôle a été ajouté sur les textbox date qui sont souvent sources d'erreurs.

Private Sub CommandButton1_Click()
Dim i%, LR%
LR = ActiveSheet.Cells(ActiveSheet.Rows.Count, 2).End(xlUp).Row + 1
For i = 6 To 7
    If Not IsDate(Me.Controls("TextBox" & i)) Then
        MsgBox "Merci de renseigner une date valide", vbCritical
        Exit Sub
    End If
Next i
For i = 2 To 5
    ActiveSheet.Cells(LR, i) = Me.Controls("TextBox" & i)
Next
ActiveSheet.Cells(LR, 6) = CDate(Me.Controls("TextBox6"))
ActiveSheet.Cells(LR, 7) = CDate(Me.Controls("TextBox7"))
With ActiveSheet.ListObjects("BDD").Sort
    .SortFields.Add Key:=ActiveSheet.ListObjects("BDD").ListColumns(1).DataBodyRange, Order:=xlAscending
    .Apply
    .SortFields.Clear
End With
End Sub

Cdlt,

@Ergotamine, merci pour ton soutien et ta réactivité.
Bon, si toi ou les autres membres du fil peuvent continuer à m'aider on avance car c'est ce que je cherchais, mais il manque un élément important et essentiel décrit dans mon 1er post, CàD qu'il faudrait que cette ligne créée par le UserForm se copie (et au bon endroit alphabétique) dans les onglets FeuilA, FeuilB, FeuilC, etc...
Penses-tu/pensez-vous que ce soit possible?

Bonjour,

Ci-joint. Je suis passé par un simple copier coller de la table structurée en bouclant sur tes différentes feuilles contenues dans une variable tableau. J'ai ajouté un message de confirmation de l'export.

Private Sub CommandButton1_Click()
Dim i%, LR%, FEUILLE As Variant
Dim FEUILLES()
FEUILLES = Array("FeuilA", "FeuilB", "FeuilC")
LR = ActiveSheet.Cells(ActiveSheet.Rows.Count, 2).End(xlUp).Row + 1
For i = 6 To 7
    If Not IsDate(Me.Controls("TextBox" & i)) Then
        MsgBox "Merci de renseigner une date valide", vbCritical
        Exit Sub
    End If
Next i
For i = 2 To 5
    ActiveSheet.Cells(LR, i) = Me.Controls("TextBox" & i)
Next
ActiveSheet.Cells(LR, 6) = CDate(Me.Controls("TextBox6"))
ActiveSheet.Cells(LR, 7) = CDate(Me.Controls("TextBox7"))
With ActiveSheet.ListObjects("BDD").Sort
    .SortFields.Add Key:=ActiveSheet.ListObjects("BDD").ListColumns(1).DataBodyRange, Order:=xlAscending
    .Apply
    .SortFields.Clear
End With
ActiveSheet.ListObjects("BDD").Range.Copy
For Each FEUILLE In FEUILLES
    Worksheets(FEUILLE).[B4].PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Next FEUILLE
Application.CutCopyMode = False
Worksheets("Data").Activate
Unload Me
MsgBox "Nouvelle ligne ajoutée", vbInformation
End Sub

Cdlt,

@Ergotamine, c'est SENSATIONNEL ce que tu as fait : réactivité et compétences ! MERCI
Comme-tu l'auras compris, le petit fichier Excel inclus est une adaptation d'un vrai projet pour lequel je suis responsable et par mesure de "confidentialité, je ne l'ai pas diffusé sur ce forum dans sa vraie nature, mais en ai fait une "adaptation" de mon besoin.
Quels sont les points de "modification" que je dois apporter à mon vrai fichier si je souhaite transposer ton code VBA sur mon editeur Visual Basic (Alt+F11) au travers de mon UserForm déjà existant, STP ?
-> J'imagine : noms des onglets, noms des entêtes de colonnes ou champs d'actions,
Aussi, j'ai d'autres Questions de type MFC(s) qui ne fonctionnent pas bien : que me conseilles-tu de faire -> poser la Question dans ce thread ou démarrer une nouvelle Question ?

Bonjour,

Pour ce qui est de l'adaptation :
- Respecter les propriétés name des textbox qui doivent être exactement pareilles que dans le fichier présenté
- Mettre le nom de tes feuilles de destination dans le tableau FEUILLES (ligne 4)
- Transformer en table structurée la première table data et la nommer "BDD"

Normalement tout le reste devrait suivre, la désignation des en tête de colonne peut être modifiée.

Pour la MEFC mieux vaut-il créer un nouveau sujet avec un fichier et ce que tu attends afin de ne pas noyer les différents éléments de réponses.

Cdlt,

Bonsoir le fil, Bonsoir @Ergotamine.

Dans la solution que tu proposes @Ergotamine, je me pose la question des : modifications. En effet une légère amélioration pourrait apporter du confort pour l'utilisateur : Je me suis aperçu qu'une fois les valeurs saisies par le UserForm, elles sont figées. En effet, les onglets n'étant pas liés entre eux, si je change mes valeurs (pour X raisons !) dans l'onglet directeur "Data", les modifications n'apparaissent pas sur les autres onglets. Une possibilité de lier ces onglets entre-eux ? Faut-il faire un TCD ?

Merci.

Bonjour,

Deux propositions pour une question, c'est les soldes :

V2 : C'est vrai que je n'y avait pas pensé mais ... Avec trois requête POWER QUERY qui renvoient ces valeurs c'est encore plus simple au niveau du code.
Ci-joint le fichier modifié. Par contre il faut connaître et comprendre un petit peu POWER QUERY.

V3 : Ce que vous pouvez faire aussi c'est avec une procédure événementielle rattachée à la feuille data et au changement de valeur dans la table BDD.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim FEUILLES()
Dim FEUILLE As Variant
If Not Application.Intersect(Target, ActiveSheet.ListObjects("BDD").DataBodyRange) Is Nothing Then
    FEUILLES = Array("FeuilA", "FeuilB", "FeuilC")
    ActiveSheet.ListObjects("BDD").Range.Copy
    For Each FEUILLE In FEUILLES
        Worksheets(FEUILLE).[B4].PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Next FEUILLE
    Application.CutCopyMode = False
    Worksheets("Data").Activate
    MsgBox "Données mises à jour"
End If
End Sub

Dans la V3 il a fallu que je change le code de l'USF car en effet quand j'ajoute des données, ça déclanche la procédure événementielle, d'où le Application.EnableEvents = False en début de procédure qui repasse a True à la toute fin.

En espérant que cela réponde à votre besoin.

Cdlt,

Interventions toujours rapides et hyper pertinentes, mille fois MERCI @Ergotamine.

Juste un petit élément de compréhension, car je connais PowerPivot pour l'avoir vu/testé (mais mal maîtrisé), les 4 onglets sont définis sous forme de Tableaux... ce qui sous-entends que SI je veux entrer des données (valeurs numériques/texte/formules) sur d'autres colonnes (ex. colonne H ) CàD hors tableau, ces données ne seraient pas mises-à-jour par PQ ? est-ce que mon raisonnement/compréhension est bonne ?

Bonjour,

Le plus simple est de tester. D'après mes tests pas de soucis car les résultats des requêtes POWER QUERY suivent et son adressées par ligne. Comme la requête est rechargée ça ne change en rien les références, c'est tout l'intérêt de la table structurée. La formule est rattachée à la ligne et non à la référence donc les tris ne changent rien (Cf feuilleA du classeur joint). Il faut par contre que les formules suivent la colonne contiguës afin de les intégrer dans la table. Il est aussi possible de les intégrer directement dans la requête POWER QUERY via le langage DAX, mais il faut connaître et ça peut s'avérer parfois un peu fastidieux.

Par contre je me suis rendu compte que j'ai oublié le Application.EnableEvents alors que j'en ai parlé dans la V2. Remplacez donc le code du bouton 1 de l'USF par ce code :

Private Sub CommandButton1_Click()
Dim i%, LR%
Application.EnableEvents = False
LR = ActiveSheet.Cells(ActiveSheet.Rows.Count, 2).End(xlUp).Row + 1
For i = 6 To 7
    If Not IsDate(Me.Controls("TextBox" & i)) Then
        MsgBox "Merci de renseigner une date valide", vbCritical
        Exit Sub
    End If
Next i
For i = 2 To 5
    ActiveSheet.Cells(LR, i) = Me.Controls("TextBox" & i)
Next
ActiveSheet.Cells(LR, 6) = CDate(Me.Controls("TextBox6"))
ActiveSheet.Cells(LR, 7) = CDate(Me.Controls("TextBox7"))
With ActiveSheet.ListObjects("BDD").Sort
    .SortFields.Add Key:=ActiveSheet.ListObjects("BDD").ListColumns(1).DataBodyRange, Order:=xlAscending
    .Apply
    .SortFields.Clear
End With
ThisWorkbook.RefreshAll
Worksheets("Data").Activate
Unload Me
MsgBox "Nouvelle ligne ajoutée", vbInformation
Application.EnableEvents = True
End Sub

Cdlt,

@Ergotamine. Merci pour ta prompte réponse.

c'est tout l'intérêt de la table structurée. La formule est rattachée à la ligne et non à la référence donc les tris ne changent rien

oui c'est tout à fait vrai, c'est ça l'élément de réponse. Est-ce que tu accepterai de m'aider en analysant en MP mon tableau réel ??

et comme tu commences à bien comprendre ma problématique tu pourrais m'aider à solutionner les quelques problèmes sur PQ que j'ai identifié... car OUI mon tableau fonctionne avec des Requêtes et du PowerPivot derrière...

Par contre je me suis rendu compte que j'ai oublié le Application. EnableEvents alors que j'en ai parlé dans la V2.

J'y ai pensé, mais 1/je n'étais pas sûr et 2/je ne voulais pas paraître présomptueux...

Slt

Bonjour,

Mmhhhh à vrai dire je ne maîtrise pas trop trop, vous pouvez toujours tenter et je verrais ce que je peux faire. Sinon il faudra repasser par ici et demander de l'aide, Jean-Eric maîtrise très bien l'outil par exemple. Nous avons quelques expert par ici qui peuvent trouver des solutions si les problématiques sont bien posées.

En tout cas je pense avoir répondu à votre demande sur ce sujet. N'hésitez pas à le clore le cas échéant.

Cdlt,

Mmhhhh à vrai dire je ne maîtrise pas trop trop

Franchement, si on parle de PQuery, je ne sais pas mais pour ce qui est des lignes de codes, de la diversité de propositions, de l'amabilité, de la rapidité, et bien je te dis mille fois MERCI.

Je suivrais tes conseils pour ma problématique MFC, je lancerai une nouveau fil.

à bientôt, donc...

Rechercher des sujets similaires à "vba creation ligne incrementee onglets critere alphabetique"