[XL-2010] Formulaire pour tâche (très) répétitive

[51Pegasi_b]

Bonjour le forum;

Passioné par les possibilités qu'offrent Excel et vba, je me décide enfin à mettre le pied à l'étrier .
Etant donné la quantité de données à traiter à la main, je pense qu'une automatisation des tâches serait vraiment parfaite !

Le But :

J'ai un tableau à réaliser pour localiser des pièces.
Ce tableau contient : reference de la pièce et emplacement correspondant.

Pas à pas :

On appele le formulaire qui contient deux champs (ref et emplacement)
On entre les données.
On valide via la commande bouton OK (ou on annule la saisie)
La macro vérifie si cette entrée existe déjà : si oui, un message le signale; si non, la macro prend la première ligne vide pour y insérer les cdonnées.

Contrainte :

Il faut que la macro soit capable de trier par ordre alphabétique au fur et à mesure des entrées.
1 mois maximum de délai

Question :

Est-ce faisable avec du temps à consacrer et (je pense) une bonne compréhension globale de l'infmmatique?

J'ai tenté de bidouiller avec l'enregistreur de macros mais pas grand résultat...

Je laisse le fichier en pièce jointe.

Je cherche des pistes ou idées, pas de réponse toute faite.
Merci par avance pour votre aide.

Localisation pièce.xls

[Sclarckone]

Bonjour,

Ton fichier doit être buggé car je n'arrive pas à l'ouvrir avec Excel 2003 (même en le renommant en .xslx, ça ne fonctionne pas).

Ceci dit, ce que tu veux faire n'est pas bien sorcier avec un minimum de connaissances en VBA et du modèle objet Excel.

• Pour faire saisir les valeurs, tu peux utiliser un userform
• Pour rechercher si chaque valeur saisie existe déjà, je récupérerai les lignes de début et de fin et je parcourrai les cellules de la colonne avec une boucle du genre for i=début to fin... (sinon tu peux aussi définir un objet Range représentant la zone de la colonne en question et parcourir la collection avec une énumération du genre "For Each Cell in Range" mais je pense que c'est plus lent.)

Si tu être guidé sans qu'on te donne un code pré-mâché, le mieux serait peut-être que tu commences à écrire ton code et nous dire où tu coinces.


Bon courage!

[MattChess]

Envoyé par 51Pegasi_b

Est-ce faisable ?

Oui.

Envoyé par 51Pegasi_b

J'ai tenté de bidouiller avec l'enregistreur de macros

C'est cela qui est intéressant. Il faudra fournir vos tentatives entre les balises [code] et [/code]. Voir la conclusion.

Envoyé par 51Pegasi_b

Je laisse le fichier en pièce jointe.

Ce n'est pas une bonne idée en tête d'une discussion. Le modérateur risque de supprimer le classeur. Lire Fichier joint dans vos discussions.

1. Décrire la feuille magasin de pièce avec des constantes nommées

Envoyé par 51Pegasi_b

J'ai un tableau à réaliser pour localiser des pièces.
Ce tableau contient : reference de la pièce et emplacement correspondant.

La première chose à faire sera de décrire la feuille contenant le tableau de localisation des pièces en traduisant votre description ci-dessus en constantes nommées et commentées par vous.

Dans Excel, ouvrir le Visual Basic Editor (Alt+F11).
Cliquez sur le VBE (Visual Basic Editor) menu "Insérer" > "Module"
Module 1 est créé.
Dans les propriétés de Module1, renommez Module1 en ModPiece.
Dans la fenêtre d'Edition du module ModPiece, copier-coller, modifier et compléter le code VBA suivant :

Code :

1
2
3
4
5
Option Explicit ' Gestion d'un magasin de pièces avec emplacement par pièce
Public Const nomFeuillePiece = "...." ' Nom de la feuille du magasin de pièces dans le classeur
Public Const rowPieceFirst = 2 ' Rangée de la première pièce. La première rangée sert d'entête
Public Const colPieceRef = 1 ' Colonne A des références des pièces
Public Const colEmpl = colPieceRef + 1 ' Colonne suivante de l'emplacement de la pièce

C'est un job de structuration où il faut trouver des noms judicieux pour les constantes, indiquez en commentaire le type d'une référence de pièce. C'est un Integer ou une String ? Idem pour l'emplacement.

Si vous avez déjà commencé votre formulaire UserFormPiece, décrivez le.
Postez votre code VBA UserFormPiece puis ModPiece entre les balises [code] et [/code].
___________

En bas de ce message s'il vous a apporté des éléments de réponse pertinents, pensez également à voter en cliquant sur le bouton vert ci-dessous.

[51Pegasi_b]

Bonsoir le forum, Mattchess,Sclarckone ;

J'ai ""bien avancé "".

Le formulaire pour saisir la réf et l'emplacement est créé. J'ai corrigé l'ordre de tabulation dans le formulaire pour plus d'ergonomie.

De plus j'ai affecté au bouton OK du formulaire une macro qui permet d'insérer les saisies clavier dans la colonne A pour la ref et dans la colonne B pour l'emplacement. voici le code utilisé :

Code :

1
2
3
 
Sheets("Localisation").Range("A2").Value = TextBox1.Value'Qui correspond à la ref
Sheets("Localisation").Range("B2").Value = TextBox2.Value'Qui correspond à l'emplacement

Oui, mais ...
Je tente de faire en sorte que VBA remplisse la première ligne vide suivante.
J'ai donc (en fouillant sur le forum) créé une variable (Ligne_vide) comme suit :

Code :

1
2
3
 
Dim Ligne_vide As Integer
Ligne_vide = Range("A65536").End(xlUp).Row + 1

Le soucis est que je ne vois pas comment l'intégrer à la macro de de façon cohérente.

MattChess : J'ai créé le module ModPiece comme vous me l'avez conseillé.Pour le moment, je ne vois pas comment l'intégrer dans ma feuille.
J'ai pourtant le sentiment que ce n'est pas pour rien que vous m'avez donné cette piste....

Est-ce que ces lignes

Code :

1
2
3
 Public Const rowPieceFirst = 2 ' Rangée de la première pièce. La première rangée sert d'entête
Public Const colPieceRef = 1 ' Colonne A des références des pièces
Public Const colEmpl = colPieceRef + 1

peuvent m'aider justement à remplir la ligne suivante ?

De plus pour répondre à votre question :

La ref est de type String, l'emplacement de type Integer.Désolé de partir un peu dans tous les sens, j'ai trifouillé un peu à tout et je commence à me perdre

Au cas où mes explications ne sont pas claires (ce qui est certainement le cas...) je laisse mon fichier qui est surment plus parlant.

Je continue cependant à travailler sur ce que vous m'avez indiqué à votre première réponse!
Merci par avance pour votre aide.

Cordialement.

[Sclarckone]

Envoyé par 51Pegasi_b

Oui, mais ...
Je tente de faire en sorte que VBA remplisse la première ligne vide suivante.
J'ai donc (en fouillant sur le forum) créé une variable (Ligne_vide) comme suit :

Code :

Le soucis est que je ne vois pas comment l'intégrer à la macro de de façon cohérente.

Ce bout de code permet de trouver la première ligne vide. Dans le cas où il n'y a pas de case vide dans la colonne considérée, on peut aussi utiliser:

Code :

Ligne_vide = Range("A65536").End(xlDown).Row + 1

Ligne_vide est donc égale à l'index de la ligne contenant la première case vide de la colonne "A".

Si tu veux écrire quelque chose dans la première ligne vide (dans la première colonne par exemple), tu peux alors utiliser:

Code :

Cells(Ligne_vide, 1).Value = 'ce que tu veux

Pour écrire dans la deuxième colonne remplace le 1 par 2...etc.

Envoyé par 51Pegasi_b

Est-ce que ces lignes

Code :

peuvent m'aider justement à remplir la ligne suivante ?

Ces lignes ne servent qu'à déclarer des constantes qui rendent le code plus lisible (enfin, je pense).

C'est-à-dire qu'au lieu d'écrire (par exemple, mais c'est inutile dans ton code);

Code :

Cells(2, 1).Value = 'ce que tu veux

Tu écriras:

Code :

Cells(rowFirstPiece, colPieceRef).Value = 'ce que tu veux

Ce qui est dans ce cas strictement équivalent mais tout de même plus lisible!

[51Pegasi_b]

Merci bcp Sclarckone. Je ""bricole"" tout ça et reviens vers vous.

Merci !

[MattChess]

1. Revue de code du formulaire

Dans la fenêtre d'Edition de "formulaire" le code a initialement la forme suivante.
Trouvez un nom plus explicite que le nom général de "formulaire" tel que frmAddRef qui indique qu'il s'agit d'un UserForm qui ajoute une référence.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
Private Sub cb1_Click() ' Trouver un nom plus explicite. Ex: BtnAddRef_Click ou BtnOk_Click
Dim Ligne_vide As Integer ' Je déclare ma variable comme un Entier : commentaire redondant

Ligne_vide = Range("A65536").End(xlUp).Row + 1
                                        ' Trouver un nom de textbox plus explicite
Sheets("Localisation").Range("A2").Value = TextBox1.Value ' Qui correspond à la ref
Sheets("Localisation").Range("B2").Value = TextBox2.Value ' Qui correspond à l'emplacement
' ^ Ajouter une tabulation dans la marge à gauche de chaque instruction
End Sub

Private Sub cb2_Click() ' Trouver un nom de bouton plus explicite. Ex: BtnCancel_Click
Unload Me   ' <-- Ajouter une tabulation dans la marge à gauche de "Unload Me"
End Sub

On a apprécié le commentaire expliquant où sont la référence et l'emplacement.

2. Version mieux présentée

Comparez avec le début du code réécrit :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Option Explicit ' Formulaire de saisie de référence de pièce sur un emplacement

' Ajouter une référence et un emplacement  <- commentaire sur ce que fait la procédure
Sub BtnOk_Click()
Dim indLineLast As Integer  ' Première ligne vide après la dernière référence
    
    ' Notez l'alignement d'une tabulation pour le corps de la procédure.
    ' L'indentation permet de mieux voir la structure du code et
    ' distinguer le corps de la procédure de la déclaration des variables locales
    
    ' On a factorisé Sheets(nomFeuillePiece) grâce à With ... End With
    With Sheets(nomFeuillePiece)
        ' Les propriétés de Sheets(nomFeuillePiece) commence par "."
        indLineLast = .Range("A65536").End(xlUp).Row + 1 ' y compris le Range !
        .Cells(indLineLast, colPieceRef) = CStr(txtboxRef.Value) ' Référence
        .Cells(indLineLast, colEmpl) = CInt(txtboxEmpl.Value)    ' Emplacement
    End With
End Sub

Option Explicit est quasiment obligatoire ainsi qu'un commentaire sur ce que fait le formulaire.
Cela permet de forcer la déclaration des variables.

3. Le code source VBA du module ModPiece

Dans la fenêtre d'Edition du module ModPiece, copier-coller le code VBA suivant :

Code :

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
Option Explicit ' Gestion d'un magasin de pièces avec emplacement par pièce
Public Const nomFeuillePiece = "Localisation" ' Nom de la feuille du magasin de pièces dans le classeur
Public Const rowPieceFirst = 2 ' Rangée de la première pièce. La première rangée sert d'entête
Public Const colPieceRef = 1 ' Colonne A des références des pièces
Public Const colEmpl = colPieceRef + 1 ' Colonne suivante de l'emplacement de la pièce
 
' Quel est le nombre de références ?
Function NbrReference() As Long
    With Worksheets(nomFeuillePiece)
        NbrReference = .Cells(.Columns(colPieceRef).Rows.Count, colPieceRef).End(xlUp).Row - rowPieceFirst + 1
    End With
End Function
 
' Afficher toutes les références en magasin
Sub AfficherReference()
Dim indRow As Long, indRef As Long
 
    With Worksheets(nomFeuillePiece)
        indRow = rowPieceFirst
        For indRef = 1 To NbrReference()
            Debug.Print "Ref n° " & indRef & " " & .Cells(indRow, colPieceRef) & _
                        " en " & .Cells(indRow, colEmpl)
            indRow = indRow + 1
        Next
    End With
End Sub
 
' La référence existe-t-elle ?
Function IsRefExist(ByVal strRef As String) As Boolean
Dim indRow As Long
 
    With Worksheets(nomFeuillePiece)
        For indRow = rowPieceFirst To rowPieceFirst + NbrReference() - 1
            If .Cells(indRow, colPieceRef) = strRef Then IsRefExist = True: Exit Function
        Next
    End With
    IsRefExist = False
End Function
 
' Ajouter une référence par ordre alphabétique
Function AjouterReference(ByVal strRef As String, ByVal empl As Integer) As Boolean
Dim indRow As Long, nbrRef As Long, indRefNew As Long
 
    If IsRefExist(strRef) Then
        Debug.Print "La référence """ & strRef & """ existe déjà"
        AjouterReference = False
    Else
        With Worksheets(nomFeuillePiece)
            nbrRef = NbrReference()
            indRefNew = rowPieceFirst + nbrRef
            For indRow = rowPieceFirst To indRefNew - 1
                If strRef < .Cells(indRow, colPieceRef) Then
                    indRefNew = indRow
                    Exit For
                End If
            Next
            If indRefNew < rowPieceFirst + nbrRef Then
                Rows(indRefNew).Copy
                Rows(indRefNew).Insert Shift:=xlDown
                Application.CutCopyMode = False
            End If
            .Cells(indRefNew, colPieceRef) = strRef
            .Cells(indRefNew, colEmpl) = empl
            AjouterReference = True
        End With
    End If
End Function

4. Tester le module ModPiece

• Pour tester le formulaire, on est obligé de l'ouvrir et saisir les champs.
• Pour tester le module ModPiece, on ouvre la fenêtre d'Exécution immédiate (Ctrl+G).

La fenêtre d'Exécution immédiate permet de tester des fragments de code pour apprendre VBA et lancer des procédures ou interroger VBA sur le résultat d'une fonction.

C'est beaucoup plus rapide que le cycle de test dans le formulaire, même si en final on pourra intégrer les fonctions testées de ModPiece dans le code du formulaire.

Dans la fenêtre d'Exécution immédiate (Ctrl+G) du VBE d'Excel, copier-coller et valider par ENTER :

Code :

? Date

23/08/2011

VBA affiche la date dans la fenêtre d'Exécution immédiate. On notera en bleu foncé le résultat affiché par VBA. Notez le point d'interrogation en début de requête. C'est le raccourci de Debug.Print que l'on trouve dans la procédure AfficherReference() de ModPiece.

Code :

? NbrReference()

1

Il y a actuellement une référence dans la feuille "Localisation".

Code :

AfficherReference

Ref n° 1 MAT3214 en 1

Comme AfficherReference() est une procédure et non une fonction, elle ne retourne pas de résultat. Il n'y a pas lieu d'interroger VBA avec le "?" en tête de requête.

Code :

? IsRefExist("MAT3214")

True

La référence "MAT3214" existe.

Code :

? IsRefExist("MATAHARI")

False

Ce n'est pas le cas de la référence "MATAHARI".

Code :

? AjouterReference("MATAHARI", 69)

True

La référence "MATAHARI" a été ajoutée avec succès. Où ?

Code :

AfficherReference

Ref n° 1 MAT3214 en 1
Ref n° 2 MATAHARI en 69

Après " MAT3214"

Code :

? AjouterReference("ANGELINA", 0)

True

Code :

AfficherReference

Ref n° 1 ANGELINA en 0
Ref n° 2 MAT3214 en 1
Ref n° 3 MATAHARI en 69

Code :

? Range("A65536").End(xlUp).Row + 1

5

Première ligne vide pour la prochaine référence.

Code :

? Cells(Columns(colPieceRef).Rows.Count, colPieceRef).End(xlUp).Row + 1

5

C'est plus long à écrire mais c'est plus explicite que le Range("A65536"). Excel VBA doit interpréter la chaîne "A65536" pour extraire les coordonnées en style L1C1. C'est donc plus long en temps d'exécution qu'en style L1C1.

Avec la forme Cells(ligne, colonne) du style L1C1, on lui donne directement l'information de ligne et colonne grâce à la constante colPieceRef identifiant la colonne A des références des pièces.

On a préféré demander à VBA de calculer le nombre de rangée de la colonne colPieceRef plutôt que de voir un 65536 -- c'est le maximum d'un entier 16-bit non signé -- se balader dans le code sans avoir de constante nommée et commenté.

5. Questions sur les références

Quel est le nombre maximum de références à saisir ?
Avez-vous ces références déjà dans un fichier texte, que l'on peut visionner dans le BlocNote (NotePad) de Windows, ou dans un autre classeur .xls ?
___________

Si la discussion est résolue, vous pouvez cliquer sur le bouton

En bas de ce message s'il vous a apporté des éléments de réponse pertinents, pensez également à voter en cliquant sur le bouton vert ci-dessous.