VBA : bouton pour générer un numéro

[dionys0s]

Bonjour

Je débute en macros, et souhaiterais vous soumettre mon premier problème :

J'ai un formulaire (un bon de commande en l'occurence) à faire saisir, et je souhaiterais que le numéros de bon de commande soit généré par une macro (un bouton "générer le numéro du bon") comme suit :

l'année, puis le mois; puis le service concerné (par exemple "SG" pour "Services Généraux" puis le numéro à deux chiffres)

le premier bon de commande passé par les services généraux devrait donc être (s'il est saisi ce mois-ci) :
2010 10 SG 01

le deuxième, passé ce mois-ci serait :
2010 10 SG 02

le troixième, passé le mois prochain serait :
2010 11 SG 01

j'espère que je suis assez clair
d'avance merci pour votre aide

Je mets ma trame en pièce jointe
Le premier onglet représente le formulaire, et le deuxième le récapitulatif dans lequel viendra se coller en dur chaque formulaire saisi (prochain étape de ma macro)

Merci pour votre aide !

 

[mromain]

Re : VBA : bouton pour générer un numéro

Bonjour dionys0s,

l'année, puis le mois; puis le service concerné (par exemple "SG" pour "Services Généraux" puis le numéro à deux chiffres)

Où est renseigné le service ?
Comment connaitre l'abréviation (SG) en fonction du service (Services Généraux) ?

a+

 

[dionys0s]

Re : VBA : bouton pour générer un numéro

Bonjour dionys0s,




Où est renseigné le service ?
Comment connaitre l'abréviation (SG) en fonction du service (Services Généraux) ?

a+

Arf oublié. Je l'ai mis dans la nouvelle pièce jointe.

 

[dionys0s]

Re : VBA : bouton pour générer un numéro

Bonjour dionys0s,




Comment connaitre l'abréviation (SG) en fonction du service (Services Généraux) ?

a+

Les abréviations seront prédéfinies en fait, mais pas forcément en fonction de la premiere lettre des mots. Par exemple pour le service marketing cela pourrait être MK

 

[mromain]

Re : VBA : bouton pour générer un numéro

Re,

Merci, il serait également intéressant d'avoir la liste des différents services avec leur abréviation associée.

De plus, pour la génération du N° du bon de commande, Faut-il repartir à 0 (dernière partie du N°) lorsqu'on change de mois ?

a+

 

[dionys0s]

Re : VBA : bouton pour générer un numéro

En fait pour les abréviations on fera comme ca :
Si le service est composé de deux mots, on prendra les initiales, et pour les autres services on prendra les deux premieres lettres du premier mot.
Et oui il faut que le premier bon de commande de chaque mois se réinitialise à 01

 

[mromain]

Re : VBA : bouton pour générer un numéro

Re,

Voici un essai :

Sub GenererNumBC()
Dim tmp() As String, numBC As String, laCell As Range, laZone As Range, memA As String, max As Long

    numBC = CStr(Year(Now)) & " " & CStr(Month(Now)) & " "
    tmp = Strings.Split(ThisWorkbook.Sheets("Formulaire services généraux").Range("C4").Text, " ")
    If LBound(tmp) = UBound(tmp) Then numBC = numBC & UCase(Left(tmp(0), 2)) & " " Else numBC = numBC & UCase(Left(tmp(0), 1)) & UCase(Left(tmp(1), 1)) & " "
    With ThisWorkbook.Sheets("Tableau Récapitulatif")
        Set laZone = .Range("A3:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
    Set laCell = laZone.Find(numBC, , xlValues, xlPart)
    If laCell Is Nothing Then
        numBC = numBC & "01"
    Else
        memA = laCell.Address
        Do
            If CLng(Replace(laCell.Text, numBC, "")) > max Then max = CLng(Replace(laCell.Text, numBC, ""))
            Set laCell = laZone.FindNext(laCell)
        Loop Until laCell.Address = memA
        numBC = numBC & Format(max + 1, "00")
    End If
    
    
    ThisWorkbook.Sheets("Formulaire services généraux").Range("F2").Value = numBC
End Sub

a+

 

[dionys0s]

Re : VBA : bouton pour générer un numéro

Merci je vais essayer d'aller voir ce que ça donne

 

[dionys0s]

Re : VBA : bouton pour générer un numéro

Cool merci bcp ca marche.
Je vais essayer de comprendre pourquoi et comment maintenant ^^

Ceci dit comment la macro va-t-elle reconnaitre le nième bon de commande de tel ou tel service et mémoriser qu'il y en n -1 avant ?

 

[Staple1600]

Re : VBA : bouton pour générer un numéro

Bonjour à tous

dionys0s
Prière de ne pas doublonner les fils pour la même question
https://www.excel-downloads.com/threads/numero-de-bon-de-commande.150564/

(La charte le conseille, encore faut-il la lire ... )

 

[dionys0s]

Re : VBA : bouton pour générer un numéro

Sorry... mais je l'ai mal intitulé et personne ne répondait...

 

[dionys0s]

Re : VBA : bouton pour générer un numéro

Re,

Voici un essai :

[COLOR=BLUE]Sub[/COLOR] GenererNumBC()
[COLOR=BLUE]Dim[/COLOR] tmp() [COLOR=BLUE]As String[/COLOR], numBC [COLOR=BLUE]As String[/COLOR], laCell [COLOR=BLUE]As[/COLOR] Range, laZone [COLOR=BLUE]As[/COLOR] Range, memA [COLOR=BLUE]As String[/COLOR], max [COLOR=BLUE]As Long[/COLOR]

    numBC = [COLOR=BLUE]CStr[/COLOR](Year(Now)) & " " & [COLOR=BLUE]CStr[/COLOR](Month(Now)) & " "
    tmp = Strings.Split(ThisWorkbook.Sheets("Formulaire services généraux").Range("C4").Text, " ")
    [COLOR=BLUE]If LBound[/COLOR](tmp) = [COLOR=BLUE]UBound[/COLOR](tmp) [COLOR=BLUE]Then[/COLOR] numBC = numBC & UCase(Left(tmp(0), 2)) & " " [COLOR=BLUE]Else[/COLOR] numBC = numBC & UCase(Left(tmp(0), 1)) & UCase(Left(tmp(1), 1)) & " "
    [COLOR=BLUE]With[/COLOR] ThisWorkbook.Sheets("Tableau Récapitulatif")
        [COLOR=BLUE]Set[/COLOR] laZone = .Range("A3:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
    [COLOR=BLUE]End With[/COLOR]
    [COLOR=BLUE]Set[/COLOR] laCell = laZone.Find(numBC, , xlValues, xlPart)
    [COLOR=BLUE]If[/COLOR] laCell [COLOR=BLUE]Is Nothing Then[/COLOR]
        numBC = numBC & "01"
    [COLOR=BLUE]Else[/COLOR]
        memA = laCell.Address
        [COLOR=BLUE]Do[/COLOR]
            [COLOR=BLUE]If CLng[/COLOR](Replace(laCell.Text, numBC, "")) > max [COLOR=BLUE]Then[/COLOR] max = [COLOR=BLUE]CLng[/COLOR](Replace(laCell.Text, numBC, ""))
            [COLOR=BLUE]Set[/COLOR] laCell = laZone.FindNext(laCell)
        [COLOR=BLUE]Loop Until[/COLOR] laCell.Address = memA
        numBC = numBC & Format(max + 1, "00")
    [COLOR=BLUE]End If[/COLOR]
    
    
    ThisWorkbook.Sheets("Formulaire services généraux").Range("F2").Value = numBC
[COLOR=BLUE]End Sub[/COLOR]

a+

Comment ajouter un bouton qui éxécute la macro à chaque fois que je clique dessus sans avoir besoin d'aller la chercher chaque fois dans le menu ?

 

[mromain]

Re : VBA : bouton pour générer un numéro

Re, bonjour JM,


J'ai rajouté les commentaires au code :

Sub GenererNumBC()
Dim tmp() As String, numBC As String, laCell As Range, laZone As Range, memA As String, max As Long

    'numBC représente le numéro du bon de commande à générer
    'on l'initialise avec le numéro de l'année + <espace> + le numéro du mois + <espace>
    numBC = CStr(Year(Now)) & " " & CStr(Month(Now)) & " "
    
    'on récupère dans un tableau chaque mot composant le service (cellule C4 du formulaire)
    tmp = Strings.Split(ThisWorkbook.Sheets("Formulaire services généraux").Range("C4").Text, " ")
    
    'si le service (C4) est composé d'un seul mot rajouter à numBC les 2 premières lettres du service en majuscule (+ <espace>)
    'sinon, rajouter à numBC la première lettre des 2 premiers mots en majuscule (+ <espace>)
    If LBound(tmp) = UBound(tmp) Then numBC = numBC & UCase(Left(tmp(0), 2)) & " " Else numBC = numBC & UCase(Left(tmp(0), 1)) & UCase(Left(tmp(1), 1)) & " "
    
    'à ce niveau, numBC est de la forme "aaaa mm XX " (ici "2010 10 SG ")
    
    'définir la zone du "Tableau Récapitulatif" contenant les références déjà inscrites (colonne A)
    With ThisWorkbook.Sheets("Tableau Récapitulatif")
        Set laZone = .Range("A3:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
    
    'rechercher dans laZone la première cellule commançant par numBC
    Set laCell = laZone.Find(numBC, , xlValues, xlPart)
    
    'si aucune cellule n'est trouvée (aucune référence déjà saisie ne commence par numBC)
    'ici, il s'agit donc de la première référence commençant par "2010 10 SG "
    If laCell Is Nothing Then
        'ajouter "01" à numBC
        numBC = numBC & "01"
    
    'sinon
    Else
        'boucler sur toutes les cellules commençant par numBC
        memA = laCell.Address
        Do
            'max représente une "mémoire" qui va contenir le numéro de la dernière référence commençant par numBC
            'si le numéro de la référence est supérieur à max, max = le numéro de la référence
            If CLng(Replace(laCell.Text, numBC, "")) > max Then max = CLng(Replace(laCell.Text, numBC, ""))
            Set laCell = laZone.FindNext(laCell)
        Loop Until laCell.Address = memA
        
        'ajouter à numBC "max+1"
        numBC = numBC & Format(max + 1, "00")
    End If
    
    'écrire numBC dans le formulaire en F2
    ThisWorkbook.Sheets("Formulaire services généraux").Range("F2").Value = numBC
End Sub

Sinon, pour le bouton, tu ne devrais pas avoir de mal à trouver des exemples sur le forum.
Bonne recherche

a+