1. #1
    Nouveau membre du Club
    Homme Profil pro
    Directeur de projet
    Inscrit en
    août 2015
    Messages
    37
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 26
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Directeur de projet

    Informations forums :
    Inscription : août 2015
    Messages : 37
    Points : 30
    Points
    30

    Par défaut Copier x fois à partir d'une valeur Textbox

    Bonjour,
    J'ai un nouveau problème, J'ai un Userform qui contient des combobox et des textbox :
    Nom : userform.png
Affichages : 451
Taille : 26,2 Ko
    J'aimerais que quand on rentre un nombre dans textbox1 et qu'on valide celui-ci me copie x fois les cellules de la Feuil2 qui sont remplies par les autres textbox et combobox.L'extraction à partir de mes textbox et combobox dans les cellules fonctionne, mais c'est une autre histoire pour copier autant de fois que renseigné.
    Serait il possible d'obtenir cette mise en page ?
    Nom : Résultat.png
Affichages : 476
Taille : 141,4 Ko
    J'ai essayé avec des codes de ce genre mais ça fonctionne pas, une idée.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     
    CommandButton1_Click()
    Worksheets("Feuil2").Range("a100000").End(xlUp).Row 1
    cpt = CInt(TextBox1)
    For I = 1 To cpt
       Cells(Derligne, 1).Value = TextBox1
       Cells(Derligne, 3).Value = TextBox1 & Format(iVAl, "00")
       Derligne = Derligne + 1
       iVAl = iVAl + 1
    Next
    MsgBox " Traitement fini"
     
    End Sub
    Merci d'avance !!

  2. #2
    Expert éminent
    Homme Profil pro
    aucune
    Inscrit en
    avril 2016
    Messages
    4 492
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 76
    Localisation : France, Pyrénées Atlantiques (Aquitaine)

    Informations professionnelles :
    Activité : aucune

    Informations forums :
    Inscription : avril 2016
    Messages : 4 492
    Points : 7 860
    Points
    7 860

    Par défaut

    Bonjour

    Analyse ce petit exemple vite fait bien fait et adapte à ton cas :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    Dim plage As Range, ou As Long, combien As Integer
    Set plage = Range("A1:B2") ' la plage à copier
    ou = 20 ' ligne où copier
    combien = 7 ' combien de fois à copier
    With plage
     For i = 1 To combien * .Columns.Count Step .Columns.Count
       .Copy Destination:=Cells(ou, i)
     Next
    End With
    comprends bien ce qu'il y a à la ligne for .... (juste de l'arithmétique, en fait)
    Je n'ai traité que la répétition "horizontale" de manière totalement délibérée. Je souhaite te voir la compléter (facile) toi-même (c'est assez le but d'un forum .. : montrer le chemin, mais ne pas tout servir tout cuit) pour ce qui est de la répétition verticale.
    Je n'accepte pas de demande d' "amitié" individuelle. Tout développeur est pour moi un ami.
    Je n'ouvre AUCUN classeur tiers (avec ou sans macro). Ne m'en proposez donc pas.

  3. #3
    Nouveau membre du Club
    Homme Profil pro
    Directeur de projet
    Inscrit en
    août 2015
    Messages
    37
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 26
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Directeur de projet

    Informations forums :
    Inscription : août 2015
    Messages : 37
    Points : 30
    Points
    30

    Par défaut

    J'ai un soucis avec le i, il me le transforme automatiquement en I majuscule et il me dit que la variable I n'est pas défini je dois faire comment ?

  4. #4
    Expert éminent
    Homme Profil pro
    aucune
    Inscrit en
    avril 2016
    Messages
    4 492
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 76
    Localisation : France, Pyrénées Atlantiques (Aquitaine)

    Informations professionnelles :
    Activité : aucune

    Informations forums :
    Inscription : avril 2016
    Messages : 4 492
    Points : 7 860
    Points
    7 860

    Par défaut

    Tu la déclares, pardi !
    Dim i as integer

    Et profitons-en pour apprendre que "un" est singulier que qu'un souci n'est pas un soucis (erreur que je vois et déplore trop souvent).
    Je n'accepte pas de demande d' "amitié" individuelle. Tout développeur est pour moi un ami.
    Je n'ouvre AUCUN classeur tiers (avec ou sans macro). Ne m'en proposez donc pas.

  5. #5
    Nouveau membre du Club
    Homme Profil pro
    Directeur de projet
    Inscrit en
    août 2015
    Messages
    37
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 26
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Directeur de projet

    Informations forums :
    Inscription : août 2015
    Messages : 37
    Points : 30
    Points
    30

    Par défaut

    j'avais oublié , merci
    Par contre ça fonctionne pas, il y a une erreur au niveau de .Copy Destination:=Cells(ou, i). Une idée ?


    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Private Sub CommandButton2_Click()
    Dim plage As Range, ou As Long, combien As Integer, i As Integer
     
    Set plage = Worksheets("Feuil2").Range("A1:B2") ' la plage à copier
    ou = Worksheets("Feuil2").Range("A" & Cells.Rows.Count).End(xlUp).Row + 1 ' ligne où copier
    combien = TextBox1.Value ' combien de fois à copier
    With plage
     For i = 1 To combien * .Columns.Count Step .Columns.Count
       .Copy Destination:=Cells(ou, i)
     Next
    End With
    MsgBox " Traitement fini"
     
    End Sub

  6. #6
    Expert éminent Avatar de casefayere
    Homme Profil pro
    RETRAITE
    Inscrit en
    décembre 2006
    Messages
    5 133
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Ardennes (Champagne Ardenne)

    Informations professionnelles :
    Activité : RETRAITE
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : décembre 2006
    Messages : 5 133
    Points : 9 840
    Points
    9 840

    Par défaut

    il y a une erreur au niveau de .Copy Destination:=Cells(ou, i). Une idée ?
    si le bouton de commande est sur "Feuil1", il faut déjà, je pense, stipuler le nom de la feuille de destination, exemple
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    For i = 1 To combien * .Columns.Count Step .Columns.Count
       .Copy Destination:=Sheets("Feuil2").Cells(ou, i)'Sheets("Feuil2") à remplacer par le nom de feuille correct
     Next
    ou
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    For i = 1 To combien * .Columns.Count Step .Columns.Count
       .Copy Sheets("Feuil2").Cells(ou, i)'Sheets("Feuil2") à remplacer par le nom de feuille correct
     Next
    Cordialement,
    Dom
    _____________________________________________
    Vous êtes nouveau ? pour baliser votre code, cliquer sur cet exemple : Anomaly
    pensez à cliquer sur si votre problème l'est
    Par contre, il est désagréable de voir une discussion résolue sans message final du demandeur (satisfaction, désarroi, remerciement, conclusion...)

  7. #7
    Expert éminent
    Homme Profil pro
    aucune
    Inscrit en
    avril 2016
    Messages
    4 492
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 76
    Localisation : France, Pyrénées Atlantiques (Aquitaine)

    Informations professionnelles :
    Activité : aucune

    Informations forums :
    Inscription : avril 2016
    Messages : 4 492
    Points : 7 860
    Points
    7 860

    Par défaut

    Merci, casefayere, de lui avoir apporté cette précision si la copie ne se fait pas à l'aide d'un bouton présent sur la même feuille.
    Ceci étant dit : ce qu'il fait là ne pouvant avoir aucune utilité dans une application, ce ne peut être qu'un exercice purement scolaire.
    D'où ce que j'ai dit plus haut, à savoir :
    Je n'ai traité que la répétition "horizontale" de manière totalement délibérée. Je souhaite te voir la compléter (facile) toi-même (c'est assez le but d'un forum .. : montrer le chemin, mais ne pas tout servir tout cuit) pour ce qui est de la répétition verticale
    Laisse-le s'il te plait faire cette seconde partie, qui peut être faite de manière lourdaute (in fine) ou plus fine (boucle j dans boucle i)
    Je sais que, comme moi, tu t'es empressé de le faire en 2 boucles imbriquées et d'y parvenir sans aucun problème
    Le lui montrer d'emblée ne serait pas ce qu'il y a de plus recommandable, toutefois.
    Laisse-le s'il te plait s'y mettre lui-même. D'autant que la boucle J dans la boucle i est quasi la même que la boucle i.
    Amitiés
    Je n'accepte pas de demande d' "amitié" individuelle. Tout développeur est pour moi un ami.
    Je n'ouvre AUCUN classeur tiers (avec ou sans macro). Ne m'en proposez donc pas.

  8. #8
    Nouveau membre du Club
    Homme Profil pro
    Directeur de projet
    Inscrit en
    août 2015
    Messages
    37
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 26
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Directeur de projet

    Informations forums :
    Inscription : août 2015
    Messages : 37
    Points : 30
    Points
    30

    Par défaut

    Merci de vos réponse!

    Désolé de te décevoir mais je suis plus étudiant, et ce n'est donc pas du tout scolaire, et oui avec beaucoup d'imagination on peut trouver pas mal d'amélioration à faire sur Excel pour gagner du temps dans des procédures Le problème c'est que je ne suis pas très fort dans ce domaine.
    J'ai simplifié une partie du fichier Excel pour le partager, pour essayer d'obtenir ce que je veux. Cela fonctionne.
    Maintenant je voudrais réussir à faire des mises en page comme présent dans l'onglet "résultat".
    J'aimerais savoir, s’il est possible de faire en sorte qu'il copie que maximum 3 fois ma sélection horizontalement, et ensuite copie verticalement.
    C'est à dire si c'est 4 par exemple, il fait 3 copier sur la premier ligne puis en dessous 1. Je ne sais pas si je suis clair.
    Autre petite question est-ce possible de laisser des espaces entre chaque copie quand on descend verticalement ?
    Ensuite je voudrais pouvoir coller à la suite quand je sélectionnerais quelque chose d'autre, c'est réalisable ?
    Le but étant d'imprimer des planches d'étiquettes les unes à la suite et optimiser le papier.
    Fichiers attachés Fichiers attachés

  9. #9
    Expert éminent Avatar de casefayere
    Homme Profil pro
    RETRAITE
    Inscrit en
    décembre 2006
    Messages
    5 133
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Ardennes (Champagne Ardenne)

    Informations professionnelles :
    Activité : RETRAITE
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : décembre 2006
    Messages : 5 133
    Points : 9 840
    Points
    9 840

    Par défaut

    et petite remarque au passage
    Et profitons-en pour apprendre que "un" est singulier que qu'un souci n'est pas un soucis (erreur que je vois et déplore trop souvent).
    parfois les gens écrivent vite et ne se relisent pas, tu n'as pas fini de déplorer pour toutes les fautes d'orthographe, omissions, coquilles...(je fais parti des gens même si en reprenant mes interventions, j'essaie de corriger ce qui me saute aux yeux)

    amitiés également
    Cordialement,
    Dom
    _____________________________________________
    Vous êtes nouveau ? pour baliser votre code, cliquer sur cet exemple : Anomaly
    pensez à cliquer sur si votre problème l'est
    Par contre, il est désagréable de voir une discussion résolue sans message final du demandeur (satisfaction, désarroi, remerciement, conclusion...)

  10. #10
    Expert éminent

    Homme Profil pro
    Curieux
    Inscrit en
    juillet 2012
    Messages
    4 413
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Curieux
    Secteur : Arts - Culture

    Informations forums :
    Inscription : juillet 2012
    Messages : 4 413
    Points : 8 432
    Points
    8 432
    Billets dans le blog
    5

    Par défaut

    Bonjour,

    étant donné que les valeurs à copier sont présentes dans les contrôles du Userform, pourquoi ne pas construire un Tableau reprenant les valeurs de ces contrôles, pour ensuite effectuer la copie X fois aux endroits voulus .... voir même directement tailler le résultat complet de la copie à effectuer dans le tableau qu'on va dimensionner d'un facteur X ?

  11. #11
    Expert éminent Avatar de casefayere
    Homme Profil pro
    RETRAITE
    Inscrit en
    décembre 2006
    Messages
    5 133
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Ardennes (Champagne Ardenne)

    Informations professionnelles :
    Activité : RETRAITE
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : décembre 2006
    Messages : 5 133
    Points : 9 840
    Points
    9 840

    Par défaut

    je ne devrais pas mais j'avais préparé un code au vu du fichier, en estimant que la feuille "etiquettes" se présente comme ça au départ
    Nom : cop.JPG
Affichages : 416
Taille : 16,9 Ko
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    Private Sub CommandButton2_Click()
    Dim plage As Range, ou As Long, combien As Integer, i As Integer, j As Integer
    j = 1
    With Worksheets("Etiquette")
      Set plage = .Range("A1:B4") ' la plage à copier
      ou = .Range("A" & .Rows.Count).End(xlUp).Row + 2 ' ligne où copier
      combien = TextBox1.Value ' combien de fois à copier
        For i = 1 To combien * plage.Columns.Count Step plage.Columns.Count
          plage.Copy .Cells(ou, j)
          j = j + plage.Columns.Count
          If j > 5 Then
            ou = ou + 3: j = 1
            ou = .Range("A" & .Rows.Count).End(xlUp).Row + 2
          End If
        Next i
    End With
    MsgBox " Traitement fini"
     
    End Sub
    Images attachées Images attachées  
    Cordialement,
    Dom
    _____________________________________________
    Vous êtes nouveau ? pour baliser votre code, cliquer sur cet exemple : Anomaly
    pensez à cliquer sur si votre problème l'est
    Par contre, il est désagréable de voir une discussion résolue sans message final du demandeur (satisfaction, désarroi, remerciement, conclusion...)

  12. #12
    Expert éminent
    Homme Profil pro
    aucune
    Inscrit en
    avril 2016
    Messages
    4 492
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 76
    Localisation : France, Pyrénées Atlantiques (Aquitaine)

    Informations professionnelles :
    Activité : aucune

    Informations forums :
    Inscription : avril 2016
    Messages : 4 492
    Points : 7 860
    Points
    7 860

    Par défaut

    Tu as eu raison (étiquettes et non devoir).
    On pourrait d'ailleurs l'écrire également ainsi (tout sur la même feuille, dans mon exemple) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    Dim plage As Range, ou As Long, combien As Integer, nbparlig As Integer, i As Integer
    Set plage = Range("A1:B4") ' la plage à copier
    ou = 20 ' ligne où copier
    nbparlig = 3 ' combien d'étiquettes par ligne
    combien = 10 ' combien d'étiquettes au total
    With plage
     Do While combien > 0
       For i = 0 To nbparlig - 1
         .Copy Destination:=Cells(ou, 1 + i * .Columns.Count)
         combien = combien - 1
         If combien <= 0 Then Exit Do
       Next
       ou = ou + 1 + .Rows.Count
     Loop
    End With
    Amitiés
    Je n'accepte pas de demande d' "amitié" individuelle. Tout développeur est pour moi un ami.
    Je n'ouvre AUCUN classeur tiers (avec ou sans macro). Ne m'en proposez donc pas.

  13. #13
    Nouveau membre du Club
    Homme Profil pro
    Directeur de projet
    Inscrit en
    août 2015
    Messages
    37
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 26
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Directeur de projet

    Informations forums :
    Inscription : août 2015
    Messages : 37
    Points : 30
    Points
    30

    Par défaut

    Citation Envoyé par joe.levrai Voir le message
    Bonjour,

    étant donné que les valeurs à copier sont présentes dans les contrôles du Userform, pourquoi ne pas construire un Tableau reprenant les valeurs de ces contrôles, pour ensuite effectuer la copie X fois aux endroits voulus .... voir même directement tailler le résultat complet de la copie à effectuer dans le tableau qu'on va dimensionner d'un facteur X ?
    Salut, désolé de ma réponse tardive. Je ne comprend pas trop comment tu veux faire. Peut-tu me préciser ?

    Re,
    Les 2 codes fonctionnent parfaitement, je vous remercie.
    Serait-il possible d'optimiser le code pour que la macro comble les fins de ligne ?
    Nom : Capture.PNG
Affichages : 434
Taille : 11,5 Ko
    Pour éviter de trop grand perte.
    Dans l'exemple: j'ai 4 étiquettes, puis 4 nouvelle. J'aimerais éviter qu'il colle directement en dessous, mais d'abord à droite de la fin des première puis en dessous...
    Pour obtenir :
    Nom : result.PNG
Affichages : 425
Taille : 8,4 Ko

    Si c'est pas possible, je ferais sans. Encore merci de m'avoir aidé.

  14. #14
    Expert éminent
    Homme Profil pro
    aucune
    Inscrit en
    avril 2016
    Messages
    4 492
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 76
    Localisation : France, Pyrénées Atlantiques (Aquitaine)

    Informations professionnelles :
    Activité : aucune

    Informations forums :
    Inscription : avril 2016
    Messages : 4 492
    Points : 7 860
    Points
    7 860

    Par défaut

    J'aimerais éviter qu'il colle directement en dessous, mais d'abord à droite de la fin des première puis en dessous
    je ne comprends pas bien ce que tu veux dire exactement par là.
    C'est ce que fait mon code.
    Montre un exemple plus clair : pas avec le même texte dans chaque cellule, de sorte à ce que l'on comprenne mieux
    En nous montrant également ce que contient la plage à copier au départ
    Je n'accepte pas de demande d' "amitié" individuelle. Tout développeur est pour moi un ami.
    Je n'ouvre AUCUN classeur tiers (avec ou sans macro). Ne m'en proposez donc pas.

  15. #15
    Nouveau membre du Club
    Homme Profil pro
    Directeur de projet
    Inscrit en
    août 2015
    Messages
    37
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 26
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Directeur de projet

    Informations forums :
    Inscription : août 2015
    Messages : 37
    Points : 30
    Points
    30

    Par défaut

    Je voudrais utiliser plusieurs fois l'Userform qui permet de générer le nombre d'étiquette.

    C'est-à-dire que je sélectionne dans un premier temps, la référence A qui permet d'incrémenter "alfa" dans la zone "A1:B4" quand je clique sur "valider". Puis quand je rentre 4 dans le nombre et que je clique sur "copie en nombre", les cellules "A6:F9" et "A11:B14" sont bien remplies.
    Dans un second temps, sans effacer les "alfa" précédents, je sélectionne la référence B qui permet d'incrémenter "beta" dans la zone "A1:B4" quand je clique sur "valider". Puis quand je rentre 4 dans le nombre et que je clique sur "copie en nombre", les cellules "A16:F19" et "A21:B24" sont remplies.
    Ce que je voudrais à ce moment, là c'est que les cellules "C11:F14" soient pas vides mais remplies par "beta". Ainsi de suite si je recommence la manip plusieurs fois.
    Nom : alfabeta.PNG
Affichages : 404
Taille : 14,4 Ko

    J'espère que j'ai été clair.
    Mais j'ai un doute sur le fait de pouvoir faire ça.
    voici le fichier Excel si tu veux tester.
    aide.xlsm

  16. #16
    Expert éminent
    Homme Profil pro
    aucune
    Inscrit en
    avril 2016
    Messages
    4 492
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 76
    Localisation : France, Pyrénées Atlantiques (Aquitaine)

    Informations professionnelles :
    Activité : aucune

    Informations forums :
    Inscription : avril 2016
    Messages : 4 492
    Points : 7 860
    Points
    7 860

    Par défaut

    Je n'ouvre (désolé) aucun classeur qui ne serait de ma propre création, mais tes explications sont claires.
    J'en conclus que tu édites un nombre défini/déterminé d'étiquettes par "jeu" de données et que tu veux économiser de la matière.
    Ce sera "sportif", mais réalisable. Je m'y mettrai ce soir, après dîner.
    Je n'accepte pas de demande d' "amitié" individuelle. Tout développeur est pour moi un ami.
    Je n'ouvre AUCUN classeur tiers (avec ou sans macro). Ne m'en proposez donc pas.

  17. #17
    Nouveau membre du Club
    Homme Profil pro
    Directeur de projet
    Inscrit en
    août 2015
    Messages
    37
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 26
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Directeur de projet

    Informations forums :
    Inscription : août 2015
    Messages : 37
    Points : 30
    Points
    30

    Par défaut

    Le nombre d'étiquette peux varier, je peux très bien en avoir 1 comme 20. Le nombre est déterminer par l'opérateur qui rentre ce qu'il a besoin.
    Merci d'avance.

  18. #18
    Membre expérimenté
    Homme Profil pro
    Inscrit en
    août 2010
    Messages
    955
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 44
    Localisation : France, Nord (Nord Pas de Calais)

    Informations forums :
    Inscription : août 2010
    Messages : 955
    Points : 1 731
    Points
    1 731

    Par défaut En utilisant le gestionnaire des noms

    Bonjour le fil,
    Salut Jacques,

    Une solution peut résider en la "préparation du terrain".
    Pour cela, pourquoi pas attribuer un nom à chaque zone "étiquette"?

    Voici un exemple relativement simple à réaliser :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    Option Explicit
     
    Sub Créer_Noms()
        Dim Lig As Integer, Col As Integer, Cpt As Integer, Ref As String
     
        Sheets("Feuil2").Select 'Eh oui, désolé, cela semble primordial dans ce cas...
        Range("A1").Select      'Idem...
        For Lig = 7 To 32 Step 5
            For Col = 1 To 5 Step 2
                With ActiveWorkbook.Names
                    Cpt = Cpt + 1
                    Ref = "=Feuil2!" & Chr(64 + Col) & Lig & ":" & Chr(65 + Col) & Lig + 3
                    .Add Name:="Zone_" & Cpt, RefersTo:=Ref
                End With
            Next
        Next
    End Sub
     
    Sub ChercheZone()
        Dim B As Boolean, L As Integer, C As Integer
     
        B = Premiere_Zone_Vide(L, C)
        If B = False Then
            'ici la feuille d'étiquettes est remplie
        Else
            MsgBox "Première cellule de la première zone vide : " & Cells(L, C).Address
        End If
    End Sub
     
    Function Premiere_Zone_Vide(Lign As Integer, Colo As Integer) As Boolean
        Dim Cpt As Integer, Nom As String, Adresse As String
     
        Premiere_Zone_Vide = False
        On Error GoTo Fin
        Do
            Cpt = Cpt + 1
            Nom = "Zone_" & Cpt
            Adresse = Replace(Split(ActiveWorkbook.Names(Nom).RefersTo, ":")(0), "=Feuil2!", "")
            If Worksheets("Feuil2").Range(Adresse).Value = "" Then
                Premiere_Zone_Vide = True
                Lign = Range(Adresse).Row
                Colo = Range(Adresse).Column
                Exit Function
             End If
        Loop While Err = 0
    Fin:
    End Function
    Cordialement,
    Franck P.


    Ps : n'oubliez pas de placer vos posts comme "résolus" () si tel est le cas...

  19. #19
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    10 895
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 10 895
    Points : 27 354
    Points
    27 354
    Billets dans le blog
    10

    Par défaut

    Salut.

    Je vais proposer une (tout) autre démarche que celle de mes camarades, pour illustrer qu'il est intéressant de construire son code VBA autrement que "tout dans le bouton valider".

    Cette technique du "tout en un" que l'on trouve hélas trop souvent sur nos forums amène à écrire un code rarement bien torché, et surtout lourd à modifier. De plus, le "tout au bouton" lie beaucoup trop des aspects de ton problème qui devraient être indépendants. Un autre problème du "tout en un" est que lorsque tu vas lancer ton traitement pour le tester, tu vas immanquablement avoir des erreurs, et tu devras nettoyer tout ce qui été réalisé avant l'erreur et recommencer tout le traiement. En découpant la tâche en petits morceaux, tu te donnes la possibilité de tester chaque étape, puis d'assembler tes morceaux pour réaliser tout le traitement. Cette façon de séparer les tâches en procédures spécifiques te permettra également, dans certains cas, de te constituer une boite à outils avec des procédures génériques. Tu ne réinventes alors pas la roue à chaque nouveau problème.

    En observant le code du bouton valider dans le code "tout dans le bouton", tu vas t'apercevoir que comme toutes les références sont en dur (nombre de lignes, de colonnes, cellules de copie et de collage,...), si par la suite tu dois copier une étiquette qui fait 2 colonne et 1 ligne de plus, tu vas galérer et devoir modifier plein de choses dans ton code alors qu'avec ce que je te propose, tu n'auras ABSOLUMENT RIEN à modifier dans ton code, du moins pour la copie et la collecte des infos sans userform. Elle sera pas belle, la vie? Bien sûr, si tu veux, via userform, manipuler des étiquettes de 3 lignes et 5 colonnes, tu devras créer un nouveau userform qui tient compte de ces nouvelles données, mais si tu comprends bien le code fourni, tu verras que les modifications seront minimes et se borneront au userform et à la fonction qui le gère.

    Réflexion, d'abord.

    Basiquement, ton problème peut se résumer à ceci:
    En français: copier x fois une étiquette en en plaçant y fois par ligne et en séparant chaque ligne d'étiquette de z lignes;
    En Excel: copier une plage source x fois en la recopiant y fois par ligne et en séparant les lignes de plages copiées de z lignes Excel.

    Il faut donc créer une procédure qui fait cela, tout cela, mais rien que cela. En procédant ainsi, tu découples totalement la copie de tes étiquettes de la façon de saisir les données. Le userform n'est alors qu'une façon parmi d'autres de récupérer les infos dont la procédure de copie à besoin. Tu découpes ainsi ton problème en petits morceaux beaucoup plus digestes, très facilement testables, beaucoup plus facilement portables et adaptables à d'autres situations.

    Je propose donc de traiter d'abord la recopie, ce qui illustrera que tu peux recopier tes étiquettes en te passant du userform, celui-ci n'étant qu'une façon parmi d'autres de saisir les données du problème. Après, on s'attaquera au formulaire pour appréhender la façon de le gérer et d'en intégrer l'utilisation dans notre logiciel de préparation d'étiquettes.

    Cette façon de procéder amènera à création de quelques procédures (parfois d'une seule ligne). Cela permet de respecter la règle qui veut qu'une procédure/fonction ne fasse qu'une seule chose. Autrement dit, lorsque tu as un problème tel que celui-ci à traiter, tu le découpes en petites tâches, chacune donnant lieu à une procédure spécifique. Donc ici, tu vas devoir:
    Nettoyer la zone d'arrivée des étiquettes (TargetClean);
    Saisir les données au travers du userform (CatchDatasFromUserform);
    Préparer l'étiquette avec les valeurs des combobox du userform (CreateLabelFromUserform);
    Copier l'étiquette (CopyLabel).


    Tu imbriqueras ces procédures/fonctions au sein d'une procédure Main. Dans l'exemple donné ici et pour illustrer la modularité du code, j'ajouterai une fonction CatchDatasFromHardCoding pour ne pas devoir réaliser le userform de suite.

    Dans ce développement, nous allons aussi utiliser les plages nommées. De cette façon, notre code n'a pas à être modifié pour tenir compte du déplacement des cellules au sein de la feuille.

    1. Nettoyage de la plage
    Même s'il ne fait qu'une ligne, on isole le traitement pour faciliter la maintenance et l'évolution du logiciel. L'avantage est que l'on peut tester cette procédure sans devoir lancer tout le traitement. Idéalement, cette plage devrait se trouver sur une autre feuille, mais avec un peu d'astuce, nous pourrons facilement
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    ' Nettoyage de la zone de collage à partir de la ligne de la cellule nommée TargetCell
    ' Placé dans une procédure spécifique pour pouvoir l'adapter facilement
    Sub TargetClean()
      Range(Range("TargetLabel"), Cells(1048576, 16384)).ClearContents
    End Sub
    2. Copie des étiquettes

    Comme dit plus haut, il s'agit d'une simple procédure qui a besoin qu'on lui passe les arguments suivants: plage source, cellule d'arrivée de départ, nombre total d'étiquettes à copier, nombre d'étiquettes par ligne, nombre de lignes de séparation. La procédure travaillera évidemment avec des boucles pour la recopie. La recopie en elle-même n'est jamais qu'une imbrication de deux boucles. Tant qu'à faire, on va s'arranger pour faire en sorte que cette procédure soit généralisable, c'est-à dire qu'elle puisse copier par la suite une étiquette ayant des dimensions différentes que 4 lignes/2 colonnes.

    La procédure est donc totalement indépendante de l'emplacement des données dans la (les) feuille(s) Excel. Aucune modification du code ne sera nécessaire en cas de modification des paramètres utilisés.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    ' Recopie d'une plage matérialisant une étiquette
    ' @param Source Plage du modèle d'étiquette
    ' @param Target Cellule supérieure gauche de la première étiquette copiée
    ' @param LabelCount Nombre d'étiquettes à copier au total
    ' @param SideBySideCount nombre d'étiquettes côte à côte sur une ligne
    ' @param Separator Nombre de lignes Excel à insérer entre chaque ligne d'étiquettes
    Sub CopyLabel(Source As Range, Target As Range, LabelCount As Long, SideBySideCount As Long, Separator As Long)
      Dim ColCounter As Long ' Itérateur de colonnes d'étiquettes
      Dim HorizontalOffset As Long ' Décalage latéral pour placer les étiquettes côtes à côte
      Dim LabelRows As Long 'nombre de lignes d'étiquettes
      Dim RowCounter As Long ' Itérateur de lignes d'étiquettes
      Dim TotalCount As Long ' Compteur d'étiquettes copiées
      Dim VerticalOffset As Long ' Décalage vertical pour placer la ligne d'étiquettes suivante
     
      ' Initialisation des variables
      LabelRows = Int(LabelCount / SideBySideCount) + 1 ' Calcul du nombre de lignes
      HorizontalOffset = Source.Columns.Count
      VerticalOffset = Source.Rows.Count + Separator
     
      Source.Copy
      For RowCounter = 1 To LabelRows
        ColCounter = 0
        ' on colle côte à côte tant que le nombre souhaité d'étiquettes n'est pas atteint
        Do While ColCounter < SideBySideCount And TotalCount < LabelCount
          Target.Offset(0, ColCounter * HorizontalOffset).PasteSpecial xlPasteValues ' recopie latérale avec décalage
          TotalCount = TotalCount + 1
          ColCounter = ColCounter + 1
        Loop
        Set Target = Target.Offset(VerticalOffset, 0) 'Décalage du target pour la nouvelle ligne
      Next RowCounter
      Application.CutCopyMode = False
    End Sub
    Comme cette procédure a besoins d'arguments, tu ne peux pas la lancer directement. Nous allons donc construire maintenant la procédure de saisie via la feuille de calcul, et nous assemblerons les morceaux dans la procédure main.

    3.Saisie des données dans la feuille Excel
    Cette procédure récupère les données des plages nommées . Outre qu'elle permet de tester notre application, elle illustre qu'en ayant procédé ainsi, par découpage de tâches, nous avons la possibilité de modifier la provenance de nos données (feuille de calcul, userform, impression d'étiquettes différentes en série, ...). De plus, on va ici utiliser les plages nommées, de manière à ne pas devoir toucher du tout au code en cas de modification des dimensions de l'étiquette ou des autres informations nécessaires au bon déroulement de l'opéation. Il suffira de redéfinir, dans Excel, la plage LabelModel et de modifier les données saisies dans les cellules nommées avant de lancer un nouveau traitement.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    ' Recopie d'une plage matérialisant une étiquette
    ' Les paramètres sont passés par référence par souci de facilité
    ' @param Source Plage du modèle d'étiquette
    ' @param Target Cellule supérieure gauche de la première étiquette copiée
    ' @param LabelCount Nombre d'étiquettes à copier au total
    ' @param SideBySideCount nombre d'étiquettes côte à côte sur une ligne
    ' @param Separator Nombre de lignes Excel à insérer entre chaque ligne d'étiquettes
    Sub CatchDatasFromExcelSheet(Source As Range, Target As Range, LabelCount As Long, SideBySideCount As Long, Separator As Long)
      Set Source = Range("LabelModel")
      Set Target = Range("TargetLabel")
      LabelCount = Range("LabelCount").Value
      SideBySideCount = Range("SideBySide").Value
      Separator = Range("Separator").Value
    End Sub
    4. Procédure principale
    La procédure principale se contente d'appeler les différentes procédures vues plus haut, dans le bon ordre.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    ' Procédure principale dans laquelle on retrouve la découpe logique de notre logiciel
    Sub Main()
      Dim Source As Range
      Dim Target As Range
      Dim LabelCount As Long
      Dim SideBySideCount As Long
      Dim Separator As Long
     
      TargetClean ' Nettoyage de la plage finale
      CatchDatasFromExcelSheet Source, Target, LabelCount, SideBySideCount, Separator
      CopyLabel Source, Target, LabelCount, SideBySideCount, Separator
      MsgBox "Traitement terminé "
    End Sub
    5. Préparation du classeur
    Le classeur est vite préparé. il suffit de préparer l'étiquette modèle et de nommer les différentes plages, comme illustré sur l'image
    Nom : 2016-05-12_125812.jpg
Affichages : 394
Taille : 109,3 Ko


    6. Test de notre logiciel
    Il suffit de lancer la procédure Main. Si tu veux tester en pas à pas, utilises F8. tu verras ainsi toutes les étapes du traitement. Voilà le résultat selon les données saisies dans la feuille Excel.
    Nom : 2016-05-12_125911.jpg
Affichages : 418
Taille : 87,7 Ko


    7. Modification des dimensions de l'étiquette et d'autres données
    Nous allons maintenant voir que, sans du tout toucher au code, nous pouvons modifier les dimensions de l'étiquette, le nombre d'étiquettes à créer, le nombre en côte à côte et l'intervalle entre deux lignes d'étiquettes, puis relancer Main pour voir le "miracle" se produire... Attention, avant de lancer Main, il faut modifier la taille de la plage nommée LabelModel comme illustré ci-dessous.
    Nom : 2016-05-12_130021.jpg
Affichages : 421
Taille : 151,9 Ko

    Voilà le résultat dans la feuille Excel. On remarquera bien ici que l'on n'a pas du tout touché au code!!
    Nom : 2016-05-12_130127.jpg
Affichages : 410
Taille : 106,7 Ko


    Saisie des données par userform
    La demande étant de saisir les données dans un userform, on va maintenant s'intéresser à construire le userform. Au passage, nous verrons qu'il est de loin préférable de nommer tes contrôles. Il faut également noter que ton userform va "figer" les dimensions de l'étiquette en fonction des combobox (ici, quatre lignes et deux colonnes). Mais ici aussi, l'adaptation du userform et du code sera simplifiée par les bonnes pratiques.

    D'abord, sur le plan de la réflexion, nous allons voir que le userform ne va pas servir à imprimer les données, mais seulement et uniquement à saisir les données souhaitées. Dès lors, le code du userform est simple à en pleurer. Le traitement sera effectué par la procédure CatchDatasFromUserform que nous allons créer. Cette procédure étant destinée à transmettre les données à l'instar de CatchDatasFromExcelSheet, elle aura évidemment la même signature, à ceci près que nous utiliserons ici une fonction qui renverra une valeur précisant que la copie doit avoir lieur ou non. En effet, si l'utilisateur ouvre son userform pour y saisir des informations, voit qu'il lui en manque et ferme sans souhaiter copier, il faut que la procédure Main tienne compte de ce refus et stoppe la procédure.

    Avant de voir cette fonction CatchDatasFromUserform en détails, nous allons construire le userform et saisir le code de son module. Je répète que ce code est simple à en pleurer (de joie), puisque le userform ne fait rien, ou presque. En effet, nous allons lui demander deux choses:
    • Mettre à disposition de son appelant le nombre de copies souhaitées;
    • Mettre à disposition de son appelant l'information du bouton sur lequel on a cliqué.

    Voici le userform...
    Nom : 2016-05-12_134240.jpg
Affichages : 413
Taille : 51,7 Ko


    Et voici son code commenté (on pleure, non?)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    Option Explicit
     
    Public Choice As String ' indique sur quel bouton on a cliqué
     
    Private Sub btnCancel_Click()
      Choice = "Cancel"
      Me.Hide
    End Sub
     
    Private Sub btnValidate_Click()
      Choice = "Validate"
      Me.Hide
    End Sub
     
    ' Fonction qui calcule de nombre de copies en tenant compte de l'éventuel +1
    Public Function LabelCount() As Long
      LabelCount = TextBox1 + -(CheckBox1.Value) ' Si checkbox1 est coché, il vaut -1, sinon 0
    End Function
    Quoi, c'est tout? Ben oui, c'est tout. Le userform n'a rien à faire, puisque c'est la procédure CopyLabel qui se tape tout le boulot.

    Maintenant, passons à la procédure CatchDatasFromUserform.
    Cette fonction doit:
    charger le formulaire;
    Passer éventuellement au formulaire certaines infos;
    l'afficher;
    en fonction du choix de l'utilisateur, récupérer les infos saisies et renvoyer True, ou simplement renvoyer False.

    On pourrait se passer du chargement si l'on n'a pas d'infos à passer au formulaire, car l'affichage s'en charge, mais j'ai pris l'habitude de charger, afficher, traiter puis décharger. Si l'on veut passer des valeurs au formulaire avant l'affichage, il est alors obligatoire de le charger avant. C'est ce que je vais faire ici pour illustrer la technique.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    ' Fonction de capture des infos du userform et passage des arguments par référence
    ' @param Source Plage du modèle d'étiquette
    ' @param Target Cellule supérieure gauche de la première étiquette copiée
    ' @param LabelCount Nombre d'étiquettes à copier au total
    ' @param SideBySideCount nombre d'étiquettes côte à côte sur une ligne
    ' @param Separator Nombre de lignes Excel à insérer entre chaque ligne d'étiquettes
    '
    ' @Return True si la copie doit avoir lieu, sinon False
    Function CatchDatasFromUserForm(Source As Range, Target As Range, LabelCount As Long, SideBySideCount As Long, Separator As Long) As Boolean
      Load uLabel1 ' Chargement du userform nommé uLabel1
      With uLabel1
        .TextBox1 = 10 ' Par défaut, le formulaire proposera 10 copies
        .CheckBox1 = True ' par défaut, le +1 sera coché
      End With
     
      uLabel1.Show ' Affichage
      ' Traitement en fonction du bouton cliqué (valeur de la variable, ou plutôt de la propriété Choice du formulaire)
      If uLabel1.Choice = "Validate" Then
        ' Transfert des données d'étiquette(j'ai gardé ici le nom de tes combobox, mais il faudra les renommer!!)
        With Range("LabelModel")
          .Cells(1, 1).Value = uLabel1.ComboBox1.Text
          .Cells(1, 2).Value = uLabel1.ComboBox2.Text
          .Cells(2, 1).Value = uLabel1.ComboBox3.Text
          .Cells(2, 2).Value = uLabel1.TextBox3.Value
          .Cells(3, 1).Value = uLabel1.TextBox2.Value
          .Cells(3, 2).Value = uLabel1.ComboBox4.Text
          .Cells(4, 1).Value = uLabel1.TextBox4.Value
        End With
     
        ' Transfert du nombre d'étiquettes à réaliser
        LabelCount = uLabel1.LabelCount
     
        ' Préparation des données non issues du userform
        Set Source = Range("LabelModel")
        Set Target = Range("TargetLabel")
        SideBySideCount = 3 ' donnée en dur pour reprendre ton exemple
        Separator = 2 ' donnée en dur pour reprendre ton exemple
        CatchDatasFromUserForm = True ' Validation, le traitement devra être exécuté
      Else
        CatchDatasFromUserForm = False ' Annulation, pas de traitement
      End If
      Unload uLabel1 ' On décharge le formulaire
    End Function
    Voilà. Sans les lignes de commentaires, ce code n'est pas bien important. Sa longueur dépend en fait du nombre d'infos à récupérer du userform.

    Il nous reste deux choses à réaliser: Modifier la plage nommée LabelModel pour qu'elle reprenne bien la plage A1:B4, et modifier Main pour qu'elle utilise la fonction CatchDatasFromUserForm au lieu de CatchDatasFromExcelSheet. Elle doit évidemment tenir compte du choix posé par l'utilisateur lorsqu'il a cliqué sur un bouton du userform. Voilà la procédure main modifiée.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    ' Procédure principale dans laquelle on retrouve la découpe logique de notre logiciel
    Sub Main()
      Dim Source As Range
      Dim Target As Range
      Dim LabelCount As Long
      Dim SideBySideCount As Long
      Dim Separator As Long
     
      TargetClean ' Nettoyage de la plage finale
      If CatchDatasFromUserForm(Source, Target, LabelCount, SideBySideCount, Separator) Then ' Si on a cliqué sur Valider
        CopyLabel Source, Target, LabelCount, SideBySideCount, Separator
        MsgBox "Traitement terminé "
      Else
        MsgBox "Copie annulée par l'utilisateur" ' Si on n'a cliqué sur Annuler
      End If
    End Sub
    Conclusion

    Une bien longue réponse que je vais transformer en tuto. J'ai pris la peine et le temps de répondre ainsi dans l'espoir que petit à petit, la qualité du code s'améliore chez les utilisateurs de VBA pour Excel. il est clair que les techniques mises en oeuvre ici sont "professionnelles" et qu'elles pourraient donner l'impression de dépasser largement les attentes de l'utilisateur lambda. Je suis pourtant convaincu que ces techniques sont à la portée de qui veut se donner la peine d'en comprendre le bienfondé et la mise en oeuvre. J'espère aussi qu'elles permettront aux intervenants qui répondent jour après jour aux questions VBA sur le forum d'améliorer eux aussi leurs techniques et, partant, leurs réponses sur le forum. Je rêve, mais j'espère ne plus voir un jour du code "tout en un" sur un bouton Valider d'un userform.

    Dans le tuto que je vais écrire sur base de cette réponse, je montrerai comment on peut greffer d'autres traitements, comme par exemple le lancement en série d'impression d'étiquettes différentes.

    J'espère aussi, au passage, avoir répondu à ta question initiale...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié l'intervention => Merci pour le
    ---------------

  20. #20
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    10 895
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 10 895
    Points : 27 354
    Points
    27 354
    Billets dans le blog
    10

    Par défaut

    Durant la rédaction de mon petit commentaire, ta demande a évolué (ou je l'ai mieux comprise ^^)...

    C'est super!! Ca va clairement illustrer le propos qu'avec la méthode très découpée que je préconise, c'est un jeu d'enfant d'adapter le code, alors qu'avec la solution "tout dans le bouton", ça va être effectivement sportif

    A nouveau, un peu de réflexion, après avoir résumé ta demande.

    Ce que tu souhaites, c'est pouvoir placer une autre série d'étiquettes à la suite d'une déjà copiée, sans recommencer à la ligne, sans effacer la page, donc, en clair, en utilisant les étiquettes vierges de la dernière ligne copiée, s'il y en a. Il me semble évident qu'il faut bien sûr que les séries d'étiquettes utilisent des modèles de tailles identiques.

    Quelles modifications cela apporte-t-il dans le code?
    • Nous allons devoir demander à l'utilisateur s'il veut commencer une nouvelle page ou s'il souhaite continuer l'impression à la suite;
    • Nous allons devoir mémoriser le nombre d'étiquettes déjà imprimées dans les traitements précédents ou le remettre à zéro selon le choix de l'opérateur;
    • Grâce à ce nombre, nous allons calculer l'emplacement de la nouvelle étiquette, ou plutôt, les ligne et colonne de son positionnement;
    • Nous allons devoir passer la ligne et la colonne de départ à la procédure CopyLabel;
    • Cette procédure va devoir utiliser ces infos pour positionner correctement la première impression.



    En découpant en petits morceaux, nous allons voir que c'est en fait assez simple.

    Commençons par le calcul de la ligne et de la colonne de départ et effectuons un rapide calcul.
    Soit j'ai imprimé un nombre d'étiquettes divisible par le nombre d'étiquettes en côte à côte, et dans ce cas la dernière ligne imprimée est complète, soit le reste de la division n'est pas égal à 0 et la dernière ligne est incomplète.
    • Si j'ai imprimé 12 étiquettes par 3 de front, 12 modulo 3 = 0 (modulo calcul le reste entier de la division). J'ai donc consommé 12 / 3 = 4 lignes complètes et je dois commencer à la cinquième.
    • Si j'ai imprimé 11 étiquettes par 3 de front, 11 mod 3 = 2, il me manque donc 1 étiquette ( 3 - 2) pour compléter ma ligne. Je dois donc commencé à la ligne 4 (quotient entier de 11 / 3).


    En VBA, cela va me donner une petite fonction qui retournera la ligne de départ.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    ' Calcule la ligne de départ de positionnement de l'étiquette
    ' en fonction des étiquettes déjà imprimées
    '
    ' @param CountofLabelsPrinted Nombre d'étiquettes déjà placées
    '
    ' @return Long mentionnant à quelle ligne doit démarrer le positionnement
    Function GetStartRow(CountofLabelsPrinted As Long, SideBySideCount As Long) As Long
      If (CountofLabelsPrinted Mod SideBySideCount) = 0 Then ' La dernière ligne est complète
        GetStartRow = CountofLabelsPrinted / SideBySideCount + 1
      Else ' La dernière ligne n'est pas complète
        GetStartRow = CountofLabelsPrinted / SideBySideCount
      End If
    End Function
    Pour le calcul de la colonne, c'est encore plus simple et cela tient sur une ligne, mais on va quand même créer la fonction qui calcule la première colonne disponible (ne jamais croire que ce sera plus simple en incluant la ligne dans le code principal, ce n'est JAMAIS plus simple!). En raisonnant, Si le nombre d'étiquettes déjà imprimées se divise par le nombre d'étiquettes de front, on commence à la colonne 1 (nouvelle ligne), sinon, on commence au modulo + 1.
    Total déjà imprimé et 3 de front: 12 étiquettes -> 12 mod 3 = 0, on commence en colonne 1 sur la nouvelle ligne (modulo + 1) (calculée par getStartRow vu plus haut).
    Total déja imprimé et 3 de front: 11 étiquettes => 11 mod 3 = 2. J'ai donc consommé deux colonnes sur la dernière ligne, je dois donc commencer à la troisième (modulo + 1).
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    ' Calcule la colonne de départ de positionnement de l'étiquette
    ' en fonction des étiquettes déjà imprimées
    '
    ' @param CountofLabelsPrinted Nombre d'étiquettes déjà placées
    '
    ' @return Long mentionnant à quelle colonne doit démarrer le positionnement
    Function GetStarColumn(CountofLabelsPrinted As Long, SideBySideCount As Long) As Long
      GetStarColumn = CountofLabelsPrinted Mod SideBySideCount + 1
    End Function
    Ca peut sembler idiot d'écrire une fonction d'une ligne, mais faites-le ( vous me remercierez plus tard ). Ca peut sembler idiot de mettre toutes ces lignes de commentaires au dessus d'une fonction d'une ligne, mais faites-le (vous me remercierez plus tard ).

    Nous allons adapter pas à pas la procédure CopyLabel. La première chose à faire est de modifier sa "signature" en ajoutant les nouveaux arguments.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    ' @param [RowStart] Ligne à laquelle doit être placée la première étiquette du traitement (utilisé pour les impressions en série)
    ' @param [ColumnStart] Colonne à laquelle doit être placée la première étiquette du traitement (utilisé pour les impressions en série)
    Sub CopyLabel(Source As Range, Target As Range, LabelCount As Long, SideBySideCount As Long, _
      Separator As Long, Optional RowStart As Long = 1, Optional columnStart As Long = 1)
    Ne placez pas ces nouveaux arguments n'importe où, sous peine de devoir peut-être modifier une kyrielle de lignes de code qui appellent cette procédure (ce n'est pas le cas ici, mais vous devez toujours, lorsque vous modifiez un code qui a été mis en production, faire en sorte que les anciens codes qui appellent votre fonction mise à jour fonctionnent toujours). C'est pourquoi j'ai mis les nouveaux arguments derrière et que je les ai rendus optionnels. Ainsi, le code appelant qui ne mentionnerait pas ces nouveaux arguments seraient fonctionnels. Remarquez aussi les valeurs par défaut, qui illustrent clairement qu'en abscence d'arguments, on commence ligne 1 colonne 1 de la plage. Au passage, vous aurez noté que j'ai ajouté deux lignes de commentaires pour les nouveaux arguments...

    Une autre modification est de positionner correctement la plage Target passée en argument. Vous remarquerez que cette plage doit TOUJOURS mentionner la première cellule de la destination, même en cas d'enchainement de traitements d'étiquettes. C'est intéressant de pratiquer ainsi car cela permet de modifier moins de code, et surtout d'avoir un point fixe pour le positionnement de la première étiquette d'un lot. La ligne ajoutée est la dernière du bloc d'initialisation des variables (c'est confortable, hein, quand le code est concis, bien rangé et bien commenté, on retrouve de suite ses jeunes...)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
      ' Initialisation des variables
      LabelRows = Int(LabelCount / SideBySideCount) + 1 ' Calcul du nombre de lignes
      HorizontalOffset = Source.Columns.Count
      VerticalOffset = Source.Rows.Count + Separator
      Set Target = Target.Offset(VerticalOffset * (RowStart - 1))
    En réfléchissant, on peut donc voir que cette cellule Target est calculée de la même façon pour une nouvelle impression (feuille blanche) ou pour une impression en continu. En fait, on décale du nombre de lignes déjà consommées. (RowStart -1) amène donc à décaler de 0 lignes en cas de nouvelle impression sur une feuille blanche.

    Pour la colonne, le fait d'avoir fonctionné avec une boucle do...while va simplifier le travail. Il s'agit simplement d'insérer un bloc If.. End IF au début du bloc Copie effective du modèle
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
      ' Copie effective du modèle
      Source.Copy
      For RowCounter = 1 To LabelRows
        If columnStart > 1 Then ' On doit compléter une colonne (uniquement sur la première ligne à imprimer)
          ColCounter = columnStart - 1
          columnStart = 1 ' Remise à 0 pour la ligne suivante (car uniquement utilisé pour la première ligne)
        Else
          ColCounter = 0
        End If
    Soit columnsstart est plus grand que 1, on doit donc terminer une ligne en décalant Target du (columnstart -1). En effet, Target est toujours en colonne 1. Si columnstart vaut 3, je dois donc décaler de 2.
    Soit columnstart vaut 1 (je dois imprimer en première colonne), et je dois donc, par rapport à Target qui est en colonne 1, décaler de 0.

    Voilà. C'est fini pour la procédure CopyLabel. Avouez que ce n'était pas si "sportif". Nous sommes simplement venus ajouter quelques lignes de codes dans un code qui avait été construit pour le permettre.
    Je remets ici tout le code de cette procédure pour que ce soit plus clair.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    ' Recopie d'une plage matérialisant une étiquette
    ' @param Source Plage du modèle d'étiquette
    ' @param Target Cellule supérieure gauche de la première étiquette copiée
    ' @param LabelCount Nombre d'étiquettes à copier au total
    ' @param SideBySideCount nombre d'étiquettes côte à côte sur une ligne
    ' @param Separator Nombre de lignes Excel à insérer entre chaque ligne d'étiquettes
    ' @param [RowStart] Ligne à laquelle doit être placée la première étiquette du traitement (utilisé pour les impressions en série)
    ' @param [ColumnStart] Colonne à laquelle doit être placée la première étiquette du traitement (utilisé pour les impressions en série)
    Sub CopyLabel(Source As Range, Target As Range, LabelCount As Long, SideBySideCount As Long, _
      Separator As Long, Optional RowStart As Long = 1, Optional columnStart As Long = 1)
      Dim ColCounter As Long ' Itérateur de colonnes d'étiquettes
      Dim HorizontalOffset As Long ' Décalage latéral pour placer les étiquettes côtes à côte
      Dim LabelRows As Long 'nombre de lignes d'étiquettes
      Dim RowCounter As Long ' Itérateur de lignes d'étiquettes
      Dim TotalCount As Long ' Compteur d'étiquettes copiées
      Dim VerticalOffset As Long ' Décalage vertical pour placer la ligne d'étiquettes suivante
     
      ' Initialisation des variables
      LabelRows = Int(LabelCount / SideBySideCount) + 1 ' Calcul du nombre de lignes
      HorizontalOffset = Source.Columns.Count
      VerticalOffset = Source.Rows.Count + Separator
      Set Target = Target.Offset(VerticalOffset * (RowStart - 1))
     
      ' Copie effective du modèle
      Source.Copy
      For RowCounter = 1 To LabelRows
        If columnStart > 1 Then ' On doit compléter une colonne (uniquement sur la première ligne à imprimer)
          ColCounter = columnStart - 1
          columnStart = 1 ' Remise à 0 pour la ligne suivante (car uniquement utilisé pour la première ligne)
        Else
          ColCounter = 0
        End If
        ' on colle côte à côte tant que le nombre souhaité d'étiquettes n'est pas atteint
        Do While ColCounter < SideBySideCount And TotalCount < LabelCount
          Target.Offset(0, ColCounter * HorizontalOffset).PasteSpecial xlPasteValues ' recopie latérale avec décalage
          TotalCount = TotalCount + 1
          ColCounter = ColCounter + 1
        Loop
        Set Target = Target.Offset(VerticalOffset, 0) 'Décalage du target pour la nouvelle ligne
      Next RowCounter
      Application.CutCopyMode = False
    End Sub
    Il reste à modifier Main. C'est en fait assez simple. D'abord, il faut mémoriser le nombre total déjà imprimé. Je propose évidemment une plage nommée que je nommerai AlreadyCopied.
    Dès lors, il suffit, après avoir affiché le userform et récupérer les infos, de demander si l'on veut une mise à zéro ou pas. Si oui, on met la valeur de AlreadyCopied à 0, on nettoie la feuille et on appelle CopyLabel, sinon, on passe par les deux fonctions getStartRow et getStartColumn pour calculer le positionnement puis on appelle CopyLabel.

    Après la copie, on n'oublie pas d'incrémenter AlreadyCopied du nombre d'étiquettes qui viennent d'être traitées. Voici le nouveau code complet de Main.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    ' Procédure principale dans laquelle on retrouve la découpe logique de notre logiciel
    Sub Main()
      Dim Answer As VbMsgBoxResult
      Dim Source As Range
      Dim Target As Range
      Dim LabelCount As Long
      Dim SideBySideCount As Long
      Dim Separator As Long
      Dim StartRow As Long
      Dim StartColumn As Long
     
     
      If CatchDatasFromUserForm(Source, Target, LabelCount, SideBySideCount, Separator) Then
        Answer = MsgBox("Voulez-vous partir d'une feuille vierge?", vbQuestion + vbYesNo, "Impression étiquettes")
        If Answer = vbYes Then
          TargetClean ' Nettoyage de la plage finale
          StartRow = 1
          StartColumn = 1
          Range("AlreadyCopied") = 0
        Else
          StartRow = GetStartRow(Range("Alreadycopied").Value, SideBySideCount)
          StartColumn = GetStarColumn(Range("Alreadycopied").Value, SideBySideCount)
        End If
        CopyLabel Source, Target, LabelCount, SideBySideCount, Separator, StartRow, StartColumn
        Range("alreadycopied").Value = Range("alreadycopied").Value + LabelCount
        MsgBox "Traitement terminé "
      Else
        MsgBox "Copie annulée par l'utilisateur"
      End If
    End Sub

    Voilà. Merci de m'avoir lu jusqu'ici. J'espère que cet exemple d'évolution du code illustre qu'il est beaucoup plus simple de travailler et de faire évoluer du code lorsqu'il est bien construit que lorsqu'il est créé dans une procédure "tout sur le bouton"...

    Bon travail pour la suite...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié l'intervention => Merci pour le
    ---------------

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 2 12 DernièreDernière

Discussions similaires

  1. Copier un champ à partir d'une valeur recherchée
    Par gilou41 dans le forum Macros et VBA Excel
    Réponses: 8
    Dernier message: 03/06/2013, 10h29
  2. Réponses: 3
    Dernier message: 23/06/2006, 11h03
  3. Réponses: 2
    Dernier message: 08/05/2006, 22h27
  4. Copier un fichier à partir d'une URL ?
    Par oulahoup dans le forum ASP
    Réponses: 2
    Dernier message: 18/08/2005, 22h52

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo