Discussion :

[FLoPigloo]

Bonjour,
J'ai un nouveau problème, J'ai un Userform qui contient des combobox et des textbox :

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 ?

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 !!

[unparia]

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.

[FLoPigloo]

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 ?

[unparia]

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).

[FLoPigloo]

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

[casefayere]

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

[unparia]

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

[FLoPigloo]

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.

[casefayere]

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

[joe.levrai]

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 ?

[casefayere]

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

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

[unparia]

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

[FLoPigloo]

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 ?

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 :


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

[unparia]

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

[FLoPigloo]

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.


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

[unparia]

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.

[FLoPigloo]

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.

[pijaku]

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

[Pierre Fauconnier]

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



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.



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.


Voilà le résultat dans la feuille Excel. On remarquera bien ici que l'on n'a pas du tout touché au code!!



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...



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...

[Pierre Fauconnier]

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...