Code VBA pour numérotation automatique avec concaténation d'année

[WISEDOME]

Bonsoir à tous,

Je suis encore là pour exposer mes problèmes! j'espère intervenir un jour pour résoudre des problème et non en exposer.

Je souhaite en fait faire un numérotation automatique d'un enregistrement par simple click sur un Commandbutton selon la forme suivante:

00012012, our les 04 derniers chiffres représentent l'année budgétaire que je sélectionne dans une combobox affichée sur le Userform. Les quatres premiers chiffres constituent l'ordre d'enregistrement d'un dossier pour l'année précédemment sélectionnée(Ces chiffres sont consécutifs et ne se répètent pas pour une même année).

Ainsi dans la même colonne d'enregistrement, on pourrait avoir ceci:

00012012
00012011
00022012

J'ai cherché en vain sur le forum, des solutions qui pourraient m'aider. Je vous pries de bien vouloir m'aider svp.

Vous trouverez un morceau de mon fichier en pièce jointe.

Merci d'avance.

Wise.

 

[Compte Supprimé 979]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Bonjour Wisedome

Alors là, j'en ai vu des choses ... mais alors des comme ça, je crois bien jamais

Quel est l'intérêt de mettre un numéro de dossier devant l'année !?
Bonjour pour le tri par ordre chronologique ...

 

[WISEDOME]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Bonjour BrunoM45 et merci d'avoir lu mon post.

L'objectif de la numérotation telle que présentée est qu'elle doit nous permettre à vue d'oeil de connaitre l'année budgétaire et le numéro d'enregistrement pour cette année. Nous avons pensé que ce format serait plus simple afin de faciliter la numérotation automatique à chaque enregistrement d'un nouveau dossier. Cependant je reste ouvert à toute proposition qui prendrait en compte les deux contraintes citées plus haut.

Merci d'avance BrunoM45.

 

[WISEDOME]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Rebonoir à tous,

Toujours personne pour m'aider? S'il vous plait je suis sérieusement bloqué. J'ai essayé différentes méthodes mais en vain, je n'arrive pas à trouver solution à mon problème.

J'ai eu l'idée d'utiliser l'objet Scripting.Dictionary de VBA mais rien. Je vous donne ci-dessous le code que j'ai essayé de bricoler, peut être qu'il donnera des idées à toutes les bonnes volontés qui voudront bien m'aider:

'------------------------------------------------------------------
Dim MonDico, C, temp(), f, maxnum As Integer, Numerosuivant As Integer
Set f = Sheets("Feuil1")
Set MonDico = CreateObject("Scripting.Dictionary")

For Each C In f.Range("A2:A" & f.[A65000].End(xlUp).Row) ' j'explore la colonne des Numéro d'enregistrement (Colonne A) If Right(C.Value, 4) = ComboBox1.Value Then MonDico(C.Value) = "" 'Je vérifie pour chaque valeur dans la colonne A, si les quatres derniers chiffres sont égaux à ceux sélectionnés dans la combobox1(année budgétaire) Next C

temp = MonDico.keys 'Je récupère les clés du dictionnaire pour les valeurs qui répondent à ma condition ci-dessus

maxnum = Application.Max(temp) 'Je récupère la valeur maximum du dictionnaire (J'avoue que je ne sais pas si c'est correcte cette écriture)

Numerosuivant = Format(Left(maxnum, 2) + 1 & ComboBox1.Value, "000000") 'Je récupère les deux chiffres à gauche du maximum récupéré dans le dictionnaire que je majore de 1 avant de concatener avec l'année budgétaire sélectionnée en combobox1 pour avoir le prochain numéro d'enregistrement

TextBox1.Value = Numerosuivant


J'avoue qu'après de très longues heures de recheches, j'en suis presqu'à bout.

Chers experts, s'il vous plaît penchez vous sur mon cas.

Wise.

 

[Compte Supprimé 979]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Bonsoir Wisedome,

Voici ton fichier avec mon idée et le code lié

Dis moi, A+

 

[WISEDOME]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Bonjour BrunoM45,

T'es un génie!!! ta solution répond à mon problème à "deux épines près". En fait il y'a deux blocages dans cette solution qu'il me faut lever et elle répondra exactement à ce que je désire, sinon à part çà je m'incline devant ton génie:

1- Vu que les numéros apparaissant dans la textbox1 sont destinés à être déversés dans la colonne "G" (dans ton exemple) Je souhaite que le numéro suivant (pour tout nouvel enregistrement) s'affiche dans ce textbox1 au format "20120002" et non "2"

2- Je remarque après les tests que les numéro d'enregistrements doivent être triés sinon la valeur donnée comme numéro d'enregistrement suivant est erronée (Ex: Je remplace dans votre exemple 20120009 par 20120020, je veux que le numéro pour l'enregistrement suivant soit le maximum de tous les enregistrements de l'année 2012, soit 20120021 et non 20120011 (qui est le numéro consécutif au dernier enregistrement de 2012).
Serait-il possible de trouver une solution pour y remédier ou suis-je obligé d'avoir les données rangées dans l'ordre croissant.

J'espère que j'ai pu me faire comprendre.

Merci en tout cas pour ton aide qui m'ouvre assurement les portes à la résolution de mon problème.

A te lire.

Cdlt.

 

[Compte Supprimé 979]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Bonjour Wisedome,

Problèmes 1 et 2 résolu voici le fichier mis à jour

A+

 

[WISEDOME]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Bonsoir BrunoM45,

Je m'apprêter à t'envoyer un mail pour te dire que c'est parfait et que mon problème est resolu, lorsque je découvre à travers les tests que les nouveaus enregistrements pour des années qui ne figurent pas dans les numéros existants, le format souhaité est perdu(Ex je sélectionne 2014 dans la combobx1, au lieu d'avoir 20140001 dans la textbox1, j'ai plutot 1), et pour tous les autres enregistrements de cette même année, j'ai toujours la même valeur 1.

Je signale au passage qu'il y'a un bug lorsque je click sur le bouton enregistrer pour tout nouvel enregistrement (Erreur d'exécution N° 6: dépassement de capacité).

Juste ces deux problèmes et ce sera un travail impec!

Merci sincèrement pour tout ce temps accordé à ma modeste personne.

A te relire.

 

[Compte Supprimé 979]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Re,

Désolé, je n'avais pas tout testé, modifications effectuées

A+

 

[Ilino]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Bonsoir Forum, Bonsoir BRUNO,
ta proposition ça m'interesse mais peut on ajouter a ton code ( non autorisé les doublons )?
Grazie

 

[Compte Supprimé 979]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Re,

Impossible d'avoir des doublons avec ce code, à moins de saisir le numéro à la main

A+

 

[Ilino]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Re Bonsoir BRUNO; Forum Bonsoir,
Oui certainement a la main ou bien si je clic deux fois sur le bouton ENREGISTER ?
@+

 

[Compte Supprimé 979]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Re Ilino

Le fichier donner n'est là qu'a titre d'exemple !

Un peu de réflexion de ta part aurait été la bienvenue
Il suffit simplement de décharger l'USF après enregistrement, comme ce devrait être le cas

Mais puisqu'il faut tout vous fair sous le ... voici le code

Private Sub Cbn_Enregistrer_Click()
  Dim NLig As Long, NumDossier As Long
  ' Avec la feuille
  With Sheets("Feuil1")
    ' Récupérer le numéro de la prochaine ligne vide
    NLig = .Range("A" & Rows.Count).End(xlUp).Row + 1
    ' Poser la question
    If MsgBox("Voulez-vous Valider cet enregistrement? " & vbTab, vbYesNo) = vbYes Then
      NumDossier = Me.TextBox1.Value
      .Range("A" & NLig).Value = NumDossier
      ' Trier les numéros
      .Range("A1:A" & NLig).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
                                 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End If
  End With
  ' Décharger l'USF
  Unload Me
End Sub

 

[WISEDOME]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Bonjour BrunoM45,

Je ne sais comment te dire merci, c'est supppperrrrrrr!.

Tu es un GENIE!

PROBLEME RESOLU!

 

[job75]

Re : Code VBA pour numérotation automatique avec concaténation d'année

Bonjour à tous,

J'interviens (un peu tard et pour le fun) pour signaler que le format du post #1 (l'année à la fin) ne pose aucun problème.

La macro principale dans l'USF :

Private Sub ComboBox1_Change()
Dim tablo, i As Long
If ComboBox1.ListIndex = -1 Then TextBox1 = "": Exit Sub
tablo = Feuil1.Range("A1:A2", Feuil1.[A65536].End(xlUp))
For i = 1 To UBound(tablo)
  If Right(tablo(i, 1), 4) = ComboBox1 Then
    tablo(i, 1) = Val(Left(tablo(i, 1), Len(tablo(i, 1)) - 4))
  Else
    tablo(i, 1) = 0
  End If
Next
TextBox1 = Format(Application.Max(tablo) + 1, "0000") & ComboBox1
End Sub

La TextBox1 est verrouillée (propriété Locked à True).

Fichier joint.

PS : on peut même trier le tableau suivant les années, mais il faut une macro...

A+